AryaWu/sqlite

1# 2021-12-292#3# The author disclaims copyright to this source code.  In place of4# 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 works13# when it tries to balance the merge tree.14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix merge118 19ifcapable !vtab {20  finish_test21  return22}23 24load_static_extension db series25 26 27optimization_control db all on28do_execsql_test 100 {29  WITH data(v) AS (30    SELECT value FROM generate_series(1,35,3)31    UNION ALL32    SELECT value FROM generate_series(10,30,4)33    UNION ALL34    SELECT value FROM generate_series(20,50,5)35    UNION ALL36    SELECT value FROM generate_series(30,60,6)37    UNION ALL38    SELECT value FROM generate_series(1,50,7)39    UNION ALL40    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 ALL48    SELECT value FROM generate_series(10,30,4)49    UNION ALL50    SELECT value FROM generate_series(20,50,5)51    UNION ALL52    SELECT value FROM generate_series(30,60,6)53    UNION ALL54    SELECT value FROM generate_series(1,50,7)55    UNION ALL56    SELECT value FROM generate_series(10,80,8)57  )58  SELECT v FROM data ORDER BY v;59} {60  QUERY PLAN61  `--MERGE (UNION ALL)62     |--LEFT63     |  `--MERGE (UNION ALL)64     |     |--LEFT65     |     |  `--MERGE (UNION ALL)66     |     |     |--LEFT67     |     |     |  `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:68     |     |     `--RIGHT69     |     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:70     |     `--RIGHT71     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:72     `--RIGHT73        `--MERGE (UNION ALL)74           |--LEFT75           |  `--MERGE (UNION ALL)76           |     |--LEFT77           |     |  `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:78           |     `--RIGHT79           |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:80           `--RIGHT81              `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:82}83 84# Same test with the blanced-merge optimization85# disabled.  Should give the exact same answer.86#87optimization_control db balanced-merge off88db cache flush89do_execsql_test 110 {90  WITH data(v) AS (91    SELECT value FROM generate_series(1,35,3)92    UNION ALL93    SELECT value FROM generate_series(10,30,4)94    UNION ALL95    SELECT value FROM generate_series(20,50,5)96    UNION ALL97    SELECT value FROM generate_series(30,60,6)98    UNION ALL99    SELECT value FROM generate_series(1,50,7)100    UNION ALL101    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 ALL109    SELECT value FROM generate_series(10,30,4)110    UNION ALL111    SELECT value FROM generate_series(20,50,5)112    UNION ALL113    SELECT value FROM generate_series(30,60,6)114    UNION ALL115    SELECT value FROM generate_series(1,50,7)116    UNION ALL117    SELECT value FROM generate_series(10,80,8)118  )119  SELECT v FROM data ORDER BY v;120} {121  QUERY PLAN122  `--MERGE (UNION ALL)123     |--LEFT124     |  `--MERGE (UNION ALL)125     |     |--LEFT126     |     |  `--MERGE (UNION ALL)127     |     |     |--LEFT128     |     |     |  `--MERGE (UNION ALL)129     |     |     |     |--LEFT130     |     |     |     |  `--MERGE (UNION ALL)131     |     |     |     |     |--LEFT132     |     |     |     |     |  `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:133     |     |     |     |     `--RIGHT134     |     |     |     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:135     |     |     |     `--RIGHT136     |     |     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:137     |     |     `--RIGHT138     |     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:139     |     `--RIGHT140     |        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:141     `--RIGHT142        `--SCAN generate_series VIRTUAL TABLE INDEX 0x17:143}144 145finish_test146