1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
---------- TOPIC000: GENERAL ISSUES --------------
-- OK000: the same field used in two columns
select id, id from persons;
-- OK001: whitespace between table-identifier, dot field-identifier/asterisk
select persons . id from persons;
select persons . * from persons;
-- OK002: multiple asterisks
select *, * from persons;
-- ER003: identifier cannot start with a number
select 1id from persons;
-- ER004: asterisk not allowed: no tables specified
select *;
-- OK005: empty tables set
select 1, 2;
-- OK006: empty column set (KEXISQL EXTENSION!)
select from cars;
-- OK007: totally empty statement (KEXISQL EXTENSION!)
select;
---------- TOPIC100: ALIASES IN SELECT STATEMENT --------------
-- OK100: aliases for columns
select id myid from persons;
-- OK101: aliases for tables
select id from persons p;
-- ER102: there's no "persons" table in this query (alias "p" covers it)
select persons.id from persons p;
-- OK103: alias "p" for table "persons" is used
select p.id from persons p;
-- OK104: multiple aliases for the same table
select persons.id from persons, persons p, persons p2
-- ER105: column "id" is defined in both tables (so "id" column is ambiguous)
select id from persons p, cars p;
-- ER106: alias "p" is used twice for tables and both have "id" column (so "p" column is ambiguous)
select p.id from persons p, cars p;
select p.* from persons p, cars p;
select persons.* from persons, cars persons;
-- ER107: alias not allowed for asterisk
select * as c from cars;
select cars.* as c from cars;
---------- TOPIC200: EXPRESSIONS IN COLUMNS OF SELECT STATEMENT --------------
-- OK200: like ER106, but it's ok, because we're not using fields from "p" tables
select 1 from persons p, cars p;
-- OK201: 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;
-- OK202: support for aliases for complex-expression columns
select (2+3)*4 from cars;
-- ER203: column names are invalidated inside a complex expressions
select one*two from persons;
-- ER204: like ER106, but ambiguous column is inside a complex expression
select id*2 from persons p, cars p;
---------- TOPIC300: EXPRESSIONS IN 'WHERE' SECTION OF SELECT STATEMENT --------------
-- OK300: complex expressions in WHERE section
select id from cars where (id > 2 OR cars.owner IS NULL) AND NOT 2 * id < 5;
---------- TOPIC400: 'ORDER BY' SECTION OF SELECT STATEMENT --------------
-- OK400: simple ORDER BY
select id from cars order by id;
-- OK401: simple ORDER BY with DESC
select id from cars order by id DESC;
-- OK402: simple ORDER BY with ASC
select id from cars order by id ASC;
-- OK403: simple ORDER BY with WHERE
select id from cars order by id WHERE id < 5;
-- OK404: simple ORDER BY with WHERE; opposite direction
select id from cars WHERE id < 5 order by id;
-- OK405: simple ORDER BY, sorting field 'owner' is not in the list of displayed fields
select id from cars order by owner;
-- OK406: ORDER BY with many arguments
select id from cars order by owner, model, id;
-- OK407: ORDER BY where column numbers are used instead of names
select id, model from cars order by 2, 1;
-- ER408: 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;
---------- TOPIC500: EXPRESSIONS --------------
-- OK500: operators precedence: arithmetic before relational
select 1 + 2 < 3;
-- OK501: *,/ before +,-
select 1+2*3;
-- OK501: unary expressions before binary expressions
select 1+-2;
-- TODO
-'--' comments
|
