/ Check-in [eef8cbef]
Login

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

Overview
Comment:Proposed fix for the query planner problem of ticket [ec32177c99ccac2b1].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: eef8cbef3c49e6331301a8f3f8a5fd12090b1e99e68487c2d405e53ef771f688
User & Date: drh 2018-01-27 05:40:10
Context
2018-01-27
13:55
New test case for ticket [ec32177c99ccac2b1] that works without the STAT4. check-in: 5259d484 user: drh tags: trunk
05:40
Proposed fix for the query planner problem of ticket [ec32177c99ccac2b1]. check-in: eef8cbef user: drh tags: trunk
03:26
Fix compiler warnings in zipfile.c. check-in: bed610d9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4627   4627     ** preserves SQLite's legacy behaviour in the following two cases:
  4628   4628     **
  4629   4629     **   FROM ... WHERE random()>0;           -- eval random() once per row
  4630   4630     **   FROM ... WHERE (SELECT random())>0;  -- eval random() once overall
  4631   4631     */
  4632   4632     for(ii=0; ii<sWLB.pWC->nTerm; ii++){
  4633   4633       WhereTerm *pT = &sWLB.pWC->a[ii];
         4634  +    if( pT->wtFlags & TERM_VIRTUAL ) continue;
  4634   4635       if( pT->prereqAll==0 && (nTabList==0 || exprIsDeterministic(pT->pExpr)) ){
  4635   4636         sqlite3ExprIfFalse(pParse, pT->pExpr, pWInfo->iBreak, SQLITE_JUMPIFNULL);
  4636   4637         pT->wtFlags |= TERM_CODED;
  4637   4638       }
  4638   4639     }
  4639   4640   
  4640   4641     if( wctrlFlags & WHERE_WANT_DISTINCT ){

Changes to test/whereF.test.

   210    210       INSERT INTO t VALUES('{"foo":"meep","other":12345}');
   211    211       INSERT INTO t VALUES('{"foo":"bingo","alt":5.25}');
   212    212       SELECT * FROM t
   213    213        WHERE(EXISTS(SELECT 1 FROM json_each(t.json,"$.foo") j
   214    214                      WHERE j.value = 'meep'));
   215    215     } {{{"foo":"meep","other":12345}}}
   216    216   }
          217  +
          218  +# 2018-01-27
          219  +# Ticket https://sqlite.org/src/tktview/ec32177c99ccac2b180fd3ea2083
          220  +# Incorrect result when using the new OR clause factoring optimization
          221  +#
          222  +# This is the original test case as reported on the sqlite-users mailing
          223  +# list
          224  +#
          225  +do_execsql_test 7.1 {
          226  +  DROP TABLE IF EXISTS cd;
          227  +  CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
          228  +  CREATE INDEX cd_idx_genreid ON cd (genreid);
          229  +  INSERT INTO cd  ( cdid, genreid ) VALUES
          230  +                     ( 1,    1 ),
          231  +                     ( 2, NULL ),
          232  +                     ( 3, NULL ),
          233  +                     ( 4, NULL ),
          234  +                     ( 5, NULL );
          235  +  
          236  +  SELECT cdid
          237  +    FROM cd me
          238  +  WHERE 2 > (
          239  +    SELECT COUNT( * )
          240  +      FROM cd rownum__emulation
          241  +    WHERE
          242  +      (
          243  +        me.genreid IS NOT NULL
          244  +          AND
          245  +        rownum__emulation.genreid IS NULL
          246  +      )
          247  +        OR
          248  +      (
          249  +        me.genreid IS NOT NULL
          250  +          AND
          251  +        rownum__emulation.genreid IS NOT NULL
          252  +          AND
          253  +        rownum__emulation.genreid < me.genreid
          254  +      )
          255  +        OR
          256  +      (
          257  +        ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
          258  +  AND rownum__emulation.genreid IS NULL ) )
          259  +          AND
          260  +        rownum__emulation.cdid > me.cdid
          261  +      )
          262  +  );
          263  +} {4 5}
          264  +
          265  +# Simplified test cases from the ticket
          266  +#
          267  +do_execsql_test 7.2 {
          268  +  DROP TABLE IF EXISTS t1;
          269  +  DROP TABLE IF EXISTS t2;
          270  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          271  +  INSERT INTO t1(a,b) VALUES(1,1);
          272  +  CREATE TABLE t2(aa INTEGER PRIMARY KEY, bb);
          273  +  INSERT INTO t2(aa,bb) VALUES(1,1),(2,NULL),(3,NULL);
          274  +  SELECT (
          275  +    SELECT COUNT(*) FROM t2
          276  +     WHERE ( t1.b IS NOT NULL AND t2.bb IS NULL )
          277  +        OR ( t2.bb < t1.b )
          278  +        OR ( t1.b IS t2.bb AND t2.aa > t1.a )
          279  +    )
          280  +    FROM t1;
          281  +} {2}
          282  +
   217    283   
   218    284   finish_test