You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Wesley Chow <we...@chartbeat.com> on 2017/04/24 21:33:14 UTC
JDBC SQL parse error on CTAS
I'm seeing a funny issue where issuing SELECT statements through the JDBC
driver is fine, but when I prepend them with CREATE TABLE AS, I start
getting parse errors. If I copy+paste the query string into sqlline the
CTAS works. When the thing breaks, it gives me an exception with message
like:
SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
And complains about a parse error at the CREATE TABLE step. Now this is
weird to me, because it seems like the driver is automatically wrapping the
CTAS with a "SELECT * FROM ... LIMIT 0"?
An additional complication is that this isn't straight up JDBC. This is
Python calling out to the JDBC driver, so admittedly I might be throwing a
wrench in there somewhere. But it doesn't make sense to me that either the
Python layer or JDBC would modify the query in that way. I've checked the
Python wrapper source and there I couldn't find anything to indicate it
might be changing the query. I also briefly sifted through the JDBC code
and couldn't find anything as well.
Any clues as to what might be going on?
Wes
Re: JDBC SQL parse error on CTAS
Posted by Zelaine Fong <zf...@mapr.com>.
Yes, being “schema free”, the LIMIT 0 is issued internally by Drill as part of the prepare so Drill will return schema information for the query without actually fetching any data.
If you can avoid the prepare of the CTAS statement, that would avoid the problem.
-- Zelaine
On 4/25/17, 3:44 PM, "Wesley Chow" <we...@chartbeat.com> wrote:
Is the point in the LIMIT 0 to trick the planner into not executing the
statement in some way? When I apply the patch to not wrap CTAS with a
LIMIT0 query I get "table exists" errors, which implies that the query is
getting executed twice. Just trying to figure this out so I know if I
should fix this on the Drill side or work around it by avoiding prepared
statements...
Thanks,
Wes
On Mon, Apr 24, 2017 at 9:38 PM, Wesley Chow <we...@chartbeat.com> wrote:
> Yes that sounds like it. I'm looking at this patch for the corresponding
> issue:
>
> https://github.com/apache/drill/pull/698/files#diff-
> 6b274c93088e03d7ee1d7d266fcf4ac8R133
>
> And I don't understand why the issue says that this patch only handles the
> "show schema" case, but it looks like it handles any case that isn't
> SELECT. That said, for it to be completely correct it should also handle
> UPDATEs, right? I'm admittedly unclear on what the purpose of the "LIMIT 0"
> is...
>
> I'll try applying this patch to see if it fixes my issue.
>
> Thanks,
> Wes
>
>
> On Mon, Apr 24, 2017 at 5:49 PM, Zelaine Fong <zf...@mapr.com> wrote:
>
>> I suspect you’ve hit https://issues.apache.org/jira/browse/DRILL-5136.
>> See the comment trail which mentions the problem with CTAS.
>>
>> -- Zelaine
>>
>> On 4/24/17, 2:45 PM, "Kunal Khatua" <kk...@mapr.com> wrote:
>>
>> I'm not sure if you can do a
>>
>> select * from (CTAS ..)
>>
>> The output of CTAS really is only a metric of the rows that got
>> written by the various fragments. Are you able to run the query in SQLLine?
>>
>> The parse error does a pretty good job of pointing out where the
>> query appears to have broken the parser's semantics. So it is possible
>> that you need some escape characters to handle the query, without which,
>> Python might be altering the query.
>>
>>
>> Kunal
>>
>> ________________________________
>> From: Wesley Chow <we...@chartbeat.com>
>> Sent: Monday, April 24, 2017 2:33:14 PM
>> To: user@drill.apache.org
>> Subject: JDBC SQL parse error on CTAS
>>
>> I'm seeing a funny issue where issuing SELECT statements through the
>> JDBC
>> driver is fine, but when I prepend them with CREATE TABLE AS, I start
>> getting parse errors. If I copy+paste the query string into sqlline
>> the
>> CTAS works. When the thing breaks, it gives me an exception with
>> message
>> like:
>>
>> SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
>>
>> And complains about a parse error at the CREATE TABLE step. Now this
>> is
>> weird to me, because it seems like the driver is automatically
>> wrapping the
>> CTAS with a "SELECT * FROM ... LIMIT 0"?
>>
>> An additional complication is that this isn't straight up JDBC. This
>> is
>> Python calling out to the JDBC driver, so admittedly I might be
>> throwing a
>> wrench in there somewhere. But it doesn't make sense to me that
>> either the
>> Python layer or JDBC would modify the query in that way. I've checked
>> the
>> Python wrapper source and there I couldn't find anything to indicate
>> it
>> might be changing the query. I also briefly sifted through the JDBC
>> code
>> and couldn't find anything as well.
>>
>> Any clues as to what might be going on?
>>
>> Wes
>>
>>
>>
>
Re: JDBC SQL parse error on CTAS
Posted by Wesley Chow <we...@chartbeat.com>.
Is the point in the LIMIT 0 to trick the planner into not executing the
statement in some way? When I apply the patch to not wrap CTAS with a
LIMIT0 query I get "table exists" errors, which implies that the query is
getting executed twice. Just trying to figure this out so I know if I
should fix this on the Drill side or work around it by avoiding prepared
statements...
Thanks,
Wes
On Mon, Apr 24, 2017 at 9:38 PM, Wesley Chow <we...@chartbeat.com> wrote:
> Yes that sounds like it. I'm looking at this patch for the corresponding
> issue:
>
> https://github.com/apache/drill/pull/698/files#diff-
> 6b274c93088e03d7ee1d7d266fcf4ac8R133
>
> And I don't understand why the issue says that this patch only handles the
> "show schema" case, but it looks like it handles any case that isn't
> SELECT. That said, for it to be completely correct it should also handle
> UPDATEs, right? I'm admittedly unclear on what the purpose of the "LIMIT 0"
> is...
>
> I'll try applying this patch to see if it fixes my issue.
>
> Thanks,
> Wes
>
>
> On Mon, Apr 24, 2017 at 5:49 PM, Zelaine Fong <zf...@mapr.com> wrote:
>
>> I suspect you’ve hit https://issues.apache.org/jira/browse/DRILL-5136.
>> See the comment trail which mentions the problem with CTAS.
>>
>> -- Zelaine
>>
>> On 4/24/17, 2:45 PM, "Kunal Khatua" <kk...@mapr.com> wrote:
>>
>> I'm not sure if you can do a
>>
>> select * from (CTAS ..)
>>
>> The output of CTAS really is only a metric of the rows that got
>> written by the various fragments. Are you able to run the query in SQLLine?
>>
>> The parse error does a pretty good job of pointing out where the
>> query appears to have broken the parser's semantics. So it is possible
>> that you need some escape characters to handle the query, without which,
>> Python might be altering the query.
>>
>>
>> Kunal
>>
>> ________________________________
>> From: Wesley Chow <we...@chartbeat.com>
>> Sent: Monday, April 24, 2017 2:33:14 PM
>> To: user@drill.apache.org
>> Subject: JDBC SQL parse error on CTAS
>>
>> I'm seeing a funny issue where issuing SELECT statements through the
>> JDBC
>> driver is fine, but when I prepend them with CREATE TABLE AS, I start
>> getting parse errors. If I copy+paste the query string into sqlline
>> the
>> CTAS works. When the thing breaks, it gives me an exception with
>> message
>> like:
>>
>> SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
>>
>> And complains about a parse error at the CREATE TABLE step. Now this
>> is
>> weird to me, because it seems like the driver is automatically
>> wrapping the
>> CTAS with a "SELECT * FROM ... LIMIT 0"?
>>
>> An additional complication is that this isn't straight up JDBC. This
>> is
>> Python calling out to the JDBC driver, so admittedly I might be
>> throwing a
>> wrench in there somewhere. But it doesn't make sense to me that
>> either the
>> Python layer or JDBC would modify the query in that way. I've checked
>> the
>> Python wrapper source and there I couldn't find anything to indicate
>> it
>> might be changing the query. I also briefly sifted through the JDBC
>> code
>> and couldn't find anything as well.
>>
>> Any clues as to what might be going on?
>>
>> Wes
>>
>>
>>
>
Re: JDBC SQL parse error on CTAS
Posted by Wesley Chow <we...@chartbeat.com>.
Yes that sounds like it. I'm looking at this patch for the corresponding
issue:
https://github.com/apache/drill/pull/698/files#diff-6b274c93088e03d7ee1d7d266fcf4ac8R133
And I don't understand why the issue says that this patch only handles the
"show schema" case, but it looks like it handles any case that isn't
SELECT. That said, for it to be completely correct it should also handle
UPDATEs, right? I'm admittedly unclear on what the purpose of the "LIMIT 0"
is...
I'll try applying this patch to see if it fixes my issue.
Thanks,
Wes
On Mon, Apr 24, 2017 at 5:49 PM, Zelaine Fong <zf...@mapr.com> wrote:
> I suspect you’ve hit https://issues.apache.org/jira/browse/DRILL-5136.
> See the comment trail which mentions the problem with CTAS.
>
> -- Zelaine
>
> On 4/24/17, 2:45 PM, "Kunal Khatua" <kk...@mapr.com> wrote:
>
> I'm not sure if you can do a
>
> select * from (CTAS ..)
>
> The output of CTAS really is only a metric of the rows that got
> written by the various fragments. Are you able to run the query in SQLLine?
>
> The parse error does a pretty good job of pointing out where the query
> appears to have broken the parser's semantics. So it is possible that you
> need some escape characters to handle the query, without which, Python
> might be altering the query.
>
>
> Kunal
>
> ________________________________
> From: Wesley Chow <we...@chartbeat.com>
> Sent: Monday, April 24, 2017 2:33:14 PM
> To: user@drill.apache.org
> Subject: JDBC SQL parse error on CTAS
>
> I'm seeing a funny issue where issuing SELECT statements through the
> JDBC
> driver is fine, but when I prepend them with CREATE TABLE AS, I start
> getting parse errors. If I copy+paste the query string into sqlline the
> CTAS works. When the thing breaks, it gives me an exception with
> message
> like:
>
> SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
>
> And complains about a parse error at the CREATE TABLE step. Now this is
> weird to me, because it seems like the driver is automatically
> wrapping the
> CTAS with a "SELECT * FROM ... LIMIT 0"?
>
> An additional complication is that this isn't straight up JDBC. This is
> Python calling out to the JDBC driver, so admittedly I might be
> throwing a
> wrench in there somewhere. But it doesn't make sense to me that either
> the
> Python layer or JDBC would modify the query in that way. I've checked
> the
> Python wrapper source and there I couldn't find anything to indicate it
> might be changing the query. I also briefly sifted through the JDBC
> code
> and couldn't find anything as well.
>
> Any clues as to what might be going on?
>
> Wes
>
>
>
Re: JDBC SQL parse error on CTAS
Posted by Zelaine Fong <zf...@mapr.com>.
I suspect you’ve hit https://issues.apache.org/jira/browse/DRILL-5136. See the comment trail which mentions the problem with CTAS.
-- Zelaine
On 4/24/17, 2:45 PM, "Kunal Khatua" <kk...@mapr.com> wrote:
I'm not sure if you can do a
select * from (CTAS ..)
The output of CTAS really is only a metric of the rows that got written by the various fragments. Are you able to run the query in SQLLine?
The parse error does a pretty good job of pointing out where the query appears to have broken the parser's semantics. So it is possible that you need some escape characters to handle the query, without which, Python might be altering the query.
Kunal
________________________________
From: Wesley Chow <we...@chartbeat.com>
Sent: Monday, April 24, 2017 2:33:14 PM
To: user@drill.apache.org
Subject: JDBC SQL parse error on CTAS
I'm seeing a funny issue where issuing SELECT statements through the JDBC
driver is fine, but when I prepend them with CREATE TABLE AS, I start
getting parse errors. If I copy+paste the query string into sqlline the
CTAS works. When the thing breaks, it gives me an exception with message
like:
SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
And complains about a parse error at the CREATE TABLE step. Now this is
weird to me, because it seems like the driver is automatically wrapping the
CTAS with a "SELECT * FROM ... LIMIT 0"?
An additional complication is that this isn't straight up JDBC. This is
Python calling out to the JDBC driver, so admittedly I might be throwing a
wrench in there somewhere. But it doesn't make sense to me that either the
Python layer or JDBC would modify the query in that way. I've checked the
Python wrapper source and there I couldn't find anything to indicate it
might be changing the query. I also briefly sifted through the JDBC code
and couldn't find anything as well.
Any clues as to what might be going on?
Wes
Re: JDBC SQL parse error on CTAS
Posted by Kunal Khatua <kk...@mapr.com>.
I'm not sure if you can do a
select * from (CTAS ..)
The output of CTAS really is only a metric of the rows that got written by the various fragments. Are you able to run the query in SQLLine?
The parse error does a pretty good job of pointing out where the query appears to have broken the parser's semantics. So it is possible that you need some escape characters to handle the query, without which, Python might be altering the query.
Kunal
________________________________
From: Wesley Chow <we...@chartbeat.com>
Sent: Monday, April 24, 2017 2:33:14 PM
To: user@drill.apache.org
Subject: JDBC SQL parse error on CTAS
I'm seeing a funny issue where issuing SELECT statements through the JDBC
driver is fine, but when I prepend them with CREATE TABLE AS, I start
getting parse errors. If I copy+paste the query string into sqlline the
CTAS works. When the thing breaks, it gives me an exception with message
like:
SQL Query SELECT * FROM (CREATE TABLE foo ... ) LIMIT 0
And complains about a parse error at the CREATE TABLE step. Now this is
weird to me, because it seems like the driver is automatically wrapping the
CTAS with a "SELECT * FROM ... LIMIT 0"?
An additional complication is that this isn't straight up JDBC. This is
Python calling out to the JDBC driver, so admittedly I might be throwing a
wrench in there somewhere. But it doesn't make sense to me that either the
Python layer or JDBC would modify the query in that way. I've checked the
Python wrapper source and there I couldn't find anything to indicate it
might be changing the query. I also briefly sifted through the JDBC code
and couldn't find anything as well.
Any clues as to what might be going on?
Wes