Warning, /libraries/kdb/autotests/parser/data/statements.txt is written in an unsupported language. File is not indexed.

 -- This file is part of the KDE project
 -- Copyright (C) 2012 Jarosław Staniek <staniek@kde.org>
 --
 -- This library is free software; you can redistribute it and/or
 -- modify it under the terms of the GNU Library General Public
 -- License as published by the Free Software Foundation; either
 -- version 2 of the License, or (at your option) any later version.
 --
 -- This library is distributed in the hope that it will be useful,
 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 -- Library General Public License for more details.
 --
 -- You should have received a copy of the GNU Library General Public License
 -- along with this library; see the file COPYING.LIB.  If not, write to
 -- the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
 -- Boston, MA 02110-1301, USA.
 
 -- SQLITEFILE: sqlParserTest.kexi --
 
 ---------- CATEGORY: General --------------
 -- The same field used in two columns
 select id, id from persons;
 -- No args: OK
 select;
 -- Whitespace between table-identifier, dot field-identifier/asterisk
 select persons .  id from persons;
 select persons .        * from persons;
 -- ERROR: Multiple asterisks
 select *, * from persons;
 -- ERROR: Identifier cannot start with a number
 select 1id from persons;
 -- ERROR: Asterisk not allowed: no tables specified
 select *;
 -- Empty tables set
 select 1, 2;
 -- Empty column set (KDbSQL EXTENSION)
 select from cars;
 -- Totally empty statement (KDbSQL EXTENSION)
 select;
 -- Keywords and constants are case insensitive
 SElEcT * FroM cars;
 select TRue;
 select FaLse;
 selecT FALse oR TRuE;
 selecT 1 LIKe 1;
 -- Constants
 select -12;
 select 7777777;
 select -1048576;
 select 16777216;
 select "01234567890";
 select '01234567890';
 select '"';
 select '\\';
 select '\"';
 select 3.14159;
 select 3.;
 select NULL;
 -- Date Constants (KDbSQL EXTENSION)
 -- https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants
 -- Date Constants: AD
 select #2018-09-17#;
 select #09/17/2018#;
 select #2018-9-17#;
 select #9/17/2018#;
 select #2018-9-07#;
 select #9/07/2018#;
 select #2018-9-7#;
 select #9/7/2018#;
 select #2018-09-7#;
 select #09/7/2018#;
 select #1900-01-01#;
 select #01/01/1900#;
 select #1500-02-02#;
 select #02/02/1500#;
 select #0001-01-21#;
 select #01/21/0001#;
 -- ERROR: Date Constants: Invalid month
 select #2018-00-17#;
 select #00/17/2018#;
 select #2018-0-17#;
 select #0/17/2018#;
 select #2018-13-17#;
 select #13/17/2018#;
 select #2018-99-17#;
 select #99/17/2018#;
 -- ERROR: Date Constants: Invalid day
 select #2018-01-00#;
 select #01/00/2018#;
 select #2018-01-0#;
 select #01/0/2018#;
 select #2018-02-29#;
 select #02/29/2018#;
 select #2018-01-32#;
 select #01/32/2018#;
 -- ERROR: Date Constants: too many digits
 select #2018-001-30#;
 select #001/30/2018#;
 select #2018-001-030#;
 select #001/030/2018#;
 select #2018-01-030#;
 select #01/030/2018#;
 -- ERROR: Date Constants: invalid characters/format
 select #2018-1a-30#;
 select #1a/30/2018#;
 select #2018--30#;
 select #/30/2018#;
 select #2018-a-30#;
 select #a/30/2018#;
 select #2018-01-3o#;
 select #01/3o/2018#;
 select #2018-01-30;
 select #01/30/2018;
 select #2018_01-30#;
 select #01/30_2018#;
 select #2018-01_30;
 select #01_30/2018#;
 select #2018_01_30;
 select #2018-01/30;
 select #20180130;
 select #2018;
 select #2018-;
 select #2018/;
 select #/2018;
 select #2018-01;
 select #01/2018;
 select #2018-01-;
 select #/01/2018;
 select #2018/01;
 select #-2018-01-;
 select #/01/2018;
 select #-2018;
 select ##-2018;
 select ##/2018;
 select #;
 select ##;
 select #2018-01-01-01#;
 select #01/01/01/2018#;
 select #++18119-10-30#;
 select #10/30/++18119#;
 select #+-18119-10-30#;
 select #10/30/+-18119#;
 select #-+18119-10-30#;
 select #10/30/-+18119#;
 select #--18119-10-30#;
 select #10/30/--18119#;
 select # 2018-03-08#;
 select #2018-03-08 #;
 select # 2018-03-08 #;
 select # 2018 -03-08 #;
 select #2018-03 -08#;
 select #2018 - 03 - 08#;
 select #201 -03-08 #;
 select #2018- 3-08 #;
 select #2018-03- 8 #;
 -- ERROR: Date Constant: at least 4 digit year required
 select #189-12-13#;
 select #12/13/189#;
 -- Date Constants: optional "+" prefix for years 1-9999
 select #+2018-12-02#;
 select #12/02/+2018#;
 select #+1500-02-02#;
 select #12/02/+1500#;
 select #+1500-3-2#;
 select #3/02/+1500#;
 select #+02012-12-13#;
 select #12/13/+02012#;
 -- Date Constants, year > 9999
 select #+123456789-12-13#;
 select #12/13/+123456789#;
 select #+000123456789-12-13#;
 select #12/13/+000123456789#;
 -- ERROR: Date Constant: More than 4-digit year so "+" prefix is required
 select #123456789-12-13#;
 select #12/13/123456789#;
 -- 1 BC Date Constants
 select #+0000-01-30#;
 select #01/30/+0000#;
 -- ERROR: Date Constant: 1 BC requires +0000 year
 select #0000-01-01#;
 select #01/01/0000#;
 select #-0000-01-01#;
 select #01/01/-0000#;
 select #+000-01-01#;
 select #01/01/+000#;
 select #-000-01-01#;
 select #01/01/-000#;
 select #+00-01-01#;
 select #01/01/+00#;
 select #-00-01-01#;
 select #01/01/-00#;
 select #+0-01-01#;
 select #01/01/+0#;
 select #-0-01-01#;
 select #01/01/-0#;
 -- 2 BC Date Constants
 select #-0001-03-04#;
 select #03/04/-0001#;
 select #-5001-03-04#;
 select #03/04/-5001#;
 -- <= 10000 BC Date Constants
 select #-9999-03-04#;
 select #04/03/-9999#;
 select #-10000-03-04#;
 select #04/03/-10000#;
 select #-543210000-03-04#;
 select #04/03/-543210000#;
 select #-00543210000-03-04#;
 select #04/03/-00543210000#;
 -- ERROR: Date Constant: MSA format
 select #24 Dec 2014#;
 select #24 December 2014#;
 select #December 24, 2014#;
 select #Dec 24, 2014#;
 
 -- Time Constants (KDbSQL EXTENSION)
 -- https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants
 -- Time Constants: 24h mode
 select #01:17:59#;
 select #01:07:59#;
 select #01:07:09#;
 select #1:07:09#;
 select #01:7:09#;
 select #01:07:9#;
 select #01:7:9#;
 select #1:7:09#;
 select #1:07:9#;
 select #1:7:9#;
 select #0:0:0#;
 select #23:11:29#;
 select #13:17:59#;
 select #23:11#;
 select #03:01#;
 select #3:01#;
 select #03:1#;
 select #3:1#;
 -- Time Constants: 12h mode
 select #11:11:29 AM#;
 select #11:11:29 PM#;
 select #11:11:29AM#;
 select #11:11:29PM#;
 select #11:11:29am#;
 select #11:11:29pm#;
 select #11:11:29   AM#;
 select #11:11:29   PM#;
 select #1:31:19 aM#;
 select #1:31:19 Am#;
 select #1:31:19 am#;
 select #1:31:19 pM#;
 select #1:31:19 pM#;
 select #11:11 AM#;
 select #11:11 PM#;
 select #11:11    AM#;
 select #11:11    PM#;
 select #11:11AM#;
 select #11:11PM#;
 select #11:11am#;
 select #11:11pm#;
 select #1:31 aM#;
 select #1:31 Am#;
 select #1:31 am#;
 select #1:31 pM#;
 select #1:31 pM#;
 -- ERROR: Time Constants: invalid characters/format
 select #001:17:59#;
 select #01:017:59#;
 select #01:017:059#;
 select #001:017:59#;
 select #01:017:059#;
 select #001:17:059#;
 select #001;017:059#;
 select #01:17:59;
 select #01::17:59#;
 select ##01::17:59#;
 select #01#;
 select #0#;
 select #11:17 #;
 select #11: 17#;
 select #11 : 17#;
 select #11 :17#;
 select #11::17#;
 select # 11:17#;
 select #11:17 AM #;
 select #1:17 PM #;
 select # 11:17 AM#;
 select # 11:17 AM #;
 select # 1:17 PM#;
 select # 1:17 PM #;
 select #o1:17#;
 select #1:17a#;
 select #1:17p#;
 select #1:17 p#;
 select #1:17 pr#;
 -- ERROR: Time Constants: invalid hour
 select #24:17:59#;
 select #28:17:59#;
 select #13:17:59 AM#;
 select #23:17:59 PM#;
 select #13:17 AM#;
 select #23:17 PM#;
 
 -- Date/Time Constants (KDbSQL EXTENSION)
 -- https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants
 -- TODO
 
 -- TRUE and FALSE constants (KDbSQL EXTENSION)
 select TRUE;
 select TRUE, NOT FALSE;
 select NOT NOT TRUE;
 select NOT NULL;
 select NOT (NOT TRUE);
 select TRUE != FALSE;
 select TRUE <> FALSE;
 select TRUE OR FALSE;
 select TRUE XOR FALSE;
 select TRUE AND NOT FALSE;
 select TRUE < FALSE;
 select TRUE IS NULL;
 select TRUE <> TRUE OR FALSE == TRUE;
 -- ERROR: 'IS' is reserved keyword
 select TRUE IS NOT FALSE;
 -- IS NULL/IS NOT NULL
 select 1 IS NOT NULL;
 select NULL IS NULL;
 select NULL IS NOT NULL;
 -- ERROR: Extra ';'
 select 1; 2;
 -- Unary operators
 select -1;
 select --1;
 select +1;
 select ++1;
 select +--+-1;
 select -NULL;
 select +NULL;
 -- Binary operators
 select 1+2;
 select 1-2;
 select 1*2;
 select 1/2;
 select 1&2;
 select 1|2;
 select 1%2;
 select 2 >> 4;
 select 2 << 4;
 select 2 <> 4;
 select 2 != -4;
 select 2 = -4;
 select 2 == -4;
 select 2 > 4;
 select 2 < 4;
 select 2 <= 4;
 select 2 >= -4;
 select NULL + NULL;
 select 7 + NULL;
 select NULL - NULL;
 select 7 - NULL;
 select NULL * NULL;
 select 7 * NULL;
 select NULL / NULL;
 select 7 / NULL;
 select NULL & NULL;
 select 7 & NULL;
 select NULL | NULL;
 select 7 | NULL;
 select NULL % NULL;
 select 7 % NULL;
 select NULL >> NULL;
 select 7 >> NULL;
 select NULL << NULL;
 select 7 << NULL;
 select NULL <> NULL;
 select 7 <> NULL;
 select NULL != NULL;
 select 7 != NULL;
 select NULL == NULL;
 select 7 == NULL;
 select NULL > NULL;
 select 7 > NULL;
 select NULL < NULL;
 select 7 < NULL;
 select NULL >= NULL;
 select 7 >= NULL;
 select NULL <= NULL;
 select 7 <= NULL;
 select 'ABC' LIKE 'A%';
 select 'ABC' NOT LIKE 'A%';
 select 'ABC' LIKE NULL;
 select NULL LIKE NULL;
 select NULL NOT LIKE NULL;
 select 1 BETWEEN 0 AND 5;
 select NULL BETWEEN 1 AND 5;
 select 1 BETWEEN NULL AND 5;
 select 1 BETWEEN 0 AND NULL;
 select NULL BETWEEN NULL AND NULL;
 select 3 SIMILAR TO 4;
 select NULL SIMILAR TO 4;
 select 3 NOT SIMILAR TO 4;
 select NULL SIMILAR TO NULL;
 select 'AB' || 'CD';
 select "AB" ||"CD";
 select 'AB'||"CD"a;
 select 'AB' || 'CD' || 'EF';
 select 'AB' + 'CD' || 'EF' + 'GH' || '';
 select NULL || NULL;
 select NULL || 'AB';
 select 'AB' + NULL;
 -- ERROR: ||
 select 'AB' || 1;
 select 'AB' + 1;
 select 7 || 'AB';
 select 7 + 'AB';
 select 7 || 1;
 -- ERROR: Type error near "1"
 select 'ABC' NOT LIKE 1;
 select 'ABC' NOT LIKE;
 select 3 SIMILAR TO;
 select 1 BETWEEN 'a' AND 5;
 -- ERROR: **
 select 1**2;
 -- ERROR: * or / are not unary operators
 select *2;
 select /2;
 -- ERROR: operators after argument
 select 2+;
 select 2-;
 select 2*;
 select 2/;
 -- ERROR: unfinished '
 select ';
 select ";
 select \';
 select \"a";
 select 1';
 
 ---------- CATEGORY: Aliases in select statement --------------
 -- Aliases for columns
 select id myid from persons;
 select id as myid from persons;
 -- Aliases for tables
 select id from persons p;
 -- ERROR: There's no "persons" table in this query (alias "p" covers it)
 select persons.id from persons p;
 -- Alias "p" for table "persons" is used
 select p.id from persons p;
 -- Multiple aliases for the same table
 select persons.id from persons, persons p, persons p2;
 -- ERROR: Column "id" is defined in both tables (so "id" column is ambiguous)
 select id from persons p, cars p;
 select id from persons p, cars c;
 -- ERROR: Table alias "p" is assigned twice
 select p.id from persons p, cars p;
 select 1 from persons p, cars p;
 select p.model from persons p, cars p;
 -- ERROR: Table alias "p" is assigned twice and both have "id" column referenced by "p.*" so "p.*" is ambiguous
 select p.* from persons p, cars p;
 -- ERROR: Table alias "persons" is assigned and "persons" table is also used; both have "id" column referenced by "persons.*" so "persons.*" is ambiguous
 select persons.* from persons, cars persons;
 -- ERROR: Table alias "p" is assigned twice and both have "id" column referenced by "*" so "*" is ambiguous
 select * from persons p, cars p;
 -- ERROR: Column alias not allowed for all-tables asterisk
 select * as c from cars;
 -- ERROR: Column alias not allowed for table asterisk
 select cars.* as c from cars;
 
 ---------- CATEGORY: Expressions with infix operators in columns of select statement --------------
 -- Complex expressions support, operators precedence, and brackets
 select NULL IS NOT NULL from cars;
 select 2+3*4 from cars;
 select (2+3)*4 from cars;
 -- Support for aliases for complex-expression columns
 select (2+3)*4 from cars;
 -- ERROR: Column names are invalidated inside a complex expressions
 select one*two from persons;
 -- ERROR: Like "select p.id from persons p, cars p" but ambiguous column is inside a complex expression
 select id*2 from persons p, cars p;
 -- Operators precedence: arithmetic before relational
 select 1 + 2 < 3;
 -- *,/ before +,-
 select 1+2*3;
 -- Unary expressions before binary expressions
 select 1+-2;
 -- ERROR: column not found in expression
 select 2 * foo;
 -- ERROR: 'NOT' operator cannot be applied to integer
 select NOT 2 * 4;
 -- Nested '()'
 select ((2));
 select ((  (2 ) ));
 -- ERROR: unmatched '('
 select (2;
 select 2);
 select ((2);
 select (2)));
 -- column names in expression
 select id * id - 2 / id from cars;
 select model || "---" || model from cars;
 
 ---------- CATEGORY: Expressions with scalar functions --------------
 -- ABS
 SELECT ABS(-27), abs(-3.1415), ABS(NULL + 1);
 -- ERROR: ABS
 SELECT ABS(); -- 1 arg expected
 SELECT ABS(1, 2); -- 1 arg expected
 SELECT ABS("a"); -- type error
 -- CEILING
 SELECT CEILING(3.14), ceiling(-99.001);
 SELECT CEILING(0 + 0);
 SELECT CEILING(NULL);
 -- ERROR: CEILING
 SELECT CEILING(); -- 1 arg expected
 SELECT CEILING(1, 2); -- 1 arg expected
 SELECT CEILING("a"); -- type error
 -- CHAR
 SELECT CHAR(75,69,88,73), CHAR();
 SELECT CHAR(NULL);
 SELECT CHAR(NULL, NULL);
 -- ERROR: CHAR
 SELECT CHAR(''); -- type error
 SELECT CHAR(1.23); -- type error
 -- COALESCE
 SELECT COALESCE(NULL, 17, NULL, "A");
 SELECT COALESCE(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1); -- 100 args == KDB_MAX_FUNCTION_ARGS
 -- ERROR: COALESCE
 SELECT COALESCE(); -- 2 or more args expected
 SELECT COALESCE(1); -- 2 or more args expected
 SELECT COALESCE(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1); -- 101 args is more than KDB_MAX_FUNCTION_ARGS
 -- FLOOR
 SELECT FLOOR(3.14), FLOOR(-99.001);
 SELECT FLOOR(0 + 0);
 SELECT FLOOR(NULL);
 -- ERROR: FLOOR
 SELECT FLOOR(); -- 1 arg expected
 SELECT FLOOR(1, 2); -- 1 arg expected
 SELECT FLOOR("a"); -- type error
 -- TODO: more functions...
 -- ERROR: unknown scalar function
 SELECT foobar();
 
 ---------- CATEGORY: "WHERE" section --------------
 -- ERROR: Empty WHERE section
 select id from cars where;
 -- WHERE section without columns
 select from cars where TRUE;
 -- Complex expressions in WHERE section
 select id from cars where (id > 2 OR cars.owner IS NULL) AND 2 * id < 5;
 
 ---------- CATEGORY: "ORDER BY" section of select statement --------------
 -- Simple ORDER BY
 select id from cars order by id;
 -- Simple ORDER BY with DESC
 select id from cars order by id DESC;
 -- Simple ORDER BY with ASC
 select id from cars order by id ASC;
 -- Simple ORDER BY with ASC and DESC
 select id, model from cars order by id ASC, model DESC;
 -- Simple ORDER BY with WHERE
 select id from cars order by id WHERE id < 5;
 -- Simple ORDER BY with WHERE; opposite direction
 select id from cars WHERE id < 5 order by id;
 -- Simple ORDER BY, sorting field 'owner' is not in the list of displayed fields
 select id from cars order by owner;
 -- ORDER BY with many arguments
 select id from cars order by owner, model, id;
 -- ORDER BY where column numbers are used instead of names
 select id, model from cars order by 2, 1;
 -- ERROR: ORDER BY column number 2 out of range - should be between 1 and 1
 -- (there's only one visible field)
 select id from cars order by 2, 1;
 
 ---------- CATEGORY: JOINs -------
 -- Join persons and cars tables
 SELECT persons.name, persons.surname, persons.age, cars.model FROM persons, cars WHERE persons.id = cars.owner;
 -- Join persons and cars tables, skip table names where possible
 SELECT name, surname, age, model FROM persons, cars WHERE persons.id = owner;
 -- Join persons and cars tables, sort by surname+name
 SELECT persons.name, persons.surname, persons.age, cars.model FROM persons, cars WHERE persons.id = cars.owner ORDER BY 2, 1;
 -- Join persons and cars tables, sort using aliases
 SELECT persons.name as p_name, persons.surname, persons.age as p_age, cars.model FROM persons, cars WHERE persons.id = cars.owner ORDER BY p_age, p_name;
 
 ---------- CATEGORY: Parameters -------
 -- Parameters in column expressions
 SELECT [p];
 SELECT [p q];
 SELECT [ą]; -- non-latin1 character
 SELECT ["];
 SELECT ['];
 SELECT [`];
 SELECT [\];
 SELECT [=];
 SELECT [*];
 SELECT [;];
 SELECT [,];
 SELECT [(];
 SELECT [)];
 SELECT [%];
 SELECT [p] + [q];
 SELECT [p] + [p];
 SELECT [p]||[q];
 SELECT [p], [q];
 SELECT [p], [p]; -- two the same parameters
 SELECT [SELECT]; -- keywords as parameter names allowed
 SELECT [p]a; -- [p] AS a
 SELECT[p]; -- no whitespace needed
 SELECT TRIM(name, [c]) FROM persons;
 SELECT [c] FROM persons;
 -- ERROR: unexpected character after parameter in column expression
 SELECT [p]a/2;
 SELECT [p][q];
 SELECT [p] [q];
 -- ERROR: unexpected character in parameter name
 SELECT [[];
 SELECT [\[];
 -- ERROR: empty parameter name
 SELECT [];
 -- ERROR: parameter in alias name
 SELECT [p] AS [q];
 -- ERROR: parameter in ORDER BY argument
 SELECT * FROM cars ORDER BY [x];
 -- Parameters in conditions
 SELECT name, surname, age, model FROM persons, cars WHERE persons.id = owner AND model = [Enter model];
 SELECT [Enter], name, surname, age, model FROM persons, cars WHERE persons.id = [Enter ID] AND [Enter model] = model;
 SELECT * FROM cars WHERE [Boolean condition];
 SELECT * FROM cars WHERE [a] < [b];
 SELECT FROM cars WHERE [a] < [b];
 SELECT LTRIM(name, [c]) FROM persons, cars WHERE persons.id = owner AND name = [Enter model];
 -- ERROR: unexpected character in parameter name in condition
 SELECT * FROM cars WHERE model = [Boolean condition]a;
 -- ERROR: empty parameter name in condition
 SELECT * FROM cars WHERE model = [];