test/selectH.test

# 2023-02-16
#
# 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.
#
#***********************************************************************
#
# Test cases for the omit-unused-subquery-column optimization.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix selectH

do_execsql_test 1.1 {
  CREATE TABLE t1(
     c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
     c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
     c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
     c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
     c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
     c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
     c60, c61, c62, c63, c64, c65
  );
  INSERT INTO t1 VALUES(
     0,  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
  );
  CREATE INDEX t1c60 ON t1(c60);
}

# The SQL counter(N) function adjusts the value of the global
# TCL variable ::selectH_cnt by the value N and returns the new
# value.  By putting calls to counter(N) as unused columns in a
# view or subquery, we can check to see if the counter gets incremented,
# and if not that means that the unused column was omitted.
#
unset -nocomplain selectH_cnt
set selectH_cnt 0
proc selectH_counter {amt} {
  global selectH_cnt
  incr selectH_cnt $amt
  return $selectH_cnt
}
db func counter selectH_counter

do_execsql_test 1.2 {
  SELECT DISTINCT c44 FROM (
    SELECT c0 AS a, *, counter(1) FROM t1
    UNION ALL
    SELECT c1 AS a, *, counter(1) FROM t1
  ) WHERE c60=60;
} {44}
do_test 1.3 {
  set ::selectH_cnt
} {0}

do_execsql_test 2.1 {
  SELECT a FROM (
    SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1
    UNION ALL
    SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1
    ORDER BY b
  );
} {16 15}
do_test 2.2 {
  set ::selectH_cnt
} {0}

do_execsql_test 3.1 {
  CREATE VIEW v1 AS
    SELECT c16 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c17 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c18 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c19 AS a, *, counter(1) AS x FROM t1;
  SELECT count(*) FROM v1 WHERE c60=60;
} {4}
do_test 3.2 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.3 {
  SELECT count(a) FROM v1 WHERE c60=60;
} {4}
do_execsql_test 3.4 {
  SELECT a FROM v1 WHERE c60=60;
} {16 17 18 19}
do_test 3.5 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.6 {
  SELECT x FROM v1 WHERE c60=60;
} {1 2 3 4}
do_test 3.7 {
  set ::selectH_cnt
} {4}

# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15
#
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema
                 UNION ALL SELECT a FROM t1);
} {1 1}

do_execsql_test 4.2 {
  SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema 
    UNION ALL 
  SELECT a FROM t1
} {v1 t1}

#-------------------------------------------------------------------------
# forum post https://sqlite.org/forum/forumpost/b83c7b2168
#
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1 (val1);
  INSERT INTO t1 VALUES(4);
  INSERT INTO t1 VALUES(5);
  CREATE TABLE t2 (val2);
}
do_execsql_test 5.1 {
  SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2;
} {
  4 5
}
do_execsql_test 5.2 {
  SELECT count(1234) FROM (
    SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2
  )
} {2}

finish_test