Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -783,48 +783,10 @@ pExpr->iColumn--; pExpr->affExpr = SQLITE_AFF_INTEGER; break; } - /* An " IS NOT NULL" or " IS NULL". After resolving the - ** LHS, check if there is a NOT NULL constraint in the schema that - ** means the value of the expression can be determined immediately. - ** If that is the case, replace the current expression node with - ** a TK_TRUEFALSE node. - ** - ** If the node is replaced with a TK_TRUEFALSE node, then also restore - ** the NameContext ref-counts to the state they where in before the - ** LHS expression was resolved. This prevents the current select - ** from being erroneously marked as correlated in some cases. - */ - case TK_NOTNULL: - case TK_ISNULL: { - int anRef[8]; - NameContext *p; - int i; - for(i=0, p=pNC; p && ipNext, i++){ - anRef[i] = p->nRef; - } - sqlite3WalkExpr(pWalker, pExpr->pLeft); - if( 0==sqlite3ExprCanBeNull(pExpr->pLeft) ){ - if( pExpr->op==TK_NOTNULL ){ - pExpr->u.zToken = "true"; - ExprSetProperty(pExpr, EP_IsTrue); - }else{ - pExpr->u.zToken = "false"; - ExprSetProperty(pExpr, EP_IsFalse); - } - pExpr->op = TK_TRUEFALSE; - for(i=0, p=pNC; p && ipNext, i++){ - p->nRef = anRef[i]; - } - sqlite3ExprDelete(pParse->db, pExpr->pLeft); - pExpr->pLeft = 0; - } - return WRC_Prune; - } - /* A column name: ID ** Or table name and column name: ID.ID ** Or a database, table and column: ID.ID.ID ** ** The TK_ID and TK_OUT cases are combined so that there will only Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -405,13 +405,10 @@ while( p ){ if( ExprHasProperty(p, EP_FromJoin) && (iTable<0 || p->iRightJoinTable==iTable) ){ ExprClearProperty(p, EP_FromJoin); } - if( p->op==TK_COLUMN && p->iTable==iTable ){ - ExprClearProperty(p, EP_CanBeNull); - } if( p->op==TK_FUNCTION && p->x.pList ){ int i; for(i=0; ix.pList->nExpr; i++){ unsetJoinExpr(p->x.pList->a[i].pExpr, iTable); } Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -1405,16 +1405,10 @@ pNew->u.x.leftColumn = aiCurCol[1]; testcase( (prereqLeft | extraRight) != prereqLeft ); pNew->prereqRight = prereqLeft | extraRight; pNew->prereqAll = prereqAll; pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask; - }else if( op==TK_ISNULL && 0==sqlite3ExprCanBeNull(pLeft) ){ - pExpr->op = TK_TRUEFALSE; - pExpr->u.zToken = "false"; - ExprSetProperty(pExpr, EP_IsFalse); - pTerm->prereqAll = 0; - pTerm->eOperator = 0; } } #ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION /* If a term is the BETWEEN operator, create two new virtual terms DELETED test/notnull2.test Index: test/notnull2.test ================================================================== --- test/notnull2.test +++ /dev/null @@ -1,101 +0,0 @@ -# 2021 February 15 -# -# 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. -# -#*********************************************************************** -# This file implements regression tests for SQLite library. The -# focus of this file is testing optimizations associated with "IS NULL" -# and "IS NOT NULL" operators on columns with NOT NULL constraints. -# - -set testdir [file dirname $argv0] -source $testdir/tester.tcl -set testprefix notnull2 - -do_execsql_test 1.0 { - CREATE TABLE t1(a, b); - CREATE TABLE t2(c, d NOT NULL); - - WITH x(i) AS ( - SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 - ) - INSERT INTO t1 SELECT i, i FROM x; - INSERT INTO t2 SELECT * FROM t1; -} - -proc do_vmstep_test {tn sql nstep {res {}}} { - uplevel [list do_execsql_test $tn.0 $sql $res] - - set vmstep [db status vmstep] - if {[string range $nstep 0 0]=="+"} { - set body "if {$vmstep<$nstep} { - error \"got $vmstep, expected more than [string range $nstep 1 end]\" - }" - } else { - set body "if {$vmstep>$nstep} { - error \"got $vmstep, expected less than $nstep\" - }" - } - - # set name "$tn.vmstep=$vmstep,expect=$nstep" - set name "$tn.1" - uplevel [list do_test $name $body {}] -} - -do_vmstep_test 1.1.1 { - SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND d IS NULL; -} 100 {} -do_vmstep_test 1.1.2 { - SELECT * FROM t1 LEFT JOIN t2 WHERE a=c AND c IS NULL; -} +1000 {} - -do_vmstep_test 1.2.1 { - SELECT * FROM ( SELECT * FROM t2 ) WHERE d IS NULL -} 100 {} -do_vmstep_test 1.2.2 { - SELECT * FROM ( SELECT * FROM t2 ) WHERE c IS NULL -} +1000 {} - -do_vmstep_test 1.3.1 { - SELECT * FROM t2 WHERE d IS NULL -} 100 {} -do_vmstep_test 1.3.2 { - SELECT * FROM t2 WHERE c IS NULL -} +1000 {} - -do_vmstep_test 1.4.1 { - SELECT (d IS NOT NULL) FROM t2 WHERE 0==( d IS NOT NULL ) -} 100 {} -do_vmstep_test 1.4.2 { - SELECT * FROM t2 WHERE 0==( c IS NOT NULL ) -} +1000 {} - -do_vmstep_test 1.5.1 { - SELECT count(*) FROM t2 WHERE EXISTS( - SELECT t2.d IS NULL FROM t1 WHERE t1.a=450 - ) -} 10000 {1000} -do_vmstep_test 1.5.2 { - SELECT count(*) FROM t2 WHERE EXISTS( - SELECT t2.c IS NULL FROM t1 WHERE t1.a=450 - ) -} +100000 {1000} - -#------------------------------------------------------------------------- -reset_db -do_execsql_test 2.0 { - CREATE TABLE T1(a INTEGER PRIMARY KEY, b); - CREATE TABLE T3(k, v); -} - -do_execsql_test 2.1 { - SELECT * FROM (SELECT a, b FROM t1) LEFT JOIN t3 ON a IS NULL; -} - -finish_test -