Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | branch-3.8.7 |
Files: | files | file ages | folders |
SHA1: |
5a80957b04d68cc7e0325873a409d83c |
User & Date: | drh 2014-12-04 16:29:21.076 |
Context
2014-12-05
| ||
14:51 | When closing a (shared-cache) database connection, be sure to clear out all KeyInfo objects cached on Index objects. Fix for ticket [e4a18565a36884b00edf]. (check-in: b7905b8c5f user: drh tags: branch-3.8.7) | |
2014-12-04
| ||
16:29 | If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. (check-in: 5a80957b04 user: drh tags: branch-3.8.7) | |
16:27 | If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. (check-in: 6f6fcbe473 user: drh tags: trunk) | |
2014-11-18
| ||
20:57 | Version 3.8.7.2. (check-in: 2ab564bf96 user: drh tags: release, version-3.8.7.2, branch-3.8.7) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1389 1390 1391 1392 1393 1394 1395 | case TK_INTEGER: case TK_STRING: case TK_FLOAT: case TK_BLOB: return 0; case TK_COLUMN: assert( p->pTab!=0 ); | > | | 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 | case TK_INTEGER: case TK_STRING: case TK_FLOAT: case TK_BLOB: return 0; case TK_COLUMN: assert( p->pTab!=0 ); return ExprHasProperty(p, EP_CanBeNull) || (p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0); default: return 1; } } /* ** Return TRUE if the given expression is a constant which would be |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
316 317 318 319 320 321 322 323 324 325 326 327 328 329 | break; } } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; pSchema = pExpr->pTab->pSchema; } } /* if( pSrcList ) */ #ifndef SQLITE_OMIT_TRIGGER /* If we have not already resolved the name, then maybe ** it is a new.* or old.* trigger argument reference | > > > > | 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 | break; } } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; assert( (pMatch->jointype & JT_RIGHT)==0 ); /* RIGHT JOIN not (yet) supported */ if( (pMatch->jointype & JT_LEFT)!=0 ){ ExprSetProperty(pExpr, EP_CanBeNull); } pSchema = pExpr->pTab->pSchema; } } /* if( pSrcList ) */ #ifndef SQLITE_OMIT_TRIGGER /* If we have not already resolved the name, then maybe ** it is a new.* or old.* trigger argument reference |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2010 2011 2012 2013 2014 2015 2016 | AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ Table *pTab; /* Table for TK_COLUMN expressions. */ }; /* ** The following are the meanings of bits in the Expr.flags field. */ | | > | 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 | AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ Table *pTab; /* Table for TK_COLUMN expressions. */ }; /* ** The following are the meanings of bits in the Expr.flags field. */ #define EP_FromJoin 0x000001 /* Originates in ON/USING clause of outer join */ #define EP_Agg 0x000002 /* Contains one or more aggregate functions */ #define EP_Resolved 0x000004 /* IDs have been resolved to COLUMNs */ #define EP_Error 0x000008 /* Expression contains one or more errors */ #define EP_Distinct 0x000010 /* Aggregate function with DISTINCT keyword */ #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */ #define EP_DblQuoted 0x000040 /* token.z was originally in "..." */ #define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */ #define EP_Collate 0x000100 /* Tree contains a TK_COLLATE operator */ #define EP_Generic 0x000200 /* Ignore COLLATE or affinity on this tree */ #define EP_IntValue 0x000400 /* Integer value contained in u.iValue */ #define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */ #define EP_Skip 0x001000 /* COLLATE, AS, or UNLIKELY */ #define EP_Reduced 0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_Constant 0x080000 /* Node is a constant */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ /* ** These macros can be used to test, set, or clear bits in the ** Expr.flags field. */ #define ExprHasProperty(E,P) (((E)->flags&(P))!=0) #define ExprHasAllProperty(E,P) (((E)->flags&(P))==(P)) |
︙ | ︙ |
Changes to test/join5.test.
︙ | ︙ | |||
102 103 104 105 106 107 108 109 110 | do_test join5-2.11 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL} } {} do_test join5-2.12 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} } {} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | do_test join5-2.11 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL} } {} do_test join5-2.12 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} } {} # Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601 # Incorrect output on a LEFT JOIN. # do_execsql_test join5-3.1 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE x1(a); INSERT INTO x1 VALUES(1); CREATE TABLE x2(b NOT NULL); CREATE TABLE x3(c, d); INSERT INTO x3 VALUES('a', NULL); INSERT INTO x3 VALUES('b', NULL); INSERT INTO x3 VALUES('c', NULL); SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b; } {1 {} {} {}} do_execsql_test join5-3.2 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t4; DROP TABLE IF EXISTS t5; CREATE TABLE t1(x text NOT NULL, y text); CREATE TABLE t2(u text NOT NULL, x text NOT NULL); CREATE TABLE t3(w text NOT NULL, v text); CREATE TABLE t4(w text NOT NULL, z text NOT NULL); CREATE TABLE t5(z text NOT NULL, m text); INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL); INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL); INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL); INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL); INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c', 'f6d7661f-4efe-4c90-87b5-858e61cd178b'); SELECT * FROM t3 INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL LEFT JOIN t4 ON t4.w = t3.w LEFT JOIN t5 ON t5.z = t4.z LEFT JOIN t2 ON t2.u = t5.m LEFT JOIN t1 xyz ON xyz.y = t2.x; } {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}} do_execsql_test join5-3.3 { DROP TABLE IF EXISTS x1; DROP TABLE IF EXISTS x2; DROP TABLE IF EXISTS x3; CREATE TABLE x1(a); INSERT INTO x1 VALUES(1); CREATE TABLE x2(b NOT NULL); CREATE TABLE x3(c, d); INSERT INTO x3 VALUES('a', NULL); INSERT INTO x3 VALUES('b', NULL); INSERT INTO x3 VALUES('c', NULL); SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b; } {} finish_test |