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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
# 2021-12-29
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Testing the compound-SELECT merge algorithm to ensure that it works
# when it tries to balance the merge tree.
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix merge1
ifcapable !vtab {
finish_test
return
}
load_static_extension db series
optimization_control db all on
do_execsql_test 100 {
WITH data(v) AS (
SELECT value FROM generate_series(1,35,3)
UNION ALL
SELECT value FROM generate_series(10,30,4)
UNION ALL
SELECT value FROM generate_series(20,50,5)
UNION ALL
SELECT value FROM generate_series(30,60,6)
UNION ALL
SELECT value FROM generate_series(1,50,7)
UNION ALL
SELECT value FROM generate_series(10,80,8)
)
SELECT v FROM data ORDER BY v;
} {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}
do_eqp_test 101 {
WITH data(v) AS (
SELECT value FROM generate_series(1,35,3)
UNION ALL
SELECT value FROM generate_series(10,30,4)
UNION ALL
SELECT value FROM generate_series(20,50,5)
UNION ALL
SELECT value FROM generate_series(30,60,6)
UNION ALL
SELECT value FROM generate_series(1,50,7)
UNION ALL
SELECT value FROM generate_series(10,80,8)
)
SELECT v FROM data ORDER BY v;
} {
QUERY PLAN
`--MERGE (UNION ALL)
|--LEFT
| `--MERGE (UNION ALL)
| |--LEFT
| | `--MERGE (UNION ALL)
| | |--LEFT
| | | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| | `--RIGHT
| | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| `--RIGHT
| `--SCAN generate_series VIRTUAL TABLE INDEX 23:
`--RIGHT
`--MERGE (UNION ALL)
|--LEFT
| `--MERGE (UNION ALL)
| |--LEFT
| | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| `--RIGHT
| `--SCAN generate_series VIRTUAL TABLE INDEX 23:
`--RIGHT
`--SCAN generate_series VIRTUAL TABLE INDEX 23:
}
# Same test with the blanced-merge optimization
# disabled. Should give the exact same answer.
#
optimization_control db balanced-merge off
db cache flush
do_execsql_test 110 {
WITH data(v) AS (
SELECT value FROM generate_series(1,35,3)
UNION ALL
SELECT value FROM generate_series(10,30,4)
UNION ALL
SELECT value FROM generate_series(20,50,5)
UNION ALL
SELECT value FROM generate_series(30,60,6)
UNION ALL
SELECT value FROM generate_series(1,50,7)
UNION ALL
SELECT value FROM generate_series(10,80,8)
)
SELECT v FROM data ORDER BY v;
} {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}
do_eqp_test 111 {
WITH data(v) AS (
SELECT value FROM generate_series(1,35,3)
UNION ALL
SELECT value FROM generate_series(10,30,4)
UNION ALL
SELECT value FROM generate_series(20,50,5)
UNION ALL
SELECT value FROM generate_series(30,60,6)
UNION ALL
SELECT value FROM generate_series(1,50,7)
UNION ALL
SELECT value FROM generate_series(10,80,8)
)
SELECT v FROM data ORDER BY v;
} {
QUERY PLAN
`--MERGE (UNION ALL)
|--LEFT
| `--MERGE (UNION ALL)
| |--LEFT
| | `--MERGE (UNION ALL)
| | |--LEFT
| | | `--MERGE (UNION ALL)
| | | |--LEFT
| | | | `--MERGE (UNION ALL)
| | | | |--LEFT
| | | | | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| | | | `--RIGHT
| | | | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| | | `--RIGHT
| | | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| | `--RIGHT
| | `--SCAN generate_series VIRTUAL TABLE INDEX 23:
| `--RIGHT
| `--SCAN generate_series VIRTUAL TABLE INDEX 23:
`--RIGHT
`--SCAN generate_series VIRTUAL TABLE INDEX 23:
}
finish_test
|
