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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix merge1 18 19ifcapable !vtab { 20 finish_test 21 return 22} 23 24load_static_extension db series 25 26 27optimization_control db all on 28do_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} 44do_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 23: 68 | | `--RIGHT 69 | | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 70 | `--RIGHT 71 | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 72 `--RIGHT 73 `--MERGE (UNION ALL) 74 |--LEFT 75 | `--MERGE (UNION ALL) 76 | |--LEFT 77 | | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 78 | `--RIGHT 79 | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 80 `--RIGHT 81 `--SCAN generate_series VIRTUAL TABLE INDEX 23: 82} 83 84# Same test with the blanced-merge optimization 85# disabled. Should give the exact same answer. 86# 87optimization_control db balanced-merge off 88db cache flush 89do_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} 105do_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 23: 133 | | | | `--RIGHT 134 | | | | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 135 | | | `--RIGHT 136 | | | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 137 | | `--RIGHT 138 | | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 139 | `--RIGHT 140 | `--SCAN generate_series VIRTUAL TABLE INDEX 23: 141 `--RIGHT 142 `--SCAN generate_series VIRTUAL TABLE INDEX 23: 143} 144 145finish_test 146