GX Core is unusable on ORACLE using SQLAlchemy - Generates invalid SQL (Adds FROM DUAL to query)

[BrentEaston]

test.py.txt

Problem Description
We currently use an old version (15) of Great Expectations against an Oracle database, using SQLAlchemy. This has worked for several years. I am trying to convert our existing installation to use the latest GX Core and SQLAlchemy versions. A simple attempt to validate an Expectation against a small batch of data fails because GX Core is generating invalid SQL trying to access Oracle.

The SQL I am using is select 1 from spriden where rownum < 10

The 2 SQLs being generated by GX Core are:

SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1
SELECT count(*) AS \"table.row_count\" \nFROM (SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1) anon_1

which are both rubbish. The FROM DUAL is just not needed.

I have tried both the oracledb and cx_oracle packages with the same result.

To Reproduce
Run the attached test.py.txt program, replace the table name 'spriden' with any other table name that exists within your environment

The output from this program is:

`--- Data Source ---
connection_string: oracle+oracledb://${ODIN_ORACLE_USER}:${ODIN_ORACLE_PASSWORD}@${ODIN_ORACLE_HOST_ODS}:${ODIN_ORACLE_PORT_ODS}?service_name=${ODIN_ORACLE_DATABASE_ODS}
id: a8801ad2-73d2-4d20-958a-693988ed96d0
name: ODST
type: sql

--- Data Source Asset List ---
[QueryAsset(name='T002', type='query', id=UUID('d95aa19b-48a2-4284-8063-b7a1908da545'), order_by=[], batch_metadata={}, batch_definitions=[], query='select 1 as error from spriden where rownum < 10')]
--- Batch Columns ---
Calculating Metrics: 10%|█████████████ | 1/10 [00:00<00:01, 8.67it/s]
--- Results ---
{
"success": false,
"expectation_config": {
"type": "expect_column_values_to_be_null",
"kwargs": {
"column": "error",
"batch_id": "ODST-T002"
},
"meta": {}
},
"result": {},
"meta": {},
"exception_info": {
"MetricConfigurationID(metric_name='table.column_types', metric_domain_kwargs_id='a351bbf72b281f0b7a62dbbf3599ce5c', metric_value_kwargs_id='include_nested=True')": {
"exception_traceback": "Traceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context\n self.dialect.do_execute(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute\n cursor.execute(statement, parameters)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/oracledb/cursor.py", line 710, in execute\n impl.execute(self)\n File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute\n File "src/oracledb/impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message\n File "src/oracledb/impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception\noracledb.exceptions.DatabaseError: ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 533, in _process_direct_and_bundled_metric_computation_configurations\n metric_computation_configuration.metric_fn( # type: ignore[misc] # F not callable\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/expectations/metrics/metric_provider.py", line 60, in inner_func\n return metric_fn(args, **kwargs)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/expectations/metrics/table_metrics/table_column_types.py", line 70, in _sqlalchemy\n return _get_sqlalchemy_column_metadata(execution_engine, batch_data)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/expectations/metrics/table_metrics/table_column_types.py", line 107, in _get_sqlalchemy_column_metadata\n return get_sqlalchemy_column_metadata(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/expectations/metrics/util.py", line 410, in get_sqlalchemy_column_metadata\n columns = column_reflection_fallback(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/expectations/metrics/util.py", line 661, in column_reflection_fallback\n result_object = connection.execute(query)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1416, in execute\n return meth(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection\n return connection._execute_clauseelement(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement\n ret = self._execute_context(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context\n return self._exec_single_context(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context\n self._handle_dbapi_exception(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception\n raise sqlalchemy_exception.with_traceback(exc_info[2]) from e\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context\n self.dialect.do_execute(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute\n cursor.execute(statement, parameters)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/oracledb/cursor.py", line 710, in execute\n impl.execute(self)\n File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute\n File "src/oracledb/impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message\n File "src/oracledb/impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception\nsqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/validator/validation_graph.py", line 278, in _resolve\n self._execution_engine.resolve_metrics(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 280, in resolve_metrics\n return self._process_direct_and_bundled_metric_computation_configurations(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 538, in _process_direct_and_bundled_metric_computation_configurations\n raise gx_exceptions.MetricResolutionError(\ngreat_expectations.exceptions.exceptions.MetricResolutionError: (oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)\n",
"exception_message": "(oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)",
"raised_exception": true
},
"MetricConfigurationID(metric_name='table.row_count', metric_domain_kwargs_id='a351bbf72b281f0b7a62dbbf3599ce5c', metric_value_kwargs_id=())": {
"exception_traceback": "Traceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context\n self.dialect.do_execute(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute\n cursor.execute(statement, parameters)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/oracledb/cursor.py", line 710, in execute\n impl.execute(self)\n File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute\n File "src/oracledb/impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message\n File "src/oracledb/impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception\noracledb.exceptions.DatabaseError: ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 546, in _process_direct_and_bundled_metric_computation_configurations\n self.resolve_metric_bundle(metric_fn_bundle=metric_fn_bundle_configurations)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_execution_engine.py", line 983, in resolve_metric_bundle\n res = self.execute_query(sa_query_object).fetchall() # type: ignore[assignment] # FIXME CoP\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_execution_engine.py", line 1274, in execute_query\n result = connection.execute(query) # type: ignore[arg-type] # FIXME:Selectable overly broad\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1416, in execute\n return meth(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection\n return connection._execute_clauseelement(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement\n ret = self._execute_context(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context\n return self._exec_single_context(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context\n self._handle_dbapi_exception(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception\n raise sqlalchemy_exception.with_traceback(exc_info[2]) from e\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context\n self.dialect.do_execute(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute\n cursor.execute(statement, parameters)\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/oracledb/cursor.py", line 710, in execute\n impl.execute(self)\n File "src/oracledb/impl/thin/cursor.pyx", line 196, in oracledb.thin_impl.ThinCursorImpl.execute\n File "src/oracledb/impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 440, in oracledb.thin_impl.Protocol._process_single_message\n File "src/oracledb/impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message\n File "src/oracledb/impl/thin/messages.pyx", line 74, in oracledb.thin_impl.Message._check_and_raise_exception\nsqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT count() AS "table.row_count" \nFROM (SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1) anon_1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/validator/validation_graph.py", line 278, in _resolve\n self._execution_engine.resolve_metrics(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 280, in resolve_metrics\n return self._process_direct_and_bundled_metric_computation_configurations(\n File "/u01/app/odin/gxenv/lib64/python3.9/site-packages/great_expectations/execution_engine/execution_engine.py", line 550, in _process_direct_and_bundled_metric_computation_configurations\n raise gx_exceptions.MetricResolutionError(\ngreat_expectations.exceptions.exceptions.MetricResolutionError: (oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT count() AS "table.row_count" \nFROM (SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1) anon_1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)\n",
"exception_message": "(oracledb.exceptions.DatabaseError) ORA-00907: missing right parenthesis\nHelp: https://docs.oracle.com/error-help/db/ora-00907/\n[SQL: SELECT count() AS "table.row_count" \nFROM (SELECT * \nFROM (SELECT 1 as error from spriden where rownum < 10 FROM DUAL) anon_1 \nWHERE 1 = 1) anon_1]\n(Background on this error at: https://sqlalche.me/e/20/4xp6)",
"raised_exception": true
}
}
}`

Expected behavior
That the call to batch.validate() will not fall over with an exception due to GX Core running an invalid SQL.

** Environment **

• Linux OS
• GX Core version 1.3.6
• oracledb version 2.5.1
• cx-oracle version 8.3.0
• Oracle version 19c Enterprise

Additional context
This problem is not present in Great Expectations version 0.15.43