8 let named s
= (Some s
,(0,0))
9 let param = (None
,(0,0))
11 let cmp_params p1 p2
=
13 List.for_all2
(fun ((name1
,pos1
),t1
) ((name2
,pos2
),t2
) ->
14 name1
= name2
&& t1
= t2
&& pos1
= (0,0) && snd pos2
> fst pos2
)
19 let tt ?msg stmt schema params
=
20 let print_schema = RA.Schema.to_string
in
21 let print_params = Stmt.params_to_string
in
24 Parser.parse_stmt stmt
26 | _
-> assert_failure
"tt failed"
28 assert_equal ?msg ~printer
:print_schema schema s1
;
29 assert_equal ?msg ~cmp
:cmp_params ~printer
:print_params params p1
32 tt "CREATE TABLE test (id INT, str TEXT, name TEXT)"
35 tt "SELECT str FROM test WHERE id=?"
38 tt "SELECT x,y+? AS z FROM (SELECT id AS y,CONCAT(str,name) AS x FROM test WHERE id=@id*2) ORDER BY x,x+z LIMIT @lim"
39 [attr
"x" Text
; attr
"z" Int
]
40 [param,Some Int
; named "id", Some Int
; named "lim",Some Int
; ];
41 tt "select test.name,other.name as other_name from test, test as other where test.id=other.id + @delta"
42 [attr
"name" Text
; attr
"other_name" Text
]
43 [named "delta", Some Int
];
44 tt "select test.name from test where test.id + @x = ? or test.id - @x = ?"
46 [named "x", Some Int
; param, Some Int
; named "x", Some Int
; param, Some Int
;];
50 see MySQL 5.4 refman -- 12.2.8.1. JOIN Syntax
51 see SQL:2008 -- 7.7 <joined table>
53 let test_join_result_cols () =
55 let ints = List.map
(fun name
-> attr name Int
) in
56 tt "CREATE TABLE t1 (i INT, j INT)" [] [];
57 tt "CREATE TABLE t2 (k INT, j INT)" [] [];
58 tt "SELECT * FROM t1 JOIN t2 ON i=t1.j" (ints ["i";"j";"k";"j"]) [];
59 tt ~msg
:"NATURAL JOIN"
60 "SELECT * FROM t1 NATURAL JOIN t2" (ints ["j";"i";"k"]) [];
62 "SELECT * FROM t1 JOIN t2 USING (j)" (ints ["j";"i";"k"]) [];
63 tt ~msg
:"NATURAL JOIN with common column in WHERE"
64 "SELECT * FROM t1 NATURAL JOIN t2 WHERE j > @x"
67 tt ~msg
:"NATURAL JOIN with common column qualified in WHERE"
68 "SELECT * FROM t1 NATURAL JOIN t2 WHERE t2.j > @x"
75 let printer = Show.show
<int list
> in
76 fun x y z
-> assert_equal ~
printer (RA.Schema.natural_ x y
) z
78 test [1;2;3;4] [1;2;5;6] [1;2;3;4;5;6];
79 test [1;2;3;4] [4;3;2;1] [1;2;3;4];
80 test [1;2;3;4] [5;4;3;7;5;7] [3;4;1;2;5;7;5;7];
81 test [1;2;3;4] [5;2;2] [2;1;3;4;5]; (* ?! *)
88 "JOIN result columns" >:: test_join_result_cols;
92 let test_suite = "main" >::: tests in
93 ignore
(run_test_tt
test_suite)