-- 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 = [];