AryaWu/sqlite

1# 2023-02-162#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# Test cases for the omit-unused-subquery-column optimization.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix selectH18 19do_execsql_test 1.1 {20  CREATE TABLE t1(21     c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,22     c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,23     c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,24     c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,25     c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,26     c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,27     c60, c61, c62, c63, c64, c6528  );29  INSERT INTO t1 VALUES(30     0,  1,  2,  3,  4,  5,  6,  7,  8,  9,31     10, 11, 12, 13, 14, 15, 16, 17, 18, 19,32     20, 21, 22, 23, 24, 25, 26, 27, 28, 29,33     30, 31, 32, 33, 34, 35, 36, 37, 38, 39,34     40, 41, 42, 43, 44, 45, 46, 47, 48, 49,35     50, 51, 52, 53, 54, 55, 56, 57, 58, 59,36     60, 61, 62, 63, 64, 6537  );38  CREATE INDEX t1c60 ON t1(c60);39}40 41# The SQL counter(N) function adjusts the value of the global42# TCL variable ::selectH_cnt by the value N and returns the new43# value.  By putting calls to counter(N) as unused columns in a44# view or subquery, we can check to see if the counter gets incremented,45# and if not that means that the unused column was omitted.46#47unset -nocomplain selectH_cnt48set selectH_cnt 049proc selectH_counter {amt} {50  global selectH_cnt51  incr selectH_cnt $amt52  return $selectH_cnt53}54db func counter selectH_counter55 56do_execsql_test 1.2 {57  SELECT DISTINCT c44 FROM (58    SELECT c0 AS a, *, counter(1) FROM t159    UNION ALL60    SELECT c1 AS a, *, counter(1) FROM t161  ) WHERE c60=60;62} {44}63do_test 1.3 {64  set ::selectH_cnt65} {0}66 67do_execsql_test 2.1 {68  SELECT a FROM (69    SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t170    UNION ALL71    SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t172    ORDER BY b73  );74} {16 15}75do_test 2.2 {76  set ::selectH_cnt77} {0}78 79do_execsql_test 3.1 {80  CREATE VIEW v1 AS81    SELECT c16 AS a, *, counter(1) AS x FROM t182    UNION ALL83    SELECT c17 AS a, *, counter(1) AS x FROM t184    UNION ALL85    SELECT c18 AS a, *, counter(1) AS x FROM t186    UNION ALL87    SELECT c19 AS a, *, counter(1) AS x FROM t1;88  SELECT count(*) FROM v1 WHERE c60=60;89} {4}90do_test 3.2 {91  set ::selectH_cnt92} {0}93do_execsql_test 3.3 {94  SELECT count(a) FROM v1 WHERE c60=60;95} {4}96do_execsql_test 3.4 {97  SELECT a FROM v1 WHERE c60=60;98} {16 17 18 19}99do_test 3.5 {100  set ::selectH_cnt101} {0}102do_execsql_test 3.6 {103  SELECT x FROM v1 WHERE c60=60;104} {1 2 3 4}105do_test 3.7 {106  set ::selectH_cnt107} {4}108 109# 2023-02-25 dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15110#111do_execsql_test 4.1 {112  DROP TABLE IF EXISTS t1;113  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);114  SELECT 1 FROM (SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema115                 UNION ALL SELECT a FROM t1);116} {1 1}117 118do_execsql_test 4.2 {119  SELECT DISTINCT name COLLATE rtrim FROM sqlite_schema 120    UNION ALL 121  SELECT a FROM t1122} {v1 t1}123 124#-------------------------------------------------------------------------125# forum post https://sqlite.org/forum/forumpost/b83c7b2168126#127reset_db128do_execsql_test 5.0 {129  CREATE TABLE t1 (val1);130  INSERT INTO t1 VALUES(4);131  INSERT INTO t1 VALUES(5);132  CREATE TABLE t2 (val2);133}134do_execsql_test 5.1 {135  SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2;136} {137  4 5138}139do_execsql_test 5.2 {140  SELECT count(1234) FROM (141    SELECT DISTINCT val1 FROM t1 UNION ALL SELECT val2 FROM t2142  )143} {2}144 145finish_test146