[jira] [Commented] (DRILL-5136) Some SQL statements fail when using Simba ODBC driver 1.3

["Robert Hou (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-5136?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15762542#comment-15762542 ] 

Robert Hou commented on DRILL-5136:
-----------------------------------

Comments from Robert Wu (Simba):

This issue is likely caused by the 1.9.0 server turning the SHOW SCHEMAS query into a limit 0 query "SELECT * FROM (show schemas) LIMIT 0" while handling the prepare API call that was introduced in Drill 1.9.0.
 
We are able to reproduce issue with the latest driver against Drill 1.9.0 server. We also noticed that when the driver passes the "show schemas" query to the prepare API exposed by Drill client the call return the following error:
   [
   PARSE ERROR: Encountered "( show" at line 1, column 15.
   Was expecting one of:
       <IDENTIFIER> ...
       … OMITTED …
       "TABLE" ...
   SQL Query SELECT * FROM (show schemas) LIMIT 0
                 ^
   [Error Id: bd266398-8090-42c0-b7b3-1efcbf2bf986 on maprdemo:31010]
   ]
 
Tracing through the Drill 1.9 server side code we were able to track down the sequence of method calls that leads to the part of the code that turns an incoming query into a limit 0 query while handling the prepare API call. Below are the details:
·         When the server received the prepare request, the Userserver class calls on the worker to submit a prepare statement (view code).

·         In the submit prearestatment function, we can see that it creates a new PreparedStatementWorker (view code).

·         Finally, in the PreparedStatementWorker class, we can see that the server is manually wrapping the user query with limit 0 (view code).

·         The server is failing to prepare the new self-modified query, resulting in the error message reported by Robert Hou.

 
We also tested the "show schemas" query against a server running pre-1.9 Drill and the issue is not reproducible. The reason for that is the driver does not use the new prepare API when connecting to server running Drill earlier than 1.9.
 
Best regards,
 
Rob
 

> Some SQL statements fail when using Simba ODBC driver 1.3
> ---------------------------------------------------------
>
>                 Key: DRILL-5136
>                 URL: https://issues.apache.org/jira/browse/DRILL-5136
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Client - ODBC
>    Affects Versions: 1.9.0
>            Reporter: Robert Hou
>            Assignee: Laurent Goujon
>
> "show schemas" does not work with Simba ODBC driver 
> SQL>show schemas
> 1: SQLPrepare = [MapR][Drill] (1040) Drill failed to execute the query: show schemas
> [30029]Query execution error. Details:[
> PARSE ERROR: Encountered "( show" at line 1, column 15.
> Was expecting one of:
>     <IDENTIFIER> ...
>     <QUOTED_IDENTIFIER> ...
>     <BACK_QUOTED_IDENTIFIER> ...
>     <BRACKET_QUOTED_IDENTIFIER> ...
>     <UNICODE_QUOTED_IDENTIFIER> ...
>     "LATERAL" ...
>     "(" "WITH" ...
>     "(" "+" ...
>     "(" "-" ...
>     "(" <UNSIGNED_INTEGER_LITERAL> ...
>     "(" <DECIMAL_NUMERIC_LITERAL> ...
>     "(" <APPROX_NUMERIC (1040) SQLSTATE=HY000
> The query profile shows this SQL statement is being executed:
>    SELECT * FROM (show schemas) LIMIT 0
>  
> The yellow highlighted syntax has been added when displaying schemas
> "use schema" also does not work.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)