/ Check-in [5a80957b]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.8.7
Files: files | file ages | folders
SHA1: 5a80957b04d68cc7e0325873a409d83c1f11b52f
User & Date: drh 2014-12-04 16:29:21
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: b7905b8c 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: 5a80957b 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: 6f6fcbe4 user: drh tags: trunk
2014-11-18
20:57
Version 3.8.7.2. check-in: 2ab564bf user: drh tags: release, version-3.8.7.2, branch-3.8.7
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1389   1389       case TK_INTEGER:
  1390   1390       case TK_STRING:
  1391   1391       case TK_FLOAT:
  1392   1392       case TK_BLOB:
  1393   1393         return 0;
  1394   1394       case TK_COLUMN:
  1395   1395         assert( p->pTab!=0 );
  1396         -      return p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0;
         1396  +      return ExprHasProperty(p, EP_CanBeNull) ||
         1397  +             (p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0);
  1397   1398       default:
  1398   1399         return 1;
  1399   1400     }
  1400   1401   }
  1401   1402   
  1402   1403   /*
  1403   1404   ** Return TRUE if the given expression is a constant which would be

Changes to src/resolve.c.

   316    316               break;
   317    317             }
   318    318           }
   319    319         }
   320    320         if( pMatch ){
   321    321           pExpr->iTable = pMatch->iCursor;
   322    322           pExpr->pTab = pMatch->pTab;
          323  +        assert( (pMatch->jointype & JT_RIGHT)==0 ); /* RIGHT JOIN not (yet) supported */
          324  +        if( (pMatch->jointype & JT_LEFT)!=0 ){
          325  +          ExprSetProperty(pExpr, EP_CanBeNull);
          326  +        }
   323    327           pSchema = pExpr->pTab->pSchema;
   324    328         }
   325    329       } /* if( pSrcList ) */
   326    330   
   327    331   #ifndef SQLITE_OMIT_TRIGGER
   328    332       /* If we have not already resolved the name, then maybe 
   329    333       ** it is a new.* or old.* trigger argument reference

Changes to src/sqliteInt.h.

  2010   2010     AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
  2011   2011     Table *pTab;           /* Table for TK_COLUMN expressions. */
  2012   2012   };
  2013   2013   
  2014   2014   /*
  2015   2015   ** The following are the meanings of bits in the Expr.flags field.
  2016   2016   */
  2017         -#define EP_FromJoin  0x000001 /* Originated in ON or USING clause of a join */
         2017  +#define EP_FromJoin  0x000001 /* Originates in ON/USING clause of outer join */
  2018   2018   #define EP_Agg       0x000002 /* Contains one or more aggregate functions */
  2019   2019   #define EP_Resolved  0x000004 /* IDs have been resolved to COLUMNs */
  2020   2020   #define EP_Error     0x000008 /* Expression contains one or more errors */
  2021   2021   #define EP_Distinct  0x000010 /* Aggregate function with DISTINCT keyword */
  2022   2022   #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */
  2023   2023   #define EP_DblQuoted 0x000040 /* token.z was originally in "..." */
  2024   2024   #define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */
................................................................................
  2030   2030   #define EP_Reduced   0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
  2031   2031   #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
  2032   2032   #define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
  2033   2033   #define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
  2034   2034   #define EP_NoReduce  0x020000 /* Cannot EXPRDUP_REDUCE this Expr */
  2035   2035   #define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
  2036   2036   #define EP_Constant  0x080000 /* Node is a constant */
         2037  +#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
  2037   2038   
  2038   2039   /*
  2039   2040   ** These macros can be used to test, set, or clear bits in the 
  2040   2041   ** Expr.flags field.
  2041   2042   */
  2042   2043   #define ExprHasProperty(E,P)     (((E)->flags&(P))!=0)
  2043   2044   #define ExprHasAllProperty(E,P)  (((E)->flags&(P))==(P))

