BUG : Can't use saved table (2 level)

[azurre931]

Hi,

There is a bug where I can't use a Saved table in a new query :

Here is the error showing in the Google Console :

{ "exception": "(Background on this error at: https://sqlalche.me/e/14/f405)", "exc": "[\"Traceback (most recent call last):\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 1900, in _execute_context\\n self.dialect.do_execute(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/default.py\\\", line 736, in do_execute\\n cursor.execute(statement, parameters)\\n File \\\"env/lib/python3.10/site-packages/pymysql/cursors.py\\\", line 158, in execute\\n result = self._query(query)\\n File \\\"env/lib/python3.10/site-packages/pymysql/cursors.py\\\", line 325, in _query\\n conn.query(q)\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 549, in query\\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 779, in _read_query_result\\n result.read()\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 1157, in read\\n first_packet = self.connection._read_packet()\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 729, in _read_packet\\n packet.raise_for_error()\\n File \\\"env/lib/python3.10/site-packages/pymysql/protocol.py\\\", line 221, in raise_for_error\\n err.raise_mysql_exception(self._data)\\n File \\\"env/lib/python3.10/site-packages/pymysql/err.py\\\", line 143, in raise_mysql_exception\\n raise errorclass(errno, errval)\\npymysql.err.ProgrammingError: (1064, \\\"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITHQRY-5563AS (\\\\n SELECT date_format(t0.posting_date, '%Y-%m-01') ...' at line 142\\\")\\n\\nThe above exception was the direct cause of the following exception:\\n\\nTraceback (most recent call last):\\n File \\\"apps/frappe/frappe/app.py\\\", line 95, in application\\n response = frappe.api.handle()\\n File \\\"apps/frappe/frappe/api.py\\\", line 54, in handle\\n return frappe.handler.handle()\\n File \\\"apps/frappe/frappe/handler.py\\\", line 47, in handle\\n data = execute_cmd(cmd)\\n File \\\"apps/frappe/frappe/handler.py\\\", line 85, in execute_cmd\\n return frappe.call(method, **frappe.form_dict)\\n File \\\"apps/frappe/frappe/__init__.py\\\", line 1622, in call\\n return fn(*args, **newargs)\\n File \\\"apps/frappe/frappe/handler.py\\\", line 317, in run_doc_method\\n response = doc.run_method(method)\\n File \\\"apps/frappe/frappe/model/document.py\\\", line 919, in run_method\\n out = Document.hook(fn)(self, *args, **kwargs)\\n File \\\"apps/frappe/frappe/model/document.py\\\", line 1281, in composer\\n return composed(self, method, *args, **kwargs)\\n File \\\"apps/frappe/frappe/model/document.py\\\", line 1263, in runner\\n add_to_return_value(self, fn(self, *args, **kwargs))\\n File \\\"apps/frappe/frappe/model/document.py\\\", line 916, in fn\\n return method_object(*args, **kwargs)\\n File \\\"apps/insights/insights/insights/doctype/insights_query/insights_query_client.py\\\", line 48, in run\\n self.fetch_results()\\n File \\\"apps/insights/insights/insights/doctype/insights_query/insights_query.py\\\", line 169, in fetch_results\\n self._results = self.variant_controller.fetch_results(additional_filters)\\n File \\\"apps/insights/insights/insights/doctype/insights_query/insights_legacy_query.py\\\", line 357, in fetch_results\\n return InsightsDataSource.get_doc(self.doc.data_source).run_query(query)\\n File \\\"apps/insights/insights/insights/doctype/insights_data_source/insights_data_source.py\\\", line 141, in run_query\\n return self.db.run_query(query)\\n File \\\"apps/insights/insights/insights/doctype/insights_data_source/sources/base_database.py\\\", line 54, in run_query\\n return self.execute_query(sql, return_columns=True)\\n File \\\"apps/insights/insights/insights/doctype/insights_data_source/sources/base_database.py\\\", line 80, in execute_query\\n res = execute_and_log(connection, sql, self.data_source)\\n File \\\"apps/insights/insights/insights/doctype/insights_data_source/sources/utils.py\\\", line 266, in execute_and_log\\n result = conn.execute(sql)\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 1365, in execute\\n return self._exec_driver_sql(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 1669, in _exec_driver_sql\\n ret = self._execute_context(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 1943, in _execute_context\\n self._handle_dbapi_exception(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 2124, in _handle_dbapi_exception\\n util.raise_(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/util/compat.py\\\", line 208, in raise_\\n raise exception\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/base.py\\\", line 1900, in _execute_context\\n self.dialect.do_execute(\\n File \\\"env/lib/python3.10/site-packages/sqlalchemy/engine/default.py\\\", line 736, in do_execute\\n cursor.execute(statement, parameters)\\n File \\\"env/lib/python3.10/site-packages/pymysql/cursors.py\\\", line 158, in execute\\n result = self._query(query)\\n File \\\"env/lib/python3.10/site-packages/pymysql/cursors.py\\\", line 325, in _query\\n conn.query(q)\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 549, in query\\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 779, in _read_query_result\\n result.read()\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 1157, in read\\n first_packet = self.connection._read_packet()\\n File \\\"env/lib/python3.10/site-packages/pymysql/connections.py\\\", line 729, in _read_packet\\n packet.raise_for_error()\\n File \\\"env/lib/python3.10/site-packages/pymysql/protocol.py\\\", line 221, in raise_for_error\\n err.raise_mysql_exception(self._data)\\n File \\\"env/lib/python3.10/site-packages/pymysql/err.py\\\", line 143, in raise_mysql_exception\\n raise errorclass(errno, errval)\\nsqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, \\\"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITHQRY-5563AS (\\\\n SELECT date_format(t0.posting_date, '%Y-%m-01') ...' at line 142\\\")\\n[SQL: WITH limited AS (/* query tables processed as CTE */ WITHQRY-5663AS (WITHQRY-5563AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASFrais variable\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Frais variables - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500),QRY-5622AS (SELECT t0.name ASGroup by Name,\\n sum(t0.qty) ASQty,\\n date_format(t2.posting_date, '%%Y-%%m-01') ASGroup by Posting Date,\\n t0.parent ASGroup by Parent,\\n t1.custom_net_weight ASNet Weight,\\n sum(t0.qty) * sum(t1.custom_net_weight) ASQty (g)\\n FROMtabSales Invoice ItemAS t0\\n LEFT OUTER JOINtabItemAS t1\\n ON t0.item_code = t1.name\\n LEFT OUTER JOINtabSales InvoiceAS t2\\n ON t0.parent = t2.name\\n WHERE t2.docstatus = '1'\\n AND t0.parent != 'ACC-SINV-2020-00663'\\n GROUP BY t0.name,\\n date_format(t2.posting_date, '%%Y-%%m-01'),\\n t0.parent\\n LIMIT 1000000),QRY-5575AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASFrais variable\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Frais variables de fabrication - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500),QRY-5572AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.credit) ASSum of Credit,\\n sum(t0.debit) ASSum of Debit,\\n sum(t0.debit) ASCumulative Sum of Debit,\\n sum(t0.credit) ASCumulative Sum of Credit\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.is_cancelled = '0'\\n AND t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Stocks - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500),QRY-5562AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASD\u00e9penses\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Charges - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500),QRY-5560AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.credit) - sum(t0.debit) ASRevenues\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Produits - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n AND t1.name != 'Bourses et subventions - P'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500),QRY-5581AS (SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) ASCumulative Sum of Debit,\\n sum(t0.credit) ASCumulative Sum of Credit,\\n sum(t0.debit) ASSum of Debit,\\n sum(t0.credit) ASSum of Credit\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Actif - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500) WITHQRY-5563AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASFrais variable\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Frais variables - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ),\\nQRY-5622AS (\\n SELECT t0.name ASGroup by Name,\\n sum(t0.qty) ASQty,\\n date_format(t2.posting_date, '%%Y-%%m-01') ASGroup by Posting Date,\\n t0.parent ASGroup by Parent,\\n t1.custom_net_weight ASNet Weight,\\n sum(t0.qty) * sum(t1.custom_net_weight) ASQty (g)\\n FROMtabSales Invoice ItemAS t0\\n LEFT OUTER JOINtabItemAS t1\\n ON t0.item_code = t1.name\\n LEFT OUTER JOINtabSales InvoiceAS t2\\n ON t0.parent = t2.name\\n WHERE t2.docstatus = '1'\\n AND t0.parent != 'ACC-SINV-2020-00663'\\n GROUP BY t0.name,\\n date_format(t2.posting_date, '%%Y-%%m-01'),\\n t0.parent\\n LIMIT 1000000\\n ),\\nQRY-5575AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASFrais variable\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Frais variables de fabrication - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ),\\nQRY-5572AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.credit) ASSum of Credit,\\n sum(t0.debit) ASSum of Debit,\\n sum(t0.debit) ASCumulative Sum of Debit,\\n sum(t0.credit) ASCumulative Sum of Credit\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.is_cancelled = '0'\\n AND t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Stocks - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ),\\nQRY-5562AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) - sum(t0.credit) ASD\u00e9penses\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Charges - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ),\\nQRY-5560AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.credit) - sum(t0.debit) ASRevenues\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Produits - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.voucher_type != 'Period Closing Voucher'\\n AND t0.is_cancelled = '0'\\n AND t1.name != 'Bourses et subventions - P'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ),\\nQRY-5581AS (\\n SELECT date_format(t0.posting_date, '%%Y-%%m-01') ASDate,\\n sum(t0.debit) ASCumulative Sum of Debit,\\n sum(t0.credit) ASCumulative Sum of Credit,\\n sum(t0.debit) ASSum of Debit,\\n sum(t0.credit) ASSum of Credit\\n FROMtabGL EntryAS t0\\n LEFT OUTER JOINtabAccountAS t1\\n ON t0.account = t1.name\\n WHERE t0.account IN (\\n SELECTtabAccount.name\\n FROMtabAccount,\\n (\\n SELECTtabAccount.lft AS lft,\\ntabAccount.rgt AS rgt\\n FROMtabAccount\\n WHEREtabAccount.name = 'Actif - P'\\n ) AS lft_rgt\\n WHEREtabAccount.lft > lft_rgt.lft\\n ANDtabAccount.rgt < lft_rgt.rgt\\n )\\n AND t0.is_cancelled = '0'\\n GROUP BY date_format(t0.posting_date, '%%Y-%%m-01')\\n LIMIT 500\\n ) SELECT date_format(t0.Date, '%%Y-%%m-01') ASDate,\\n t0.RevenuesASRevenues,\\n t1.D\u00e9pensesASD\u00e9penses,\\n t2.Frais variableASCOGS,\\n sum(t3.Cumulative Sum of Debit) - sum(t3.Cumulative Sum of Credit) ASStocks,\\n t4.Frais variableASFrais variable,\\n sum(t0.Revenues) - sum(t1.D\u00e9penses) ASProfits,\\n sum(t5.Cumulative Sum of Debit) - sum(t5.Cumulative Sum of Credit) ASActif,\\n sum(t6.Qty (g)) ASQty (g),\\n ((sum(t0.Revenues) - sum(t4.Frais variable)) / sum(t0.Revenues)) * 100 ASMarge brute %%,\\n (sum(t0.Revenues- t1.D\u00e9penses) / sum(t0.Revenues)) * 100 ASMarge net %%,\\n (avg(t0.Revenues- t4.Frais variable) / t0.Revenues) * 100 ASAvg marge brut\\n FROMQRY-5560AS t0\\n LEFT OUTER JOINQRY-5562AS t1\\n ON t0.Date= t1.Date\\n LEFT OUTER JOINQRY-5575AS t2\\n ON t0.Date= t2.Date\\n LEFT OUTER JOINQRY-5572AS t3\\n ON t0.Date= t3.Date\\n LEFT OUTER JOINQRY-5563AS t4\\n ON t0.Date= t4.Date\\n LEFT OUTER JOINQRY-5581AS t5\\n ON t0.Date= t5.Date\\n LEFT OUTER JOINQRY-5622AS t6\\n ON t0.Date= t6.Group by Posting Date\\n GROUP BY date_format(t0.Date, '%%Y-%%m-01')\\n LIMIT 2147483647) SELECT t0.* \\nFROMQRY-5663AS t0 \\n LIMIT 500) SELECT * FROM limited LIMIT 500;]\\n(Background on this error at: https://sqlalche.me/e/14/f405)\\n\"]" }

[nextchamp-saqib]

I am not able to replicate the issue. If possible, can you replicate with 2 queries using one in the other?

[azurre931]

Here are the steps to replicate the bug Tue Nov 28 2023 13_53_12.webm

[nextchamp-saqib]

This should be fixed now with v2.0+ version. Re-open if it is still replicable in the newer version