search

Quansight / omnisci

Explorations on using MapD and Jupyter together.
4 stars 1 forks source link

Ibis error from pymapd when using a SQL query appended with a semicolon or comment #50

Closed jp-harvey closed 5 years ago

jp-harvey commented 5 years ago

It's common for users to append a ; to the end of their OmniSci query by habit since it's required in command line tools like omnisql. In Immerse SQL Editor the semi colon is not required but it is accepted.

When using the sql API in Ibis, the sql is not validated and the string is simply wrapped in SELECT * and executed, leading to a syntax error in OmniSci.

Example, where o is an Ibis object:

con.sql("""select * from a;""")
---------------------------------------------------------------------------
TMapDException                            Traceback (most recent call last)
/opt/conda/lib/python3.7/site-packages/pymapd/cursor.py in execute(self, operation, parameters)
    110                 column_format=True,
--> 111                 nonce=None, first_n=-1, at_most_n=-1)
    112         except T.TMapDException as e:

/opt/conda/lib/python3.7/site-packages/mapd/MapD.py in sql_execute(self, session, query, column_format, nonce, first_n, at_most_n)
   1577         self.send_sql_execute(session, query, column_format, nonce, first_n, at_most_n)
-> 1578         return self.recv_sql_execute()
   1579 

/opt/conda/lib/python3.7/site-packages/mapd/MapD.py in recv_sql_execute(self)
   1606         if result.e is not None:
-> 1607             raise result.e
   1608         raise TApplicationException(TApplicationException.MISSING_RESULT, "sql_execute failed: unknown result")

TMapDException: TMapDException(error_msg='Exception: Parse failed: Encountered ";" at line 1, column 31.\nWas expecting one of:\n    ")" ...\n    "ORDER" ...\n    "LIMIT" ...\n    "OFFSET" ...\n    "FETCH" ...\n    "NATURAL" ...\n    "JOIN" ...\n    "INNER" ...\n    "LEFT" ...\n    "RIGHT" ...\n    "FULL" ...\n    "CROSS" ...\n    "," ...\n    "OUTER" ...\n    "EXTEND" ...\n    "(" ...\n    "FOR" ...\n    "MATCH_RECOGNIZE" ...\n    "." ...\n    "AS" ...\n    <IDENTIFIER> ...\n    <QUOTED_IDENTIFIER> ...\n    <BACK_QUOTED_IDENTIFIER> ...\n    <BRACKET_QUOTED_IDENTIFIER> ...\n    <UNICODE_QUOTED_IDENTIFIER> ...\n    "TABLESAMPLE" ...\n    "WHERE" ...\n    "GROUP" ...\n    "HAVING" ...\n    "WINDOW" ...\n    "UNION" ...\n    "INTERSECT" ...\n    "EXCEPT" ...\n    "MINUS" ...\n    ')

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

ProgrammingError                          Traceback (most recent call last)
/opt/conda/lib/python3.7/site-packages/ibis/mapd/client.py in _execute(self, query, results)
    485         try:
--> 486             result = MapDCursor(execute(query))
    487         except Exception as e:

/opt/conda/lib/python3.7/site-packages/pymapd/cursor.py in execute(self, operation, parameters)
    112         except T.TMapDException as e:
--> 113             raise _translate_exception(e) from e
    114         self._description = _extract_description(result.row_set.row_desc)

ProgrammingError: Exception: Parse failed: Encountered ";" at line 1, column 31.
Was expecting one of:
    ")" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "FETCH" ...
    "NATURAL" ...
    "JOIN" ...
    "INNER" ...
    "LEFT" ...
    "RIGHT" ...
    "FULL" ...
    "CROSS" ...
    "," ...
    "OUTER" ...
    "EXTEND" ...
    "(" ...
    "FOR" ...
    "MATCH_RECOGNIZE" ...
    "." ...
    "AS" ...
    <IDENTIFIER> ...
    <QUOTED_IDENTIFIER> ...
    <BACK_QUOTED_IDENTIFIER> ...
    <BRACKET_QUOTED_IDENTIFIER> ...
    <UNICODE_QUOTED_IDENTIFIER> ...
    "TABLESAMPLE" ...
    "WHERE" ...
    "GROUP" ...
    "HAVING" ...
    "WINDOW" ...
    "UNION" ...
    "INTERSECT" ...
    "EXCEPT" ...
    "MINUS" ...

