René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Nested labeled expressions are omitted","articleBody":"The pybigquery driver replaces nested labeled expression with the label instead of the actual expression. The result is an \"Unrecognized name\" error. Otherwise, when the expression label matches the inner column name, the expression will be silently omitted for a plain column reference.\r\n\r\nWhen the query is compiled and printed with `literal_binds`, the query looks correct (ie: the full expression is included), but for plain `print`, `compile`, and the actual execution, the expression is missing.\r\n\r\nIt seems like labeled expressions are only compiled one step past the first _inner_ label instead of recursively.\r\n\r\nTest case:\r\n\r\n```python\r\nfrom contextlib import contextmanager\r\n\r\nfrom sqlalchemy import MetaData, Table, case, create_engine, func, literal, select\r\n\r\nengine = create_engine(\"bigquery://\u003cyour connstr here\u003e\")\r\n# engine = create_engine(\"postgresql:/\u003cyour connstr here\u003e\")\r\n\r\n\r\n@contextmanager\r\ndef test_query():\r\n    engine.execute(\r\n        \"\"\"\r\n        create table test as (\r\n            select 1 as val\r\n            union all select 2\r\n            union all select 3\r\n        )\r\n    \"\"\"\r\n    )\r\n    val = Table(\"test\", MetaData(bind=engine), autoload=True).c.val\r\n    sum = func.sum\r\n    try:\r\n        yield select(\r\n            [\r\n                # References `inner` instead of `val`.\r\n                sum(sum(val.label(\"inner\")).label(\"outer\")).over(),\r\n                # Also references `inner` instead of `val`.\r\n                sum(case([[literal(True), val.label(\"inner\")]]).label(\"outer\")),\r\n                # Completely omits the case expression and references `middle`.\r\n                sum(\r\n                    sum(\r\n                        case([[literal(True), val.label(\"inner\")]]).label(\"middle\")\r\n                    ).label(\"outer\")\r\n                ).over(),\r\n            ]\r\n        )\r\n    finally:\r\n        engine.execute(\"drop table test\")\r\n\r\n\r\nwith test_query() as q:\r\n    print(\"Print:\")\r\n    print(q)\r\n\r\n    print(\"\\nCompile:\")\r\n    print(q.compile(engine))\r\n\r\n    print(\"\\nLiteral compile:\")\r\n    print(q.compile(engine, compile_kwargs={\"literal_binds\": True}))\r\n\r\n    print(\"\\nResult:\")\r\n    print(tuple(engine.execute(q)))\r\n```\r\n\r\nThe output from the Bigquery Engine is below - note that the query in the exception and all prints except the literal compile show the labels instead of inner expressions.\r\n\r\n```\r\nPrint:\r\nSELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN %(param_1)s THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\nFROM `test`\r\n\r\nCompile:\r\nSELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN %(param_1)s THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\nFROM `test`\r\n\r\nLiteral compile:\r\nSELECT sum(sum(`test`.`val`)) OVER () AS `anon_1`, sum(CASE WHEN true THEN `test`.`val` END) AS `sum_1`, sum(sum(CASE WHEN true THEN `test`.`val` END)) OVER () AS `anon_2`\r\nFROM `test`\r\n\r\nResult:\r\nTraceback (most recent call last):\r\n  File \"/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/dbapi/cursor.py\", line 172, in execute\r\n    self._query_job.result()\r\n  File \"/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/job.py\", line 2939, in result\r\n    super(QueryJob, self).result(timeout=timeout)\r\n  File \"/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/job.py\", line 734, in result\r\n    return super(_AsyncJob, self).result(timeout=timeout)\r\n  File \"/usr/local/lib/python3.7/site-packages/google/api_core/future/polling.py\", line 127, in result\r\n    raise self._exception\r\ngoogle.api_core.exceptions.BadRequest: 400 Unrecognized name: `inner` at [1:16]\r\n\r\n(job ID: \u003comitted\u003e)\r\n\r\n                                                         -----Query Job SQL Follows-----                                                       \r\n\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n   1:SELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN @`param_1` THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\n   2:FROM `test`\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n\r\nDuring handling of the above exception, another exception occurred:\r\n\r\nTraceback (most recent call last):\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 1249, in _execute_context\r\n    cursor, statement, parameters, context\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py\", line 552, in do_execute\r\n    cursor.execute(statement, parameters)\r\n  File \"/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/dbapi/cursor.py\", line 174, in execute\r\n    raise exceptions.DatabaseError(exc)\r\ngoogle.cloud.bigquery.dbapi.exceptions.DatabaseError: 400 Unrecognized name: `inner` at [1:16]\r\n\r\n(job ID: \u003comitted\u003e)\r\n\r\n                                                         -----Query Job SQL Follows-----                                                       \r\n\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n   1:SELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN @`param_1` THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\n   2:FROM `test`\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n\r\nThe above exception was the direct cause of the following exception:\r\n\r\nTraceback (most recent call last):\r\n  File \"src/test.py\", line 54, in \u003cmodule\u003e\r\n    print(tuple(engine.execute(q)))\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 2179, in execute\r\n    return connection.execute(statement, *multiparams, **params)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 988, in execute\r\n    return meth(self, multiparams, params)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py\", line 287, in _execute_on_connection\r\n    return connection._execute_clauseelement(self, multiparams, params)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 1107, in _execute_clauseelement\r\n    distilled_params,\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 1253, in _execute_context\r\n    e, statement, parameters, cursor, context\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 1473, in _handle_dbapi_exception\r\n    util.raise_from_cause(sqlalchemy_exception, exc_info)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py\", line 398, in raise_from_cause\r\n    reraise(type(exception), exception, tb=exc_tb, cause=cause)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py\", line 152, in reraise\r\n    raise value.with_traceback(tb)\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py\", line 1249, in _execute_context\r\n    cursor, statement, parameters, context\r\n  File \"/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py\", line 552, in do_execute\r\n    cursor.execute(statement, parameters)\r\n  File \"/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/dbapi/cursor.py\", line 174, in execute\r\n    raise exceptions.DatabaseError(exc)\r\nsqlalchemy.exc.DatabaseError: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 Unrecognized name: `inner` at [1:16]\r\n\r\n(job ID: \u003comitted\u003e)\r\n\r\n                                                         -----Query Job SQL Follows-----                                                       \r\n\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n   1:SELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN @`param_1` THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\n   2:FROM `test`\r\n    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |\r\n[SQL: SELECT sum(sum(`inner`)) OVER () AS `anon_1`, sum(CASE WHEN %(param_1)s THEN `inner` END) AS `sum_1`, sum(sum(`middle`)) OVER () AS `anon_2`\r\nFROM `test`]\r\n[parameters: {'param_1': True}]\r\n(Background on this error at: http://sqlalche.me/e/4xp6)\r\n```\r\n\r\nFor context (and correct behavior I think), when I change the engine to a Postgres database, the query looks correct and runs fine:\r\n\r\n```\r\nSELECT sum(sum(test.val)) OVER () AS anon_1, sum(CASE WHEN %(param_1)s THEN test.val END) AS sum_1, sum(sum(CASE WHEN %(param_2)s THEN test.val END)) OVER () AS anon_2\r\nFROM test\r\n\r\nCompile:\r\nSELECT sum(sum(test.val)) OVER () AS anon_1, sum(CASE WHEN %(param_1)s THEN test.val END) AS sum_1, sum(sum(CASE WHEN %(param_2)s THEN test.val END)) OVER () AS anon_2\r\nFROM test\r\n\r\nLiteral compile:\r\nSELECT sum(sum(test.val)) OVER () AS anon_1, sum(CASE WHEN true THEN test.val END) AS sum_1, sum(sum(CASE WHEN true THEN test.val END)) OVER () AS anon_2\r\nFROM test\r\n\r\nResult:\r\n((Decimal('6'), 6, Decimal('6')),)\r\n```","author":{"url":"https://github.com/JacobHayes","@type":"Person","name":"JacobHayes"},"datePublished":"2019-09-23T19:45:12.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/39/python-bigquery-sqlalchemy/issues/39"}