test/merge1.test

# 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:
}