During handling of the above exception, another exception occurred:

Exception                                 Traceback (most recent call last)
<ipython-input-4-0cf040e99dfd> in <module>
----> 1 o = con.sql("""select * from a;""")
      2 o

/opt/conda/lib/python3.7/site-packages/ibis/mapd/client.py in sql(self, query)
    862         # there is already a limit in the query, we find and remove it
    863         limited_query = 'SELECT * FROM ({}) t0 LIMIT 1'.format(query)
--> 864         schema = self._get_schema_using_query(limited_query)
    865         return ops.SQLQueryResult(query, schema, self).to_expr()
    866 

/opt/conda/lib/python3.7/site-packages/ibis/mapd/client.py in _get_schema_using_query(self, query)
    440 
    441     def _get_schema_using_query(self, query):
--> 442         with self._execute(query, results=True) as result:
    443             # resets the state of the cursor and closes operation
    444             result.cursor.fetchall()

/opt/conda/lib/python3.7/site-packages/ibis/mapd/client.py in _execute(self, query, results)
    486             result = MapDCursor(execute(query))
    487         except Exception as e:
--> 488             raise Exception('{}: {}'.format(e, query))
    489 
    490         if results:

Exception: Exception: Parse failed: Encountered ";" at line 1, column 31.
Was expecting one of:
    ")" ...
    "ORDER" ...
    "LIMIT" ...
    "OFFSET" ...
    "FETCH" ...
    "NATURAL" ...
    "JOIN" ...
    "INNER" ...
    "LEFT" ...
    "RIGHT" ...
    "FULL" ...
    "CROSS" ...
    "," ...
    "OUTER" ...
    "EXTEND" ...
    "(" ...
    "FOR" ...
    "MATCH_RECOGNIZE" ...
    "." ...
    "AS" ...
    <IDENTIFIER> ...
    <QUOTED_IDENTIFIER> ...
    <BACK_QUOTED_IDENTIFIER> ...
    <BRACKET_QUOTED_IDENTIFIER> ...
    <UNICODE_QUOTED_IDENTIFIER> ...
    "TABLESAMPLE" ...
    "WHERE" ...
    "GROUP" ...
    "HAVING" ...
    "WINDOW" ...
    "UNION" ...
    "INTERSECT" ...
    "EXCEPT" ...
    "MINUS" ...
    : SELECT * FROM (select * from a;) t0 LIMIT 1

OmniSci supports comments in the form of -- also, although not tested this will likely cause a similar issue.

xmnlab commented 5 years ago

the PR https://github.com/ibis-project/ibis/pull/1903 will resolve this issue.

cpcloud commented 5 years ago

This seems like a pymapd bug not an ibis issue, what if a non ibis library wanted to issue a similar query? Fixing every downstream client of pymapd is going to lead to a bunch of duplicate code.

xmnlab commented 5 years ago

@cpcloud my understanding about this issue is:

pymapd allows the usage of ; but not inside a nested SQL. So select * from a; works fine, but SELECT * FROM (select * from a;) t0 doesn't work.

Using con.sql("""select * from a;""").execute() it will translate the expression to SELECT * FROM (select * from a;) t0

that is why in https://github.com/ibis-project/ibis/pull/1903 I am removing ;

cpcloud commented 5 years ago

Right, but it's no longer nested anymore because the schema is being retrieved in an alternative way.

xmnlab commented 5 years ago

@cpcloud mmm not sure I am following you. is there any current changing that fixed that issue?

cpcloud commented 5 years ago

The nesting issue stems from adding a LIMIT 0 subquery to retrieve the schema of query.

mpeaton commented 5 years ago

Terminating ';' still causes an error on _execute. Exception: Parse failed: Encountered ";" at line 3, column 26. . _execute has a 'LIMIT 10000' statement.

cpcloud commented 5 years ago

I see. ibis is still wrapping the query in a subquery, the munging is necessary.

mpeaton commented 5 years ago

Right, unless there is a way to get rid of the wrapper of course...

xmnlab commented 5 years ago

@mpeaton I could change the default limit and now (in that PR) it doesn't put LIMIT 10000 anymore.

mpeaton commented 5 years ago

LGTM

xmnlab commented 5 years ago

fixed by https://github.com/ibis-project/ibis/pull/1903