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 >>> >>> >>
