René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Dispatched sql from dry run does not match sql from Dataframe attribute","articleBody":"I have noticed a wild discrepancy between the sql obtained from `DataFrame.sql` and the dispatched sql from `DataFrame.to_pandas(dry_run=True)`. The sql code I get from `DataFrame.sql` is much cleaner and, accordingly to the big query UI, it would consume much less bytes when executed (in my example 156.35 MB). The dispatched sql obtained via `DataFrame.to_pandas(dry_run=True)` is much heavier and less optimised, requiring a full table scan (~2TB in my example). As far as I understood, the sql from `DataFrame.sql` does not rely on any cached table.\n\nIt would be nice if the dispatched sql would be the optimised one in order to avoid a full table scan on partitioned tables.\n\n#### Environment details\n\n  - OS type and version: Debian GNU/Linux 11 (bullseye)\n  - Python version: 3.10.16\n  - pip version: `pip 25.0.1`\n  - `bigframes` version: `2.26.0`\n\n\n```python\nPython: 3.10.16 | packaged by conda-forge | (main, Dec  5 2024, 14:16:10) [GCC 13.3.0]\nbigframes==2.26.0\ngoogle-cloud-bigquery==3.38.0\npandas==2.3.3\npyarrow==21.0.0\nsqlglot==27.28.1\n```\n\n#### Code example\n\n```python\nimport datetime\nimport bigframes.pandas as bpd\n\nbpd.options.bigquery.project = \"\u003credacted\u003e\"\ndf = bpd.read_gbq(\"bigquery-public-data.crypto_bitcoin.transactions\")\n# This table is partitioned by block_timestamp_month\nselection = df[df.block_timestamp_month == datetime.date(2025, 10, 1)][[\"block_timestamp_month\", \"size\"]]\ndry_run = selection.to_pandas(dry_run=True)\n```\n\n```python\ndry_run[dry_run.index == \"totalBytesProcessed\"].values[0]\n#Output: 2365056405079\n# ~2.3TB\n```\n\n```python\nselection.sql\n```\nOutputs\n```sql\nSELECT\n`bfuid_col_12` AS `block_timestamp_month`,\n`bfuid_col_5` AS `size`\nFROM\n(SELECT\n  `t0`.`size`,\n  `t0`.`block_timestamp_month`,\n  `t0`.`size` AS `bfuid_col_5`,\n  `t0`.`block_timestamp_month` AS `bfuid_col_12`,\n  `t0`.`block_timestamp_month` = DATE(2025, 10, 1) AS `bfuid_col_21`\nFROM (\n  SELECT\n    `size`,\n    `block_timestamp_month`\n  FROM `bigquery-public-data.crypto_bitcoin.transactions` FOR SYSTEM_TIME AS OF TIMESTAMP('2025-10-23T12:47:48.073960+00:00')\n) AS `t0`\nWHERE\n  `t0`.`block_timestamp_month` = DATE(2025, 10, 1))\n```\n\nWhich gives an estimated processed bytes of 156.35 MB when pasted on the big query editor.\n\nChecking the dispatched sql I get:\n\n```python\nprint(dry_run[dry_run.index == \"dispatchedSql\"].values[0])\n```\n\n```sql\nSELECT\n`bfuid_col_3` AS `bfuid_col_3`,\n`bfuid_col_12` AS `bfuid_col_12`,\n`bfuid_col_5` AS `bfuid_col_5`\nFROM\n(SELECT\n  `t1`.`bfuid_col_3`,\n  `t1`.`bfuid_col_12`,\n  `t1`.`bfuid_col_5`,\n  `t1`.`bfuid_col_28` AS `bfuid_col_29`\nFROM (\n  SELECT\n    ROW_NUMBER() OVER (\n      ORDER BY CONCAT(\n        CAST(FARM_FINGERPRINT(\n          CONCAT(\n            CONCAT('\\\\', REPLACE(COALESCE(`t0`.`hash`, ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`virtual_size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`version` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`lock_time` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(`t0`.`block_hash`, ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_number` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_timestamp` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT(\n              '\\\\',\n              REPLACE(COALESCE(CAST(`t0`.`block_timestamp_month` AS STRING), ''), '\\\\', '\\\\\\\\')\n            ),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`is_coinbase` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`fee` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`inputs`), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`outputs`), ''), '\\\\', '\\\\\\\\'))\n          )\n        ) AS STRING),\n        CAST(FARM_FINGERPRINT(\n          CONCAT(\n            CONCAT(\n              CONCAT('\\\\', REPLACE(COALESCE(`t0`.`hash`, ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`virtual_size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`version` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`lock_time` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(`t0`.`block_hash`, ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_number` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_timestamp` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT(\n                '\\\\',\n                REPLACE(COALESCE(CAST(`t0`.`block_timestamp_month` AS STRING), ''), '\\\\', '\\\\\\\\')\n              ),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`is_coinbase` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`fee` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`inputs`), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`outputs`), ''), '\\\\', '\\\\\\\\'))\n            ),\n            '_'\n          )\n        ) AS STRING),\n        CAST(RAND() AS STRING)\n      ) ASC\n    ) - 1 AS `bfuid_col_3`,\n    `t0`.`size` AS `bfuid_col_5`,\n    `t0`.`block_timestamp_month` AS `bfuid_col_12`,\n    `t0`.`block_timestamp_month` = DATE(2025, 10, 1) AS `bfuid_col_21`,\n    ROW_NUMBER() OVER (\n      ORDER BY CONCAT(\n        CAST(FARM_FINGERPRINT(\n          CONCAT(\n            CONCAT('\\\\', REPLACE(COALESCE(`t0`.`hash`, ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`virtual_size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`version` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`lock_time` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(`t0`.`block_hash`, ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_number` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_timestamp` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT(\n              '\\\\',\n              REPLACE(COALESCE(CAST(`t0`.`block_timestamp_month` AS STRING), ''), '\\\\', '\\\\\\\\')\n            ),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`is_coinbase` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`fee` AS STRING), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`inputs`), ''), '\\\\', '\\\\\\\\')),\n            CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`outputs`), ''), '\\\\', '\\\\\\\\'))\n          )\n        ) AS STRING),\n        CAST(FARM_FINGERPRINT(\n          CONCAT(\n            CONCAT(\n              CONCAT('\\\\', REPLACE(COALESCE(`t0`.`hash`, ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`virtual_size` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`version` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`lock_time` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(`t0`.`block_hash`, ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_number` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`block_timestamp` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT(\n                '\\\\',\n                REPLACE(COALESCE(CAST(`t0`.`block_timestamp_month` AS STRING), ''), '\\\\', '\\\\\\\\')\n              ),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_count` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`input_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`output_value` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`is_coinbase` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(CAST(`t0`.`fee` AS STRING), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`inputs`), ''), '\\\\', '\\\\\\\\')),\n              CONCAT('\\\\', REPLACE(COALESCE(to_json_string(`t0`.`outputs`), ''), '\\\\', '\\\\\\\\'))\n            ),\n            '_'\n          )\n        ) AS STRING),\n        CAST(RAND() AS STRING)\n      ) ASC\n    ) - 1 AS `bfuid_col_28`\n  FROM (\n    SELECT\n      `hash`,\n      `size`,\n      `virtual_size`,\n      `version`,\n      `lock_time`,\n      `block_hash`,\n      `block_number`,\n      `block_timestamp`,\n      `block_timestamp_month`,\n      `input_count`,\n      `output_count`,\n      `input_value`,\n      `output_value`,\n      `is_coinbase`,\n      `fee`,\n      `inputs`,\n      `outputs`\n    FROM `bigquery-public-data.crypto_bitcoin.transactions` FOR SYSTEM_TIME AS OF TIMESTAMP('2025-10-23T12:47:48.073960+00:00')\n  ) AS `t0`\n) AS `t1`\nWHERE\n  `t1`.`bfuid_col_21`)\nORDER BY `bfuid_col_29` ASC NULLS LAST\n```\n","author":{"url":"https://github.com/ClaudioSalvatoreArcidiacono","@type":"Person","name":"ClaudioSalvatoreArcidiacono"},"datePublished":"2025-10-23T13:12:09.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/2191/python-bigquery-dataframes/issues/2191"}