/ Check-in [caebf879]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:In the WHERE-constraint propagation optimization, if there are duplicate constraint, make sure only one of them propagates. Proposed fix for ticket [cf5ed20fc8621b165].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.25
Files: files | file ages | folders
SHA3-256: caebf8792576752d1cd8af29f00ba3909b2448018c830287007328f6c31072c1
User & Date: drh 2018-10-25 15:25:10
Context
2018-11-05
13:35
Fix a assert() in the query planner that can arise when doing row-value operations on a PRIMARY KEY that contains duplicate columns. Ticket [1a84668dcfdebaf12415d]. check-in: 1309c84a user: drh tags: branch-3.25
2018-10-25
15:25
In the WHERE-constraint propagation optimization, if there are duplicate constraint, make sure only one of them propagates. Proposed fix for ticket [cf5ed20fc8621b165]. check-in: caebf879 user: drh tags: branch-3.25
14:15
In the WHERE-constraint propagation optimization, if there are duplicate constraint, make sure only one of them propagates. Proposed fix for ticket [cf5ed20fc8621b165]. check-in: 5d5b596f user: drh tags: trunk
2018-10-01
14:05
Fix a potential crash that can occur while reading an index from a corrupt database file. The corruption is a record-header-size that is larger than 0x7fffffff. Problem detected by OSSFuzz against GDAL and reported to us (with a suggested fix) by Even Rouault. The test case is in TH3. check-in: 5d291658 user: drh tags: branch-3.25
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4070   4070   #endif
  4071   4071   
  4072   4072     return 1;
  4073   4073   }
  4074   4074   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  4075   4075   
  4076   4076   /*
  4077         -** A structure to keep track of all of the column values that fixed to
         4077  +** A structure to keep track of all of the column values that are fixed to
  4078   4078   ** a known value due to WHERE clause constraints of the form COLUMN=VALUE.
  4079   4079   */
  4080   4080   typedef struct WhereConst WhereConst;
  4081   4081   struct WhereConst {
  4082   4082     Parse *pParse;   /* Parsing context */
  4083   4083     int nConst;      /* Number for COLUMN=CONSTANT terms */
  4084   4084     int nChng;       /* Number of times a constant is propagated */
  4085   4085     Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
  4086   4086   };
  4087   4087   
  4088   4088   /*
  4089         -** Add a new entry to the pConst object
         4089  +** Add a new entry to the pConst object.  Except, do not add duplicate
         4090  +** pColumn entires.
  4090   4091   */
  4091   4092   static void constInsert(
  4092         -  WhereConst *pConst,
  4093         -  Expr *pColumn,
  4094         -  Expr *pValue
         4093  +  WhereConst *pConst,      /* The WhereConst into which we are inserting */
         4094  +  Expr *pColumn,           /* The COLUMN part of the constraint */
         4095  +  Expr *pValue             /* The VALUE part of the constraint */
  4095   4096   ){
         4097  +  int i;
         4098  +  assert( pColumn->op==TK_COLUMN );
         4099  +
         4100  +  /* 2018-10-25 ticket [cf5ed20f]
         4101  +  ** Make sure the same pColumn is not inserted more than once */
         4102  +  for(i=0; i<pConst->nConst; i++){
         4103  +    const Expr *pExpr = pConst->apExpr[i*2];
         4104  +    assert( pExpr->op==TK_COLUMN );
         4105  +    if( pExpr->iTable==pColumn->iTable
         4106  +     && pExpr->iColumn==pColumn->iColumn
         4107  +    ){
         4108  +      return;  /* Already present.  Return without doing anything. */
         4109  +    }
         4110  +  }
  4096   4111   
  4097   4112     pConst->nConst++;
  4098   4113     pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
  4099   4114                            pConst->nConst*2*sizeof(Expr*));
  4100   4115     if( pConst->apExpr==0 ){
  4101   4116       pConst->nConst = 0;
  4102   4117     }else{

Changes to test/whereL.test.

   106    106       FROM A,
   107    107            (SELECT id,yy,zz FROM C) subq,
   108    108            B
   109    109      WHERE A.id='1'
   110    110        AND A.id=subq.yy
   111    111        AND B.id=subq.zz;
   112    112   } {1}  
          113  +
          114  +# 2018-10-25: Ticket [cf5ed20f]
          115  +# Incorrect join result with duplicate WHERE clause constraint.
          116  +#
          117  +do_execsql_test 400 {
          118  +  CREATE TABLE x(a, b, c);
          119  +  CREATE TABLE y(a, b);
          120  +  INSERT INTO x VALUES (1, 0, 1);
          121  +  INSERT INTO y VALUES (1, 2);
          122  +  SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
          123  +} {}
   113    124   
   114    125   finish_test