You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Vitalii Diravka <vi...@gmail.com> on 2016/06/29 17:15:50 UTC
Implement "DROP TABLE IIF EXISTS" statement
Hi all!
I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF EXISTS"
statements in Drill (DRILL-4673
<https://issues.apache.org/jira/browse/DRILL-4673>).
The reason of using "IIF" is inability of adding "IF" keyword to
non-reserved words list (due to SQL:2011 standard which calcite parser
uses). Adding of "IF" to reserved words list leads to not working hive "IF"
UDF.
I'm interested are there any concerns with using "IIF" ?
Kind regards
Vitalii
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Neeraja Rentachintala <nr...@maprtech.com>.
sounds good.
We need to clear document this - specifically the Hive UDF IF syntax issue.
-Neeraja
On Fri, Jul 1, 2016 at 11:07 AM, Vitalii Diravka <vi...@gmail.com>
wrote:
> Agree with the last decision to use "IF EXISTS" statement and `if` udf with
> backticks.
> It is acceptable option.
>
> Thank you for valuable advices.
>
> Kind regards
> Vitalii
>
> 2016-06-30 17:22 GMT+00:00 John Omernik <jo...@omernik.com>:
>
> > I agree with Julian. If we can backtick quote Hive's if and have an
> option
> > for Hive users, it would be nice. But Hive made a mess, and there is
> > precedent for IF. This makes from a cluster administration perspective,
> > and even being a Hive user, as long as I had an option (with backticks)
> to
> > allow me to move forward, I'd understand and accept the required changes.
> >
> > John
> >
> >
> > On Thu, Jun 30, 2016 at 12:00 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> > > Even though it’s not standard, several other databases have DROP TABLE
> …
> > > IF EXISTS (MySQL [1]; Postgres [2] and SQL Server 2016 [3] put the “IF
> > > EXISTS” before the table name). I know there are problems with the IF
> > > keyword clashing with the Hive “IF” function, but I think it would be
> > crazy
> > > to do “IIF EXISTS”.
> > >
> > > I’d block Hive’s “IF” function, frankly. They screwed up. No need to
> > > propagate their mess into Drill.
> > >
> > > Julian
> > >
> > > [1] http://dev.mysql.com/doc/refman/5.7/en/drop-table.html <
> > > http://dev.mysql.com/doc/refman/5.7/en/drop-table.html>
> > >
> > > [2] https://www.postgresql.org/docs/8.2/static/sql-droptable.html <
> > > https://www.postgresql.org/docs/8.2/static/sql-droptable.html>
> > >
> > > [3]
> > >
> >
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> > > <
> > >
> >
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> > > >
> > >
> > > > On Jun 30, 2016, at 5:06 AM, Khurram Faraaz <kf...@maprtech.com>
> > > wrote:
> > > >
> > > > I looked at the SQL standard and I did not find that IF EXISTS is a
> > part
> > > of
> > > > DROP TABLE syntax, please see below.
> > > >
> > > > INTERNATIONAL STANDARD
> > > > ISO/IEC 9075-2
> > > > Fourth edition 2011-12-15
> > > >
> > > >
> > > > Format
> > > > <drop table statement> ::=
> > > > DROP TABLE <table name> <drop behavior>
> > > >
> > > > <drop behavior> ::=
> > > > CASCADE
> > > > | RESTRICT
> > > >
> > > > On Thu, Jun 30, 2016 at 3:44 PM, Arina Yelchiyeva <
> > > > arina.yelchiyeva@gmail.com> wrote:
> > > >
> > > >> To sum up currently we are facing two options:
> > > >>
> > > >> 1. Add IF as keyword.
> > > >> Pros:
> > > >> DROP TABLE / VIEW IF EXISTS will work
> > > >> Cons:
> > > >> if function (loaded from Hive) will stop working. In this case users
> > > will
> > > >> have two options:
> > > >> a) surround if with backticks (ex: select `if`(condition,option1,
> > > option2)
> > > >> from table)
> > > >> b) replace if function with case statement
> > > >>
> > > >> 2. Use IIF instead of IF
> > > >> Pros:
> > > >> if function will work, no backward compatibility issues.
> > > >> Cons:
> > > >> uncommon syntax for IF EXISTS statement
> > > >>
> > > >> So far none of this options seems to be ideal.
> > > >>
> > > >> Kind regards
> > > >> Arina
> > > >>
> > > >>
> > > >> On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com>
> > > wrote:
> > > >>
> > > >>> Hi Vitalii,
> > > >>>
> > > >>> This will be a nice improvement. Your question about “IIF” vs. “IF”
> > is
> > > in
> > > >>> the context of one small enhancement. But, it raises a larger
> > question
> > > >>> (which is beyond the scope of your project, but is worth discussing
> > > >> anyway.)
> > > >>>
> > > >>> That larger issue is that we really should modify the Drill SQL
> > parser
> > > to
> > > >>> better handle keywords vs. identifiers. That is, the following
> > > >>> “pathological” statement should be valid:
> > > >>>
> > > >>> SELECT select, from FROM from, where WHERE from.select =
> where.from;
> > > >>>
> > > >>> This seems very confusing to us humans. But, to the SQL grammar the
> > > above
> > > >>> is unambiguous. SQL syntax determines where a keyword is valid. All
> > > other
> > > >>> uses of that keyword can easily be interpreted as an identifier.
> > > Further,
> > > >>> the location of the identifier determines whether to interpreted it
> > as
> > > a
> > > >>> column, table, schema, function, etc. For example, a keyword will
> > never
> > > >>> appear in a select list, from list or where expression.
> Technically,
> > we
> > > >>> could introduce distinct name spaces for keywords, columns, tables,
> > > >>> functions and so on.
> > > >>>
> > > >>> Without this change we run two risks:
> > > >>>
> > > >>> 1. We can’t use proper SQL syntax when we need it (as in your
> > project.)
> > > >>> 2. We risk breaking queries when we add new keywords (as in the
> > dynamic
> > > >>> UDF project.)
> > > >>>
> > > >>> This is not a new idea. Informix made this very change to their
> > parser,
> > > >>> for similar reasons — and did it back in the late ‘80s using C and
> > YACC
> > > >>> (for you old timers.)
> > > >>>
> > > >>> I’m not familiar with the Calcite parser. Anyone know what would be
> > > >>> involved in making this change so Vitalii can use proper SQL
> syntax?
> > > >>>
> > > >>> - Paul
> > > >>>
> > > >>>
> > > >>>> On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <
> > > >> vitalii.diravka@gmail.com>
> > > >>> wrote:
> > > >>>>
> > > >>>> Hi all!
> > > >>>>
> > > >>>> I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF
> > > >> EXISTS"
> > > >>>> statements in Drill (DRILL-4673
> > > >>>> <https://issues.apache.org/jira/browse/DRILL-4673>).
> > > >>>> The reason of using "IIF" is inability of adding "IF" keyword to
> > > >>>> non-reserved words list (due to SQL:2011 standard which calcite
> > parser
> > > >>>> uses). Adding of "IF" to reserved words list leads to not working
> > hive
> > > >>> "IF"
> > > >>>> UDF.
> > > >>>>
> > > >>>> I'm interested are there any concerns with using "IIF" ?
> > > >>>>
> > > >>>> Kind regards
> > > >>>> Vitalii
> > > >>>
> > > >>>
> > > >>
> > >
> > >
> >
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Vitalii Diravka <vi...@gmail.com>.
Agree with the last decision to use "IF EXISTS" statement and `if` udf with
backticks.
It is acceptable option.
Thank you for valuable advices.
Kind regards
Vitalii
2016-06-30 17:22 GMT+00:00 John Omernik <jo...@omernik.com>:
> I agree with Julian. If we can backtick quote Hive's if and have an option
> for Hive users, it would be nice. But Hive made a mess, and there is
> precedent for IF. This makes from a cluster administration perspective,
> and even being a Hive user, as long as I had an option (with backticks) to
> allow me to move forward, I'd understand and accept the required changes.
>
> John
>
>
> On Thu, Jun 30, 2016 at 12:00 PM, Julian Hyde <jh...@apache.org> wrote:
>
> > Even though it’s not standard, several other databases have DROP TABLE …
> > IF EXISTS (MySQL [1]; Postgres [2] and SQL Server 2016 [3] put the “IF
> > EXISTS” before the table name). I know there are problems with the IF
> > keyword clashing with the Hive “IF” function, but I think it would be
> crazy
> > to do “IIF EXISTS”.
> >
> > I’d block Hive’s “IF” function, frankly. They screwed up. No need to
> > propagate their mess into Drill.
> >
> > Julian
> >
> > [1] http://dev.mysql.com/doc/refman/5.7/en/drop-table.html <
> > http://dev.mysql.com/doc/refman/5.7/en/drop-table.html>
> >
> > [2] https://www.postgresql.org/docs/8.2/static/sql-droptable.html <
> > https://www.postgresql.org/docs/8.2/static/sql-droptable.html>
> >
> > [3]
> >
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> > <
> >
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> > >
> >
> > > On Jun 30, 2016, at 5:06 AM, Khurram Faraaz <kf...@maprtech.com>
> > wrote:
> > >
> > > I looked at the SQL standard and I did not find that IF EXISTS is a
> part
> > of
> > > DROP TABLE syntax, please see below.
> > >
> > > INTERNATIONAL STANDARD
> > > ISO/IEC 9075-2
> > > Fourth edition 2011-12-15
> > >
> > >
> > > Format
> > > <drop table statement> ::=
> > > DROP TABLE <table name> <drop behavior>
> > >
> > > <drop behavior> ::=
> > > CASCADE
> > > | RESTRICT
> > >
> > > On Thu, Jun 30, 2016 at 3:44 PM, Arina Yelchiyeva <
> > > arina.yelchiyeva@gmail.com> wrote:
> > >
> > >> To sum up currently we are facing two options:
> > >>
> > >> 1. Add IF as keyword.
> > >> Pros:
> > >> DROP TABLE / VIEW IF EXISTS will work
> > >> Cons:
> > >> if function (loaded from Hive) will stop working. In this case users
> > will
> > >> have two options:
> > >> a) surround if with backticks (ex: select `if`(condition,option1,
> > option2)
> > >> from table)
> > >> b) replace if function with case statement
> > >>
> > >> 2. Use IIF instead of IF
> > >> Pros:
> > >> if function will work, no backward compatibility issues.
> > >> Cons:
> > >> uncommon syntax for IF EXISTS statement
> > >>
> > >> So far none of this options seems to be ideal.
> > >>
> > >> Kind regards
> > >> Arina
> > >>
> > >>
> > >> On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com>
> > wrote:
> > >>
> > >>> Hi Vitalii,
> > >>>
> > >>> This will be a nice improvement. Your question about “IIF” vs. “IF”
> is
> > in
> > >>> the context of one small enhancement. But, it raises a larger
> question
> > >>> (which is beyond the scope of your project, but is worth discussing
> > >> anyway.)
> > >>>
> > >>> That larger issue is that we really should modify the Drill SQL
> parser
> > to
> > >>> better handle keywords vs. identifiers. That is, the following
> > >>> “pathological” statement should be valid:
> > >>>
> > >>> SELECT select, from FROM from, where WHERE from.select = where.from;
> > >>>
> > >>> This seems very confusing to us humans. But, to the SQL grammar the
> > above
> > >>> is unambiguous. SQL syntax determines where a keyword is valid. All
> > other
> > >>> uses of that keyword can easily be interpreted as an identifier.
> > Further,
> > >>> the location of the identifier determines whether to interpreted it
> as
> > a
> > >>> column, table, schema, function, etc. For example, a keyword will
> never
> > >>> appear in a select list, from list or where expression. Technically,
> we
> > >>> could introduce distinct name spaces for keywords, columns, tables,
> > >>> functions and so on.
> > >>>
> > >>> Without this change we run two risks:
> > >>>
> > >>> 1. We can’t use proper SQL syntax when we need it (as in your
> project.)
> > >>> 2. We risk breaking queries when we add new keywords (as in the
> dynamic
> > >>> UDF project.)
> > >>>
> > >>> This is not a new idea. Informix made this very change to their
> parser,
> > >>> for similar reasons — and did it back in the late ‘80s using C and
> YACC
> > >>> (for you old timers.)
> > >>>
> > >>> I’m not familiar with the Calcite parser. Anyone know what would be
> > >>> involved in making this change so Vitalii can use proper SQL syntax?
> > >>>
> > >>> - Paul
> > >>>
> > >>>
> > >>>> On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <
> > >> vitalii.diravka@gmail.com>
> > >>> wrote:
> > >>>>
> > >>>> Hi all!
> > >>>>
> > >>>> I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF
> > >> EXISTS"
> > >>>> statements in Drill (DRILL-4673
> > >>>> <https://issues.apache.org/jira/browse/DRILL-4673>).
> > >>>> The reason of using "IIF" is inability of adding "IF" keyword to
> > >>>> non-reserved words list (due to SQL:2011 standard which calcite
> parser
> > >>>> uses). Adding of "IF" to reserved words list leads to not working
> hive
> > >>> "IF"
> > >>>> UDF.
> > >>>>
> > >>>> I'm interested are there any concerns with using "IIF" ?
> > >>>>
> > >>>> Kind regards
> > >>>> Vitalii
> > >>>
> > >>>
> > >>
> >
> >
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by John Omernik <jo...@omernik.com>.
I agree with Julian. If we can backtick quote Hive's if and have an option
for Hive users, it would be nice. But Hive made a mess, and there is
precedent for IF. This makes from a cluster administration perspective,
and even being a Hive user, as long as I had an option (with backticks) to
allow me to move forward, I'd understand and accept the required changes.
John
On Thu, Jun 30, 2016 at 12:00 PM, Julian Hyde <jh...@apache.org> wrote:
> Even though it’s not standard, several other databases have DROP TABLE …
> IF EXISTS (MySQL [1]; Postgres [2] and SQL Server 2016 [3] put the “IF
> EXISTS” before the table name). I know there are problems with the IF
> keyword clashing with the Hive “IF” function, but I think it would be crazy
> to do “IIF EXISTS”.
>
> I’d block Hive’s “IF” function, frankly. They screwed up. No need to
> propagate their mess into Drill.
>
> Julian
>
> [1] http://dev.mysql.com/doc/refman/5.7/en/drop-table.html <
> http://dev.mysql.com/doc/refman/5.7/en/drop-table.html>
>
> [2] https://www.postgresql.org/docs/8.2/static/sql-droptable.html <
> https://www.postgresql.org/docs/8.2/static/sql-droptable.html>
>
> [3]
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> <
> https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/
> >
>
> > On Jun 30, 2016, at 5:06 AM, Khurram Faraaz <kf...@maprtech.com>
> wrote:
> >
> > I looked at the SQL standard and I did not find that IF EXISTS is a part
> of
> > DROP TABLE syntax, please see below.
> >
> > INTERNATIONAL STANDARD
> > ISO/IEC 9075-2
> > Fourth edition 2011-12-15
> >
> >
> > Format
> > <drop table statement> ::=
> > DROP TABLE <table name> <drop behavior>
> >
> > <drop behavior> ::=
> > CASCADE
> > | RESTRICT
> >
> > On Thu, Jun 30, 2016 at 3:44 PM, Arina Yelchiyeva <
> > arina.yelchiyeva@gmail.com> wrote:
> >
> >> To sum up currently we are facing two options:
> >>
> >> 1. Add IF as keyword.
> >> Pros:
> >> DROP TABLE / VIEW IF EXISTS will work
> >> Cons:
> >> if function (loaded from Hive) will stop working. In this case users
> will
> >> have two options:
> >> a) surround if with backticks (ex: select `if`(condition,option1,
> option2)
> >> from table)
> >> b) replace if function with case statement
> >>
> >> 2. Use IIF instead of IF
> >> Pros:
> >> if function will work, no backward compatibility issues.
> >> Cons:
> >> uncommon syntax for IF EXISTS statement
> >>
> >> So far none of this options seems to be ideal.
> >>
> >> Kind regards
> >> Arina
> >>
> >>
> >> On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com>
> wrote:
> >>
> >>> Hi Vitalii,
> >>>
> >>> This will be a nice improvement. Your question about “IIF” vs. “IF” is
> in
> >>> the context of one small enhancement. But, it raises a larger question
> >>> (which is beyond the scope of your project, but is worth discussing
> >> anyway.)
> >>>
> >>> That larger issue is that we really should modify the Drill SQL parser
> to
> >>> better handle keywords vs. identifiers. That is, the following
> >>> “pathological” statement should be valid:
> >>>
> >>> SELECT select, from FROM from, where WHERE from.select = where.from;
> >>>
> >>> This seems very confusing to us humans. But, to the SQL grammar the
> above
> >>> is unambiguous. SQL syntax determines where a keyword is valid. All
> other
> >>> uses of that keyword can easily be interpreted as an identifier.
> Further,
> >>> the location of the identifier determines whether to interpreted it as
> a
> >>> column, table, schema, function, etc. For example, a keyword will never
> >>> appear in a select list, from list or where expression. Technically, we
> >>> could introduce distinct name spaces for keywords, columns, tables,
> >>> functions and so on.
> >>>
> >>> Without this change we run two risks:
> >>>
> >>> 1. We can’t use proper SQL syntax when we need it (as in your project.)
> >>> 2. We risk breaking queries when we add new keywords (as in the dynamic
> >>> UDF project.)
> >>>
> >>> This is not a new idea. Informix made this very change to their parser,
> >>> for similar reasons — and did it back in the late ‘80s using C and YACC
> >>> (for you old timers.)
> >>>
> >>> I’m not familiar with the Calcite parser. Anyone know what would be
> >>> involved in making this change so Vitalii can use proper SQL syntax?
> >>>
> >>> - Paul
> >>>
> >>>
> >>>> On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <
> >> vitalii.diravka@gmail.com>
> >>> wrote:
> >>>>
> >>>> Hi all!
> >>>>
> >>>> I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF
> >> EXISTS"
> >>>> statements in Drill (DRILL-4673
> >>>> <https://issues.apache.org/jira/browse/DRILL-4673>).
> >>>> The reason of using "IIF" is inability of adding "IF" keyword to
> >>>> non-reserved words list (due to SQL:2011 standard which calcite parser
> >>>> uses). Adding of "IF" to reserved words list leads to not working hive
> >>> "IF"
> >>>> UDF.
> >>>>
> >>>> I'm interested are there any concerns with using "IIF" ?
> >>>>
> >>>> Kind regards
> >>>> Vitalii
> >>>
> >>>
> >>
>
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Julian Hyde <jh...@apache.org>.
Even though it’s not standard, several other databases have DROP TABLE … IF EXISTS (MySQL [1]; Postgres [2] and SQL Server 2016 [3] put the “IF EXISTS” before the table name). I know there are problems with the IF keyword clashing with the Hive “IF” function, but I think it would be crazy to do “IIF EXISTS”.
I’d block Hive’s “IF” function, frankly. They screwed up. No need to propagate their mess into Drill.
Julian
[1] http://dev.mysql.com/doc/refman/5.7/en/drop-table.html <http://dev.mysql.com/doc/refman/5.7/en/drop-table.html>
[2] https://www.postgresql.org/docs/8.2/static/sql-droptable.html <https://www.postgresql.org/docs/8.2/static/sql-droptable.html>
[3] https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/ <https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/>
> On Jun 30, 2016, at 5:06 AM, Khurram Faraaz <kf...@maprtech.com> wrote:
>
> I looked at the SQL standard and I did not find that IF EXISTS is a part of
> DROP TABLE syntax, please see below.
>
> INTERNATIONAL STANDARD
> ISO/IEC 9075-2
> Fourth edition 2011-12-15
>
>
> Format
> <drop table statement> ::=
> DROP TABLE <table name> <drop behavior>
>
> <drop behavior> ::=
> CASCADE
> | RESTRICT
>
> On Thu, Jun 30, 2016 at 3:44 PM, Arina Yelchiyeva <
> arina.yelchiyeva@gmail.com> wrote:
>
>> To sum up currently we are facing two options:
>>
>> 1. Add IF as keyword.
>> Pros:
>> DROP TABLE / VIEW IF EXISTS will work
>> Cons:
>> if function (loaded from Hive) will stop working. In this case users will
>> have two options:
>> a) surround if with backticks (ex: select `if`(condition,option1, option2)
>> from table)
>> b) replace if function with case statement
>>
>> 2. Use IIF instead of IF
>> Pros:
>> if function will work, no backward compatibility issues.
>> Cons:
>> uncommon syntax for IF EXISTS statement
>>
>> So far none of this options seems to be ideal.
>>
>> Kind regards
>> Arina
>>
>>
>> On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com> wrote:
>>
>>> Hi Vitalii,
>>>
>>> This will be a nice improvement. Your question about “IIF” vs. “IF” is in
>>> the context of one small enhancement. But, it raises a larger question
>>> (which is beyond the scope of your project, but is worth discussing
>> anyway.)
>>>
>>> That larger issue is that we really should modify the Drill SQL parser to
>>> better handle keywords vs. identifiers. That is, the following
>>> “pathological” statement should be valid:
>>>
>>> SELECT select, from FROM from, where WHERE from.select = where.from;
>>>
>>> This seems very confusing to us humans. But, to the SQL grammar the above
>>> is unambiguous. SQL syntax determines where a keyword is valid. All other
>>> uses of that keyword can easily be interpreted as an identifier. Further,
>>> the location of the identifier determines whether to interpreted it as a
>>> column, table, schema, function, etc. For example, a keyword will never
>>> appear in a select list, from list or where expression. Technically, we
>>> could introduce distinct name spaces for keywords, columns, tables,
>>> functions and so on.
>>>
>>> Without this change we run two risks:
>>>
>>> 1. We can’t use proper SQL syntax when we need it (as in your project.)
>>> 2. We risk breaking queries when we add new keywords (as in the dynamic
>>> UDF project.)
>>>
>>> This is not a new idea. Informix made this very change to their parser,
>>> for similar reasons — and did it back in the late ‘80s using C and YACC
>>> (for you old timers.)
>>>
>>> I’m not familiar with the Calcite parser. Anyone know what would be
>>> involved in making this change so Vitalii can use proper SQL syntax?
>>>
>>> - Paul
>>>
>>>
>>>> On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <
>> vitalii.diravka@gmail.com>
>>> wrote:
>>>>
>>>> Hi all!
>>>>
>>>> I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF
>> EXISTS"
>>>> statements in Drill (DRILL-4673
>>>> <https://issues.apache.org/jira/browse/DRILL-4673>).
>>>> The reason of using "IIF" is inability of adding "IF" keyword to
>>>> non-reserved words list (due to SQL:2011 standard which calcite parser
>>>> uses). Adding of "IF" to reserved words list leads to not working hive
>>> "IF"
>>>> UDF.
>>>>
>>>> I'm interested are there any concerns with using "IIF" ?
>>>>
>>>> Kind regards
>>>> Vitalii
>>>
>>>
>>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Khurram Faraaz <kf...@maprtech.com>.
I looked at the SQL standard and I did not find that IF EXISTS is a part of
DROP TABLE syntax, please see below.
INTERNATIONAL STANDARD
ISO/IEC 9075-2
Fourth edition 2011-12-15
Format
<drop table statement> ::=
DROP TABLE <table name> <drop behavior>
<drop behavior> ::=
CASCADE
| RESTRICT
On Thu, Jun 30, 2016 at 3:44 PM, Arina Yelchiyeva <
arina.yelchiyeva@gmail.com> wrote:
> To sum up currently we are facing two options:
>
> 1. Add IF as keyword.
> Pros:
> DROP TABLE / VIEW IF EXISTS will work
> Cons:
> if function (loaded from Hive) will stop working. In this case users will
> have two options:
> a) surround if with backticks (ex: select `if`(condition,option1, option2)
> from table)
> b) replace if function with case statement
>
> 2. Use IIF instead of IF
> Pros:
> if function will work, no backward compatibility issues.
> Cons:
> uncommon syntax for IF EXISTS statement
>
> So far none of this options seems to be ideal.
>
> Kind regards
> Arina
>
>
> On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com> wrote:
>
> > Hi Vitalii,
> >
> > This will be a nice improvement. Your question about “IIF” vs. “IF” is in
> > the context of one small enhancement. But, it raises a larger question
> > (which is beyond the scope of your project, but is worth discussing
> anyway.)
> >
> > That larger issue is that we really should modify the Drill SQL parser to
> > better handle keywords vs. identifiers. That is, the following
> > “pathological” statement should be valid:
> >
> > SELECT select, from FROM from, where WHERE from.select = where.from;
> >
> > This seems very confusing to us humans. But, to the SQL grammar the above
> > is unambiguous. SQL syntax determines where a keyword is valid. All other
> > uses of that keyword can easily be interpreted as an identifier. Further,
> > the location of the identifier determines whether to interpreted it as a
> > column, table, schema, function, etc. For example, a keyword will never
> > appear in a select list, from list or where expression. Technically, we
> > could introduce distinct name spaces for keywords, columns, tables,
> > functions and so on.
> >
> > Without this change we run two risks:
> >
> > 1. We can’t use proper SQL syntax when we need it (as in your project.)
> > 2. We risk breaking queries when we add new keywords (as in the dynamic
> > UDF project.)
> >
> > This is not a new idea. Informix made this very change to their parser,
> > for similar reasons — and did it back in the late ‘80s using C and YACC
> > (for you old timers.)
> >
> > I’m not familiar with the Calcite parser. Anyone know what would be
> > involved in making this change so Vitalii can use proper SQL syntax?
> >
> > - Paul
> >
> >
> > > On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <
> vitalii.diravka@gmail.com>
> > wrote:
> > >
> > > Hi all!
> > >
> > > I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF
> EXISTS"
> > > statements in Drill (DRILL-4673
> > > <https://issues.apache.org/jira/browse/DRILL-4673>).
> > > The reason of using "IIF" is inability of adding "IF" keyword to
> > > non-reserved words list (due to SQL:2011 standard which calcite parser
> > > uses). Adding of "IF" to reserved words list leads to not working hive
> > "IF"
> > > UDF.
> > >
> > > I'm interested are there any concerns with using "IIF" ?
> > >
> > > Kind regards
> > > Vitalii
> >
> >
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Arina Yelchiyeva <ar...@gmail.com>.
To sum up currently we are facing two options:
1. Add IF as keyword.
Pros:
DROP TABLE / VIEW IF EXISTS will work
Cons:
if function (loaded from Hive) will stop working. In this case users will
have two options:
a) surround if with backticks (ex: select `if`(condition,option1, option2)
from table)
b) replace if function with case statement
2. Use IIF instead of IF
Pros:
if function will work, no backward compatibility issues.
Cons:
uncommon syntax for IF EXISTS statement
So far none of this options seems to be ideal.
Kind regards
Arina
On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com> wrote:
> Hi Vitalii,
>
> This will be a nice improvement. Your question about “IIF” vs. “IF” is in
> the context of one small enhancement. But, it raises a larger question
> (which is beyond the scope of your project, but is worth discussing anyway.)
>
> That larger issue is that we really should modify the Drill SQL parser to
> better handle keywords vs. identifiers. That is, the following
> “pathological” statement should be valid:
>
> SELECT select, from FROM from, where WHERE from.select = where.from;
>
> This seems very confusing to us humans. But, to the SQL grammar the above
> is unambiguous. SQL syntax determines where a keyword is valid. All other
> uses of that keyword can easily be interpreted as an identifier. Further,
> the location of the identifier determines whether to interpreted it as a
> column, table, schema, function, etc. For example, a keyword will never
> appear in a select list, from list or where expression. Technically, we
> could introduce distinct name spaces for keywords, columns, tables,
> functions and so on.
>
> Without this change we run two risks:
>
> 1. We can’t use proper SQL syntax when we need it (as in your project.)
> 2. We risk breaking queries when we add new keywords (as in the dynamic
> UDF project.)
>
> This is not a new idea. Informix made this very change to their parser,
> for similar reasons — and did it back in the late ‘80s using C and YACC
> (for you old timers.)
>
> I’m not familiar with the Calcite parser. Anyone know what would be
> involved in making this change so Vitalii can use proper SQL syntax?
>
> - Paul
>
>
> > On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <vi...@gmail.com>
> wrote:
> >
> > Hi all!
> >
> > I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF EXISTS"
> > statements in Drill (DRILL-4673
> > <https://issues.apache.org/jira/browse/DRILL-4673>).
> > The reason of using "IIF" is inability of adding "IF" keyword to
> > non-reserved words list (due to SQL:2011 standard which calcite parser
> > uses). Adding of "IF" to reserved words list leads to not working hive
> "IF"
> > UDF.
> >
> > I'm interested are there any concerns with using "IIF" ?
> >
> > Kind regards
> > Vitalii
>
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Arina Yelchiyeva <ar...@gmail.com>.
To sum up currently we are facing two options:
1. Add IF as keyword.
Pros:
DROP TABLE / VIEW IF EXISTS will work
Cons:
if function (loaded from Hive) will stop working. In this case users will
have two options:
a) surround if with backticks (ex: select `if`(condition,option1, option2)
from table)
b) replace if function with case statement
2. Use IIF instead of IF
Pros:
if function will work, no backward compatibility issues.
Cons:
uncommon syntax for IF EXISTS statement
So far none of this options seems to be ideal.
Kind regards
Arina
On Wed, Jun 29, 2016 at 8:56 PM Paul Rogers <pr...@maprtech.com> wrote:
> Hi Vitalii,
>
> This will be a nice improvement. Your question about “IIF” vs. “IF” is in
> the context of one small enhancement. But, it raises a larger question
> (which is beyond the scope of your project, but is worth discussing anyway.)
>
> That larger issue is that we really should modify the Drill SQL parser to
> better handle keywords vs. identifiers. That is, the following
> “pathological” statement should be valid:
>
> SELECT select, from FROM from, where WHERE from.select = where.from;
>
> This seems very confusing to us humans. But, to the SQL grammar the above
> is unambiguous. SQL syntax determines where a keyword is valid. All other
> uses of that keyword can easily be interpreted as an identifier. Further,
> the location of the identifier determines whether to interpreted it as a
> column, table, schema, function, etc. For example, a keyword will never
> appear in a select list, from list or where expression. Technically, we
> could introduce distinct name spaces for keywords, columns, tables,
> functions and so on.
>
> Without this change we run two risks:
>
> 1. We can’t use proper SQL syntax when we need it (as in your project.)
> 2. We risk breaking queries when we add new keywords (as in the dynamic
> UDF project.)
>
> This is not a new idea. Informix made this very change to their parser,
> for similar reasons — and did it back in the late ‘80s using C and YACC
> (for you old timers.)
>
> I’m not familiar with the Calcite parser. Anyone know what would be
> involved in making this change so Vitalii can use proper SQL syntax?
>
> - Paul
>
>
> > On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <vi...@gmail.com>
> wrote:
> >
> > Hi all!
> >
> > I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF EXISTS"
> > statements in Drill (DRILL-4673
> > <https://issues.apache.org/jira/browse/DRILL-4673>).
> > The reason of using "IIF" is inability of adding "IF" keyword to
> > non-reserved words list (due to SQL:2011 standard which calcite parser
> > uses). Adding of "IF" to reserved words list leads to not working hive
> "IF"
> > UDF.
> >
> > I'm interested are there any concerns with using "IIF" ?
> >
> > Kind regards
> > Vitalii
>
>
Re: Implement "DROP TABLE IIF EXISTS" statement
Posted by Paul Rogers <pr...@maprtech.com>.
Hi Vitalii,
This will be a nice improvement. Your question about “IIF” vs. “IF” is in the context of one small enhancement. But, it raises a larger question (which is beyond the scope of your project, but is worth discussing anyway.)
That larger issue is that we really should modify the Drill SQL parser to better handle keywords vs. identifiers. That is, the following “pathological” statement should be valid:
SELECT select, from FROM from, where WHERE from.select = where.from;
This seems very confusing to us humans. But, to the SQL grammar the above is unambiguous. SQL syntax determines where a keyword is valid. All other uses of that keyword can easily be interpreted as an identifier. Further, the location of the identifier determines whether to interpreted it as a column, table, schema, function, etc. For example, a keyword will never appear in a select list, from list or where expression. Technically, we could introduce distinct name spaces for keywords, columns, tables, functions and so on.
Without this change we run two risks:
1. We can’t use proper SQL syntax when we need it (as in your project.)
2. We risk breaking queries when we add new keywords (as in the dynamic UDF project.)
This is not a new idea. Informix made this very change to their parser, for similar reasons — and did it back in the late ‘80s using C and YACC (for you old timers.)
I’m not familiar with the Calcite parser. Anyone know what would be involved in making this change so Vitalii can use proper SQL syntax?
- Paul
> On Jun 29, 2016, at 10:15 AM, Vitalii Diravka <vi...@gmail.com> wrote:
>
> Hi all!
>
> I'm going to implement "DROP TABLE IIF EXISTS" and "DROP VIEW IIF EXISTS"
> statements in Drill (DRILL-4673
> <https://issues.apache.org/jira/browse/DRILL-4673>).
> The reason of using "IIF" is inability of adding "IF" keyword to
> non-reserved words list (due to SQL:2011 standard which calcite parser
> uses). Adding of "IF" to reserved words list leads to not working hive "IF"
> UDF.
>
> I'm interested are there any concerns with using "IIF" ?
>
> Kind regards
> Vitalii