diff options
Diffstat (limited to 'test/merge1.test')
| -rw-r--r-- | test/merge1.test | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/test/merge1.test b/test/merge1.test new file mode 100644 index 0000000..7ec4dab --- /dev/null +++ b/test/merge1.test @@ -0,0 +1,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 |