AryaWu/sqlite

1# 2021 February 152#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# This file implements regression tests for SQLite library.  The12# focus of this file is testing optimizations associated with "IS NULL"13# and "IS NOT NULL" operators on columns with NOT NULL constraints.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set testprefix notnull219 20do_execsql_test 1.0 {21  CREATE TABLE t1(a, b);22  CREATE TABLE t2(c, d NOT NULL);23 24  WITH x(i) AS (25    SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100026  )27  INSERT INTO t1 SELECT i, i FROM x;28  INSERT INTO t2 SELECT * FROM t1;29}30 31 32do_vmstep_test 1.1.1 {33  SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL;34} 100 {}35do_vmstep_test 1.1.2 {36  SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND c IS NULL;37} +1000 {}38 39do_vmstep_test 1.2.1 {40  SELECT * FROM ( SELECT * FROM t2 ) WHERE d IS NULL41} 100 {}42do_vmstep_test 1.2.2 {43  SELECT * FROM ( SELECT * FROM t2 ) WHERE c IS NULL44} +1000 {}45 46do_vmstep_test 1.3.1 {47  SELECT * FROM t2 WHERE d IS NULL48} 100 {}49do_vmstep_test 1.3.2 {50  SELECT * FROM t2 WHERE c IS NULL51} +1000 {}52 53do_vmstep_test 1.4.1 {54  SELECT (d IS NOT NULL) FROM t2 WHERE 0==( d IS NOT NULL )55} 100 {}56do_vmstep_test 1.4.2 {57  SELECT * FROM t2 WHERE 0==( c IS NOT NULL )58} +1000 {}59 60do_vmstep_test 1.5.1 {61  SELECT count(*) FROM t2 WHERE EXISTS(62    SELECT 1 FROM t1 WHERE t1.a=450 AND t2.d IS NULL63  )64} 7000 {0}65do_vmstep_test 1.5.2 {66  SELECT count(*) FROM t2 WHERE EXISTS(67    SELECT 1 FROM t1 WHERE t1.a=450 AND t2.c IS NULL68  )69} 4000 {0}70 71#-------------------------------------------------------------------------72reset_db73do_execsql_test 2.0 {74  CREATE TABLE T1(a INTEGER PRIMARY KEY, b);75  CREATE TABLE T3(k, v);76}77 78do_execsql_test 2.1 {79  SELECT * FROM (SELECT a, b FROM t1) LEFT JOIN t3 ON a IS NULL;80}81 82 83 84#-------------------------------------------------------------------------85reset_db86do_execsql_test 3.0 {87  CREATE TABLE t0(c0 PRIMARY KEY);88  INSERT INTO t0(c0) VALUES (0);89}90do_execsql_test 3.1 {91  SELECT * FROM t0 WHERE ((c0 NOT NULL) AND 1) OR (c0 == NULL);92} {0}93 94# 2021-07-22 https://sqlite.org/forum/forumpost/2078b7edd295#96reset_db97do_execsql_test 4.0 {98  SELECT *, '/'99  FROM (100      SELECT NULL val FROM (SELECT 1)101      UNION ALL102      SELECT 'missing' FROM (SELECT 1)103  ) a104  LEFT JOIN (SELECT 1)105      ON a.val IS NULL;106} {{} 1 / missing {} /}107do_execsql_test 4.1 {108  CREATE TABLE t1(a INT);109  INSERT INTO t1(a) VALUES(1);110  CREATE TABLE t2(b INT);111  SELECT * FROM (SELECT 3 AS c FROM t1) AS t3 LEFT JOIN t2 ON c IS NULL;112} {3 {}}113 114# 2024-03-08 https://sqlite.org/forum/forumpost/440f2a2f17115#116reset_db117do_execsql_test 5.0 {118  CREATE TABLE t1(a INT NOT NULL);119  SELECT a IS NULL, a IS NOT NULL, count(*) FROM t1;120} {1 0 0}121 122finish_test123