Skip to content

Commit 0db0bc3

Browse files
nsycansyca
committed
address @gatorsmile's comment #1 
1 parent 48ff3c7 commit 0db0bc3

File tree

2 files changed

+115
-124
lines changed

2 files changed

+115
-124
lines changed

‎sql/core/src/test/resources/sql-tests/inputs/subquery/scalar-subquery/scalar-subquery-select.sql‎

Lines changed: 50 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -1,49 +1,49 @@
11
-- A test suite for scalar subquery in SELECT clause
22

33
create temporary view t1 as select * from values
4-
("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
5-
("t1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
6-
("t1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
7-
("t1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
8-
("t1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
9-
("t1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
10-
("t1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
11-
("t1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
12-
("t1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
13-
("t1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
14-
("t1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
15-
("t1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
4+
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
5+
('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
6+
('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
7+
('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
8+
('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
9+
('val1d', null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
10+
('val1d', null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
11+
('val1e', 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
12+
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
13+
('val1d', 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
14+
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
15+
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
1616
as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
1717

1818
create temporary view t2 as select * from values
19-
("t2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
20-
("t1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
21-
("t1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
22-
("t1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
23-
("t1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
24-
("t2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
25-
("t1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
26-
("t1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
27-
("t1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
28-
("t1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
29-
("t1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
30-
("t1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
31-
("t1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
19+
('val2a', 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
20+
('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
21+
('val1b', 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
22+
('val1c', 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
23+
('val1b', null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
24+
('val2e', 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
25+
('val1f', 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
26+
('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
27+
('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
28+
('val1c', 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
29+
('val1e', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
30+
('val1f', 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
31+
('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
3232
as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
3333

3434
create temporary view t3 as select * from values
35-
("t3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
36-
("t3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
37-
("t1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
38-
("t1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
39-
("t1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
40-
("t1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
41-
("t3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
42-
("t3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
43-
("t1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
44-
("t1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
45-
("t3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
46-
("t3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
35+
('val3a', 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
36+
('val3a', 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
37+
('val1b', 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
38+
('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
39+
('val1b', 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
40+
('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
41+
('val3c', 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
42+
('val3c', 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
43+
('val1b', null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
44+
('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
45+
('val3b', 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
46+
('val3b', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
4747
as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
4848

4949
-- Group 1: scalar subquery in SELECT clause
@@ -53,8 +53,7 @@ create temporary view t3 as select * from values
5353
SELECT (SELECT min(t3d) FROM t3) min_t3d,
5454
(SELECT max(t2h) FROM t2) max_t2h
5555
FROM t1
56-
WHERE t1a = 't1c'
57-
;
56+
WHERE t1a = 'val1c';
5857

5958
-- TC 01.02
6059
-- scalar subquery in an IN subquery
@@ -64,63 +63,57 @@ WHERE t1c IN (SELECT (SELECT min(t3c) FROM t3)
6463
FROM t2
6564
GROUP BY t2g
6665
HAVING count(*) > 1)
67-
GROUP BY t1a
68-
;
66+
GROUP BY t1a;
6967

7068
-- TC 01.03
7169
-- under a set op
7270
SELECT (SELECT min(t3d) FROM t3) min_t3d,
7371
null
7472
FROM t1
75-
WHERE t1a = 't1c'
73+
WHERE t1a = 'val1c'
7674
UNION
7775
SELECT null,
7876
(SELECT max(t2h) FROM t2) max_t2h
7977
FROM t1
80-
WHERE t1a = 't1c'
81-
;
78+
WHERE t1a = 'val1c';
8279

8380
-- TC 01.04
8481
SELECT (SELECT min(t3c) FROM t3) min_t3d
8582
FROM t1
86-
WHERE t1a = 't1a'
83+
WHERE t1a = 'val1a'
8784
INTERSECT
8885
SELECT (SELECT min(t2c) FROM t2) min_t2d
8986
FROM t1
90-
WHERE t1a = 't1d'
91-
;
87+
WHERE t1a = 'val1d';
9288

9389
-- TC 01.05
9490
SELECT q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d
9591
FROM (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d
9692
FROM t1
97-
WHERE t1a IN ('t1e', 't1c')) q1
93+
WHERE t1a IN ('val1e', 'val1c')) q1
9894
FULL OUTER JOIN
9995
(SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d
10096
FROM t2
101-
WHERE t2a IN ('t1c', 't2a')) q2
97+
WHERE t2a IN ('val1c', 'val2a')) q2
10298
ON q1.t1a = q2.t2a
103-
AND q1.min_t3d < q2.avg_t3d
104-
;
99+
AND q1.min_t3d < q2.avg_t3d;
105100

106101
-- Group 2: scalar subquery in SELECT clause
107102
-- with correlation
108103
-- TC 02.01
109104
SELECT (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d,
110105
(SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h
111106
FROM t1
112-
WHERE t1a = 't1b'
113-
;
107+
WHERE t1a = 'val1b';
114108

115109
-- TC 02.02
116110
SELECT (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d
117111
FROM t1
118-
WHERE t1a = 't1b'
112+
WHERE t1a = 'val1b'
119113
MINUS
120114
SELECT (SELECT min(t3d) FROM t3) abs_min_t3d
121115
FROM t1
122-
WHERE t1a = 't1b'
123-
;
116+
WHERE t1a = 'val1b';
124117

125118
-- TC 02.03
126119
SELECT t1a, t1b
@@ -134,6 +127,4 @@ WHERE NOT EXISTS (SELECT (SELECT max(t2b)
134127
FROM t2 LEFT JOIN t1
135128
ON t2a = t1a
136129
WHERE t2c = t3c)
137-
AND t3a = t1a)
138-
139-
;
130+
AND t3a = t1a);

0 commit comments

Comments
 (0)