Various parser bugs / missing features

[lukaseder]

This is a continuation of #6485 and will be continued in #7518

---

Some features were not yet supported in the jOOQ 3.9 parser. We'll fix them all in this single issue in order not to create too many issues. Here's the list of fixed things:

SELECT features

• SEL as a synonym for the SELECT keyword (Teradata)
  • DEL for DELETE
  • UPD for UPDATE
  • INS for INSERT
• SEEK clause
• WITH with UNION: e.g. WITH t(a) AS (SELECT 1) SELECT a FROM t UNION SELECT 1
• WITH with parenthesised UNION: e.g. WITH t(a) AS (SELECT 1) (SELECT a FROM t UNION SELECT 1)
• Hints
  • Oracle style
    • Table hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
• FETCH n PERCENT ROWS or TOP n PERCENT clauses (support in jOOQ, first: Add support for Oracle 12c FETCH N PERCENT ROWS and SQL Server TOP n PERCENT clauses #5594)
• COUNT(t.*) (support in jOOQ, first: Add DSL.count(SelectFieldOrAsterisk) and countDistinct(SelectFieldOrAsterisk) #7288)
• Nested unions cannot be parsed, e.g.
  • select x from ((select 1 as x) union (select 2 as x)) t
  • select ((select 1) union (select 2))
• Support parsing identifiers as predicates (see: Support parsing column references as predicates #7266)
• VALUES in CTE: WITH a AS (VALUES (1)) SELECT * FROM a
• VALUES wherever a query primary can be used, e.g. VALUES(1, 2) UNION VALUES(2, 1)
• WINDOW clause
• LIMIT / FETCH etc shouldn't parse an DSL.inline() param, but a constant

Other statements

• CREATE INDEX .. ON t (a [ ASC | DESC ]): Explicit sort clause in indexes
• CREATE INDEX .. INCLUDE (support in jOOQ first: Add support for the CREATE INDEX .. INCLUDE syntax #7412)
• CREATE [ SPATIAL | FULLTEXT ] INDEX (Unsupported in jOOQ: MySQL index types)
• CREATE TABLE AS WITH .. SELECT
• CREATE TABLE: KEY clause is MySQL's equivalent for an index. The keyword INDEX can also be used. See: Support parsing KEY/INDEX specifications in MySQL CREATE TABLE statements #7347, Add possibility of adding an INDEX (or KEY) as a "constraint" to DDL statements #7348
• CREATE TABLE quoted data types
• CREATE TABLE with REFERENCES clauses directly on column specification
• CREATE OR REPLACE VIEW (support in jOOQ first: Add CREATE OR REPLACE VIEW #7286)
• ALTER TABLE: Multiple columns added in one statement (support in jOOQ first: Add support for ALTER TABLE .. ADD (...) to add multiple objects to a table at once #5318)
• ALTER TABLE .. RENAME TO should accept qualified target table names
• ALTER TABLE .. ADD INDEX (MySQL synonym for creating the index using CREATE INDEX)
• ALTER TABLE .. RENAME INDEX (MySQL synonym for creating the index using RENAME INDEX)
• ALTER TABLE .. DROP INDEX (MySQL synonym for creating the index using DROP INDEX)
• USE command (support in jOOQ first: Add support for SET SCHEMA and SET CATALOG #7087)
• Support ON CONFLICT DO (without constraint specification)
• MERGE without INTO (possible in SQL Server)
• MERGE with WHERE clauses (or SQL Server alternative with AND clause)
• ENABLE clause on constraints (Oracle)
• INSERT table ... (without INTO, SQL Server)

Table expressions

• Oracle outer join (+)
• SQL Server outer join *= (see also: SQL Translation Error - Transact-SQL outer joins #7300). We'll simply translate this to (+) for now. Emulation will follow
• UNNEST operator
• PostgreSQL generate_series()

Column expressions (and predicates)

• Row DISTINCT predicate (support in jOOQ first: Add is[Not]DistinctFrom() to Row[N] types #2051)
• All sorts of vendor-specific functions
  • SQL SERVER getdate()t
  • MySQL FIELD function: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field
  • Oracle DECODE function
  • Oracle TO_NUMBER() function (single argument, convert to CAST(x AS NUMBER))
  • Oracle TO_CHAR() function (single argument, convert to CAST(x AS VARCHAR))
  • Oracle TO_DATE and TO_TIMESTAMP functions (see also: Support parsing Oracle TO_DATE() and TO_TIMESTAMP() functions #7320)
  • SQL Server IIF (support in jOOQ first: Add DSL.iif(Condition, Field<T>, Field<T>) #7275): https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql
  • DATEADD et al.: https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql
• now() function (see Add DSL.now() as a synonym for DSL.currentTimestamp() #7237)
• current_timestamp(), current_time(), and current_date() can be functions, not just literals
• BIT_COUNT function (MySQL)
• BIT_AND, BIT_OR, BIT_NOT, BIT_XOR, BIT_NAND, BIT_NOR, BIT_XNOR functions
• SHL and SHR functions
• DATE, TIME, TIMESTAMP, INTERVAL column references (not literals)
• MySQL CAST types (see https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast)

Literals and data types

• PostgreSQL array data types
• PostgreSQL SERIAL2 data type alias
• Oracle VARCHAR2(n BYTE) and VARCHAR2(n CHAR) syntax (simply ignore the setting for now)
• Various interval literals
  • PostgreSQL interval literals (various syntaxes are possible: (https://www.postgresql.org/docs/current/static/datatype-datetime.html, see also Support parsing PostgreSQL-specific interval literals #7445)

Other improvements

• Better exception formatting (adding ... around abbreviated SQL strings)
• Improve unexpectedToken() error messages (see Add more error messages to the parser #7261)

New features

• Parse and fail clauses available only in the pro edition

[mkmatis]

Hi, I discovered that DDLDatabase fails when script contains CREATE INDEX and column name is the same as H2 data type, e.g.:

CREATE TABLE TASK_HISTORY
(
    TIMESTAMP TIMESTAMP NOT NULL
);

CREATE INDEX TASK_HISTORY_TIMESTAMP_INDEX ON TASK_HISTORY(TIMESTAMP);

The same thing happens for DATE.

[lukaseder]

Interesting, thanks for reporting. The parser parses the TIMESTAMP keyword as the beginning of a TIMESTAMP literal. The workaround would be to quote the identifier:

CREATE INDEX TASK_HISTORY_TIMESTAMP_INDEX ON TASK_HISTORY("TIMESTAMP");

If H2 accepts the syntax, we'll have to, as well, of course

[lukaseder]

Remaining tasks moved to #7518