Changes to test/join5.test.

   102    102   do_test join5-2.11 {
   103    103     execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL}
   104    104   } {}
   105    105   do_test join5-2.12 {
   106    106     execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL}
   107    107   } {}
   108    108   
          109  +# Ticket https://www.sqlite.org/src/tktview/6f2222d550f5b0ee7ed37601
          110  +# Incorrect output on a LEFT JOIN.
          111  +#
          112  +do_execsql_test join5-3.1 {
          113  +  DROP TABLE IF EXISTS t1;
          114  +  DROP TABLE IF EXISTS t2;
          115  +  DROP TABLE IF EXISTS t3;
          116  +  CREATE TABLE x1(a);
          117  +  INSERT INTO x1 VALUES(1);
          118  +  CREATE TABLE x2(b NOT NULL);
          119  +  CREATE TABLE x3(c, d);
          120  +  INSERT INTO x3 VALUES('a', NULL);
          121  +  INSERT INTO x3 VALUES('b', NULL);
          122  +  INSERT INTO x3 VALUES('c', NULL);
          123  +  SELECT * FROM x1 LEFT JOIN x2 LEFT JOIN x3 ON x3.d = x2.b;
          124  +} {1 {} {} {}}
          125  +do_execsql_test join5-3.2 {
          126  +  DROP TABLE IF EXISTS t1;
          127  +  DROP TABLE IF EXISTS t2;
          128  +  DROP TABLE IF EXISTS t3;
          129  +  DROP TABLE IF EXISTS t4;
          130  +  DROP TABLE IF EXISTS t5;
          131  +  CREATE TABLE t1(x text NOT NULL, y text);
          132  +  CREATE TABLE t2(u text NOT NULL, x text NOT NULL);
          133  +  CREATE TABLE t3(w text NOT NULL, v text);
          134  +  CREATE TABLE t4(w text NOT NULL, z text NOT NULL);
          135  +  CREATE TABLE t5(z text NOT NULL, m text);
          136  +  INSERT INTO t1 VALUES('f6d7661f-4efe-4c90-87b5-858e61cd178b',NULL);
          137  +  INSERT INTO t1 VALUES('f6ea82c3-2cad-45ce-ae8f-3ddca4fb2f48',NULL);
          138  +  INSERT INTO t1 VALUES('f6f47499-ecb4-474b-9a02-35be73c235e5',NULL);
          139  +  INSERT INTO t1 VALUES('56f47499-ecb4-474b-9a02-35be73c235e5',NULL);
          140  +  INSERT INTO t3 VALUES('007f2033-cb20-494c-b135-a1e4eb66130c',
          141  +                        'f6d7661f-4efe-4c90-87b5-858e61cd178b');
          142  +  SELECT *
          143  +    FROM t3
          144  +         INNER JOIN t1 ON t1.x= t3.v AND t1.y IS NULL
          145  +         LEFT JOIN t4  ON t4.w = t3.w
          146  +         LEFT JOIN t5  ON t5.z = t4.z
          147  +         LEFT JOIN t2  ON t2.u = t5.m
          148  +         LEFT JOIN t1 xyz ON xyz.y = t2.x;
          149  +} {007f2033-cb20-494c-b135-a1e4eb66130c f6d7661f-4efe-4c90-87b5-858e61cd178b f6d7661f-4efe-4c90-87b5-858e61cd178b {} {} {} {} {} {} {} {} {}}
          150  +do_execsql_test join5-3.3 {
          151  +  DROP TABLE IF EXISTS x1;
          152  +  DROP TABLE IF EXISTS x2;
          153  +  DROP TABLE IF EXISTS x3;
          154  +  CREATE TABLE x1(a);
          155  +  INSERT INTO x1 VALUES(1);
          156  +  CREATE TABLE x2(b NOT NULL);
          157  +  CREATE TABLE x3(c, d);
          158  +  INSERT INTO x3 VALUES('a', NULL);
          159  +  INSERT INTO x3 VALUES('b', NULL);
          160  +  INSERT INTO x3 VALUES('c', NULL);
          161  +  SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
          162  +} {}
   109    163   
   110    164   finish_test