-
Notifications
You must be signed in to change notification settings - Fork 21
/
Copy pathsubquery.sql
88 lines (73 loc) · 2.8 KB
/
subquery.sql
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
/* setup */
CREATE TABLE PUBLIC.t1 (x INT, y VARCHAR(16));
INSERT INTO t1 (x, y) VALUES (123, 'hello');
INSERT INTO t1 (x, y) VALUES (456, 'world');
EXPLAIN SELECT * FROM (SELECT * FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.X INTEGER, ":memory:".PUBLIC.T1.Y CHARACTER VARYING(16))
-- EXPLAIN: TABLE $1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR ($1.X INTEGER, $1.Y CHARACTER VARYING(16))
EXPLAIN SELECT * FROM (SELECT x FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (X INTEGER)
-- EXPLAIN: TABLE $1 (X INTEGER)
-- EXPLAIN: EXPR ($1.X INTEGER)
EXPLAIN SELECT x FROM (SELECT x FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (X INTEGER)
-- EXPLAIN: TABLE $1 (X INTEGER)
-- EXPLAIN: EXPR (X INTEGER)
SELECT x FROM (SELECT x FROM t1);
-- X: 123
-- X: 456
SELECT * FROM (SELECT x FROM t1);
-- X: 123
-- X: 456
EXPLAIN SELECT x, y FROM (SELECT x, y FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: TABLE $1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (X INTEGER, Y CHARACTER VARYING(16))
SELECT x, y FROM (SELECT x, y FROM t1);
-- X: 123 Y: hello
-- X: 456 Y: world
EXPLAIN SELECT y FROM (SELECT x, y FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: TABLE $1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (Y CHARACTER VARYING(16))
SELECT y FROM (SELECT x, y FROM t1);
-- Y: hello
-- Y: world
EXPLAIN SELECT * FROM (SELECT x + char_length(y) FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (COL1 INTEGER)
-- EXPLAIN: TABLE $1 (COL1 INTEGER)
-- EXPLAIN: EXPR ($1.COL1 INTEGER)
SELECT * FROM (SELECT x + char_length(y) FROM t1);
-- COL1: 128
-- COL1: 461
EXPLAIN SELECT * FROM (SELECT x + char_length(y) AS foo FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (FOO INTEGER)
-- EXPLAIN: TABLE $1 (FOO INTEGER)
-- EXPLAIN: EXPR ($1.FOO INTEGER)
SELECT * FROM (SELECT x + char_length(y) AS foo FROM t1);
-- FOO: 128
-- FOO: 461
EXPLAIN SELECT foo AS bar FROM (SELECT x + char_length(y) AS foo FROM t1);
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (X INTEGER, Y CHARACTER VARYING(16))
-- EXPLAIN: EXPR (FOO INTEGER)
-- EXPLAIN: TABLE $1 (FOO INTEGER)
-- EXPLAIN: EXPR (BAR INTEGER)
SELECT foo AS bar FROM (SELECT x + char_length(y) AS foo FROM t1);
-- BAR: 128
-- BAR: 461