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 <[email protected]> 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 <[email protected]>: > > > 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 <[email protected]> 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 <[email protected]> > > > 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 < > > > > [email protected]> 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 <[email protected]> > > > 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 < > > > >> [email protected]> > > > >>> 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 > > > >>> > > > >>> > > > >> > > > > > > > > >
