Duplicate contracts on BigQuery

[davideaimar]

I found out that the contracts table contains duplicates on Google BigQuery. For example the query:

SELECT address, block_timestamp, block_number
FROM `bigquery-public-data.crypto_ethereum.contracts` 
WHERE address = '0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67'

Returns 23 rows with inconsistent block informations:

[{
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-11-02 11:35:59.000000 UTC",
  "block_number": "15882078"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-11-02 11:55:35.000000 UTC",
  "block_number": "15882176"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-13 15:59:35.000000 UTC",
  "block_number": "16176824"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-11-09 17:08:59.000000 UTC",
  "block_number": "15933860"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-03 15:46:35.000000 UTC",
  "block_number": "16748963"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-10 00:35:35.000000 UTC",
  "block_number": "16794237"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-11 00:16:11.000000 UTC",
  "block_number": "16801223"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-14 00:00:47.000000 UTC",
  "block_number": "16822495"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-14 21:21:59.000000 UTC",
  "block_number": "16828829"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-03-16 01:24:35.000000 UTC",
  "block_number": "16837129"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-02-16 03:18:35.000000 UTC",
  "block_number": "16638562"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2023-02-11 16:54:11.000000 UTC",
  "block_number": "16606839"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-11-11 17:41:11.000000 UTC",
  "block_number": "15948328"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-27 02:10:11.000000 UTC",
  "block_number": "16273011"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-11-16 00:02:59.000000 UTC",
  "block_number": "15978883"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-24 23:45:11.000000 UTC",
  "block_number": "16257963"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-24 23:55:11.000000 UTC",
  "block_number": "16258013"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-22 22:48:47.000000 UTC",
  "block_number": "16243335"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-25 00:05:11.000000 UTC",
  "block_number": "16258063"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-25 00:15:11.000000 UTC",
  "block_number": "16258113"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-25 00:25:11.000000 UTC",
  "block_number": "16258162"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-25 00:35:11.000000 UTC",
  "block_number": "16258212"
}, {
  "address": "0xb7af915f3d152ee32c8fb75c838eb42e62bbcc67",
  "block_timestamp": "2022-12-25 00:45:11.000000 UTC",
  "block_number": "16258262"
}]

Is this the expected behaviour? Is this documented somewhere?

Thanks!

[davideaimar]

I think this is an example of a "metamorphosis smart contract" that was destroyed and recreated multiple times at the same address using the CREATE2 opcode. More can be found here: https://ethereum-blockchain-developer.com/110-upgrade-smart-contracts/12-metamorphosis-create2/