Debian Codegraph @

test/merge1.test

raw
1 # 2021-12-29
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12 # Testing the compound-SELECT merge algorithm to ensure that it works
13 # when it tries to balance the merge tree.
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix merge1
18
19 ifcapable !vtab {
20 finish_test
21 return
22 }
23
24 load_static_extension db series
25
26
27 optimization_control db all on
28 do_execsql_test 100 {
29 WITH data(v) AS (
30 SELECT value FROM generate_series(1,35,3)
31 UNION ALL
32 SELECT value FROM generate_series(10,30,4)
33 UNION ALL
34 SELECT value FROM generate_series(20,50,5)
35 UNION ALL
36 SELECT value FROM generate_series(30,60,6)
37 UNION ALL
38 SELECT value FROM generate_series(1,50,7)
39 UNION ALL
40 SELECT value FROM generate_series(10,80,8)
41 )
42 SELECT v FROM data ORDER BY v;
43 } {1 1 4 7 8 10 10 10 13 14 15 16 18 18 19 20 22 22 22 25 25 26 26 28 29 30 30 30 31 34 34 35 36 36 40 42 42 43 45 48 50 50 50 54 58 60 66 74}
44 do_eqp_test 101 {
45 WITH data(v) AS (
46 SELECT value FROM generate_series(1,35,3)
47 UNION ALL
48 SELECT value FROM generate_series(10,30,4)
49 UNION ALL
50 SELECT value FROM generate_series(20,50,5)
51 UNION ALL
52 SELECT value FROM generate_series(30,60,6)
53 UNION ALL
54 SELECT value FROM generate_series(1,50,7)
55 UNION ALL
56 SELECT value FROM generate_series(10,80,8)
57 )
58 SELECT v FROM data ORDER BY v;
59 } {
60 QUERY PLAN
61 `--MERGE (UNION ALL)
62 |--LEFT
63 | `--MERGE (UNION ALL)
64 | |--LEFT
65 | | `--MERGE (UNION ALL)
66 | | |--LEFT
67 | | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
68 | | `--RIGHT
69 | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
70 | `--RIGHT
71 | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
72 `--RIGHT
73 `--MERGE (UNION ALL)
74 |--LEFT
75 | `--MERGE (UNION ALL)
76 | |--LEFT
77 | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
78 | `--RIGHT
79 | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
80 `--RIGHT
81 `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
82 }
83
84 # Same test with the blanced-merge optimization
85 # disabled. Should give the exact same answer.
86 #
87 optimization_control db balanced-merge off
88 db cache flush
89 do_execsql_test 110 {
90 WITH data(v) AS (
91 SELECT value FROM generate_series(1,35,3)
92 UNION ALL
93 SELECT value FROM generate_series(10,30,4)
94 UNION ALL
95 SELECT value FROM generate_series(20,50,5)
96 UNION ALL
97 SELECT value FROM generate_series(30,60,6)
98 UNION ALL
99 SELECT value FROM generate_series(1,50,7)
100 UNION ALL
101 SELECT value FROM generate_series(10,80,8)
102 )
103 SELECT v FROM data ORDER BY v;
104 } {1 1 4 7 8 10 10 10 13 14 15 16 18 18 19 20 22 22 22 25 25 26 26 28 29 30 30 30 31 34 34 35 36 36 40 42 42 43 45 48 50 50 50 54 58 60 66 74}
105 do_eqp_test 111 {
106 WITH data(v) AS (
107 SELECT value FROM generate_series(1,35,3)
108 UNION ALL
109 SELECT value FROM generate_series(10,30,4)
110 UNION ALL
111 SELECT value FROM generate_series(20,50,5)
112 UNION ALL
113 SELECT value FROM generate_series(30,60,6)
114 UNION ALL
115 SELECT value FROM generate_series(1,50,7)
116 UNION ALL
117 SELECT value FROM generate_series(10,80,8)
118 )
119 SELECT v FROM data ORDER BY v;
120 } {
121 QUERY PLAN
122 `--MERGE (UNION ALL)
123 |--LEFT
124 | `--MERGE (UNION ALL)
125 | |--LEFT
126 | | `--MERGE (UNION ALL)
127 | | |--LEFT
128 | | | `--MERGE (UNION ALL)
129 | | | |--LEFT
130 | | | | `--MERGE (UNION ALL)
131 | | | | |--LEFT
132 | | | | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
133 | | | | `--RIGHT
134 | | | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
135 | | | `--RIGHT
136 | | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
137 | | `--RIGHT
138 | | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
139 | `--RIGHT
140 | `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
141 `--RIGHT
142 `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:
143 }
144
145 finish_test