BUG: read_sql from IBM DB2 raises syntax error

[knyats]

System information

• OS Platform and Distribution (e.g., Linux Ubuntu 16.04):
• Modin version (modin-0.8.0):
• Python version (py3):
• Code we can use to reproduce: import ibm_db_sa import sqlalchemy import modin.pandas as pandas import configparser import os

os.environ["MODIN_ENGINE"] = "ray"

query = """ SELECT * FROM db2_table FETCH FIRST 20 ROWS ONLY; """ conn = """ibm_db_sa://user_name:password@host:port/database""" df = pandas.read_sql(query,conn)

Describe the problem

I am trying to use modin.pandas to query an IBM DB2 table. DB2 does not use the LIMIT keyword so I am getting an error from the line in your code "SELECT * FROM ({}) as foo LIMIT 0".format(sql), con, index_col=index_co

Error message sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "LIMIT" was found following "". Expected tokens may include: "FETCH FIRST ROWS ONLY". SQLSTATE=42601 SQLCODE=-104

Source code / logs

Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/ibm_db-3.0.1-py3.6-linux-x86_64.egg/ibm_db_dbi.py", line 1235, in _set_cursor_helper num_columns = ibm_db.num_fields(self.stmt_handler) Exception: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "LIMIT" was found following "". Expected tokens may include: "FETCH FIRST ROWS ONLY". SQLSTATE=42601 SQLCODE=-104

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py", line 106, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.6/site-packages/ibm_db-3.0.1-py3.6-linux-x86_64.egg/ibm_db_dbi.py", line 1355, in execute self._set_cursor_helper() File "/usr/local/lib/python3.6/site-packages/ibm_db-3.0.1-py3.6-linux-x86_64.egg/ibm_db_dbi.py", line 1238, in _set_cursor_helper raise self.messages[len(self.messages) - 1] ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "LIMIT" was found following "". Expected tokens may include: "FETCH FIRST ROWS ONLY". SQLSTATE=42601 SQLCODE=-104

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "parallel.py", line 58, in df = pandas.read_sql(query, connection) File "/home/knyat/.local/lib/python3.6/site-packages/modin/pandas/io.py", line 385, in read_sql return DataFrame(query_compiler=EngineDispatcher.read_sql(kwargs)) File "/home/knyat/.local/lib/python3.6/site-packages/modin/data_management/dispatcher.py", line 155, in read_sql return cls.__engine._read_sql(kwargs) File "/home/knyat/.local/lib/python3.6/site-packages/modin/data_management/factories.py", line 96, in _read_sql return cls.io_cls.read_sql(kwargs) File "/home/knyat/.local/lib/python3.6/site-packages/modin/engines/base/io/file_reader.py", line 29, in read query_compiler = cls._read(*args, *kwargs) File "/home/knyat/.local/lib/python3.6/site-packages/modin/engines/base/io/sql/sql_reader.py", line 62, in _read "SELECT FROM ({}) as foo LIMIT 0".format(sql), con, index_col=index_col File "/home/knyat/.local/lib/python3.6/site-packages/pandas/io/sql.py", line 438, in read_sql chunksize=chunksize, File "/home/knyat/.local/lib/python3.6/site-packages/pandas/io/sql.py", line 1218, in read_query result = self.execute(args) File "/home/knyat/.local/lib/python3.6/site-packages/pandas/io/sql.py", line 1087, in execute return self.connectable.execute(args, kwargs) File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 2244, in execute return connection.execute(statement, *multiparams, *params) File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1012, in execute return self._executetext(object, multiparams, params) File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1187, in _execute_text parameters, File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1324, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1518, in _handle_dbapi_exception sqlalchemy_exception, with_traceback=excinfo[2], from=e File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_ raise exception File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py", line 106, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.6/site-packages/ibm_db-3.0.1-py3.6-linux-x86_64.egg/ibm_db_dbi.py", line 1355, in execute self._set_cursor_helper() File "/usr/local/lib/python3.6/site-packages/ibm_db-3.0.1-py3.6-linux-x86_64.egg/ibm_db_dbi.py", line 1238, in _set_cursor_helper raise self.messages[len(self.messages) - 1] sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "LIMIT" was found following "". Expected tokens may include: "FETCH FIRST ROWS ONLY". SQLSTATE=42601 SQLCODE=-104 [SQL: SELECT FROM ( SELECT * FROM DB2_TABLE ) as foo LIMIT 0] (Background on this error at: http://sqlalche.me/e/f405)

[pyrito]

Hi @knyats I am not able to reproduce the error you are facing. Could you posted an update issue?

[mvashishtha]

@pyrito this looks like a syntax error in how we were selecting a subset of the rows in our read_sql implementation. It's similar to #3524.

[Vsn92]

Getting a similar error when using Modin to query IBM DB2. Any workaround for this issue?

An unexpected token "" was found following "". Expected tokens may include: "". SQLSTATE=42601 SQLCODE=-104 [SQL: SELECT COUNT() FROM (select from TestTable where year =2023) AS ]

[mvashishtha]

@Vsn92 thank you for commenting here. #5631 should fix that particular issue. That fix hasn't gone into an official modin release yet, but can you try installing from the Modin master branch to get the fix?

[Vsn92]

@mvashishtha , Sure will try installing from master branch.

[Vsn92]

@mvashishtha, I installed Modin from the master branch. Still facing the same issue. Having '_' as alias is causing the issue.

An unexpected token "" was found following "". Expected tokens may include: "". SQLSTATE=42601 SQLCODE=-104 [SQL: SELECT COUNT() FROM (select from TestTable where year =2023) AS ]

[mvashishtha]

@Vsn92 could you pleas post the entire stack trace?