drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Neeraja Rentachintala <nrentachint...@maprtech.com>
Subject Re: Implement "DROP TABLE IIF EXISTS" statement
Date Fri, 01 Jul 2016 18:11:08 GMT
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 <vitalii.diravka@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 <john@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 <jhyde@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 <kfaraaz@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 <progers@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
> > > >>>
> > > >>>
> > > >>
> > >
> > >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message