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
90
91
92
93
94
95
96
97
98
99
100
|
# name: test/sql/prepared/parameter_order_subquery.test
# description: Test the parameter order for prepared statements with sub-queries
# group: [prepared]
statement ok
CREATE TABLE t1(c0 INT2);
statement ok
INSERT INTO t1 VALUES (1), (2), (3), (5), (7), (10), (20);
statement ok
PREPARE prepare_query AS SELECT (? NOT IN (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?));
query I
EXECUTE prepare_query(-675986880, 1);
----
true
statement ok
PREPARE q1 AS SELECT (? NOT IN (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?)) AND
(? IN (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?));
query I
EXECUTE q1(10, 1, 20, 3);
----
false
statement ok
PREPARE q2 AS SELECT ? IN (
SELECT t1.c0
FROM t1
WHERE t1.c0 IN (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?)
AND t1.c0 > ?
ORDER BY t1.c0
LIMIT ?
);
query I
EXECUTE q2(7, 5, 3, 2);
----
true
statement ok
PREPARE q3 AS SELECT ? = ANY (
SELECT t1.c0
FROM t1
WHERE t1.c0 = ALL (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?)
ORDER BY t1.c0
LIMIT ?
);
query I
EXECUTE q3(1, 1, 2);
----
true
statement ok
PREPARE q4 AS SELECT (? NOT IN (
SELECT t1.c0
FROM t1
WHERE t1.c0 < ?
ORDER BY t1.c0
LIMIT ?
));
query I
EXECUTE q4(10, 5, 2);
----
true
statement ok
PREPARE q5 AS SELECT ? IN (
(SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?)
UNION ALL
(SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?)
);
query I
EXECUTE q5(2, 2, 4);
----
true
statement ok
PREPARE q7 AS SELECT ? = (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?);
query I
EXECUTE q7(1, 1);
----
true
statement ok
PREPARE q8 AS SELECT
(SELECT MAX(c0) FROM (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?) s1) AS a,
? AS b,
(SELECT MAX(c0) FROM (SELECT t1.c0 FROM t1 ORDER BY t1.c0 LIMIT ?) s2) AS c;
query III
EXECUTE q8(1, 10, 3);
----
1 10 3
|
