/ Check-in [1e86d81d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix a problem causing SQLite not to use stat4 or stat3 data to analyze constraints of the form "column = expr COLLATE collation" (those with an explicit COLLATE on the non-column side of the comparison operator).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1e86d81d46c9da6aaee0c6938ee40933f35e3d0d
User & Date: dan 2013-08-31 17:21:26
Context
2013-08-31
18:36
Cleanup of the windows VFS, including added support for Cygwin, fixes for compiler warnings under unusual configurations, and improved diagnostic error messages. check-in: c94933f1 user: drh tags: trunk
17:21
Fix a problem causing SQLite not to use stat4 or stat3 data to analyze constraints of the form "column = expr COLLATE collation" (those with an explicit COLLATE on the non-column side of the comparison operator). check-in: 1e86d81d user: dan tags: trunk
14:56
Reenable a test accidentally disabled when the stat4 branch was merged. check-in: d9fadc8f user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbemem.c.

  1307   1307   
  1308   1308     struct ValueNewStat4Ctx alloc;
  1309   1309     alloc.pParse = pParse;
  1310   1310     alloc.pIdx = pIdx;
  1311   1311     alloc.ppRec = ppRec;
  1312   1312     alloc.iVal = iVal;
  1313   1313   
         1314  +  /* Skip over any TK_COLLATE nodes */
         1315  +  pExpr = sqlite3ExprSkipCollate(pExpr);
         1316  +
  1314   1317     if( !pExpr ){
  1315   1318       pVal = valueNew(pParse->db, &alloc);
  1316   1319       if( pVal ){
  1317   1320         sqlite3VdbeMemSetNull((Mem*)pVal);
  1318   1321         *pbOk = 1;
  1319   1322       }
  1320   1323     }else if( pExpr->op==TK_VARIABLE

Changes to test/analyze9.test.

   417    417   do_eqp_test 9.4.1 {
   418    418     SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
   419    419   } {/t1 USING INDEX i2/}
   420    420   set value_d [expr 99]
   421    421   do_eqp_test 9.4.2 {
   422    422     SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
   423    423   } {/t1 USING INDEX i1/}
          424  +
          425  +#-------------------------------------------------------------------------
          426  +# Check that the planner takes stat4 data into account when considering
          427  +# "IS NULL" and "IS NOT NULL" constraints.
          428  +#
          429  +do_execsql_test 10.1.1 {
          430  +  DROP TABLE IF EXISTS t3;
          431  +  CREATE TABLE t3(a, b);
          432  +  CREATE INDEX t3a ON t3(a);
          433  +  CREATE INDEX t3b ON t3(b);
          434  +}
          435  +do_test 10.1.2 {
          436  +  for {set i 1} {$i < 100} {incr i} {
          437  +    if {$i>90} { set a $i } else { set a NULL }
          438  +    set b [expr $i % 5]
          439  +    execsql "INSERT INTO t3 VALUES($a, $b)"
          440  +  }
          441  +  execsql ANALYZE
          442  +} {}
          443  +do_eqp_test 10.1.3 {
          444  +  SELECT * FROM t3 WHERE a IS NULL AND b = 2
          445  +} {/t3 USING INDEX t3b/}
          446  +do_eqp_test 10.1.4 {
          447  +  SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
          448  +} {/t3 USING INDEX t3a/}
          449  +
          450  +do_execsql_test 10.2.1 {
          451  +  DROP TABLE IF EXISTS t3;
          452  +  CREATE TABLE t3(x, a, b);
          453  +  CREATE INDEX t3a ON t3(x, a);
          454  +  CREATE INDEX t3b ON t3(x, b);
          455  +}
          456  +do_test 10.2.2 {
          457  +  for {set i 1} {$i < 100} {incr i} {
          458  +    if {$i>90} { set a $i } else { set a NULL }
          459  +    set b [expr $i % 5]
          460  +    execsql "INSERT INTO t3 VALUES('xyz', $a, $b)"
          461  +  }
          462  +  execsql ANALYZE
          463  +} {}
          464  +do_eqp_test 10.2.3 {
          465  +  SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2
          466  +} {/t3 USING INDEX t3b/}
          467  +do_eqp_test 10.2.4 {
          468  +  SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2
          469  +} {/t3 USING INDEX t3a/}
          470  +
          471  +#-------------------------------------------------------------------------
          472  +# Check that stat4 data is used correctly with non-default collation
          473  +# sequences.
          474  +#
          475  +foreach {tn schema} {
          476  +  1 {
          477  +    CREATE TABLE t4(a COLLATE nocase, b);
          478  +    CREATE INDEX t4a ON t4(a);
          479  +    CREATE INDEX t4b ON t4(b);
          480  +  }
          481  +  2 {
          482  +    CREATE TABLE t4(a, b);
          483  +    CREATE INDEX t4a ON t4(a COLLATE nocase);
          484  +    CREATE INDEX t4b ON t4(b);
          485  +  }
          486  +} {
          487  +  drop_all_tables
          488  +  do_test 11.$tn.1 { execsql $schema } {}
          489  +
          490  +  do_test 11.$tn.2 {
          491  +    for {set i 0} {$i < 100} {incr i} {
          492  +      if { ($i % 10)==0 } { set a ABC } else { set a DEF }
          493  +      set b [expr $i % 5]
          494  +        execsql { INSERT INTO t4 VALUES($a, $b) }
          495  +    }
          496  +    execsql ANALYZE
          497  +  } {}
          498  +
          499  +  do_eqp_test 11.$tn.3 {
          500  +    SELECT * FROM t4 WHERE a = 'def' AND b = 3;
          501  +  } {/t4 USING INDEX t4b/}
          502  +
          503  +  if {$tn==1} {
          504  +    set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
          505  +    do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
          506  +  } else {
          507  +
          508  +    set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
          509  +    do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}
          510  +
          511  +    set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
          512  +    do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
          513  +  }
          514  +}
          515  +
          516  +foreach {tn schema} {
          517  +  1 {
          518  +    CREATE TABLE t4(x, a COLLATE nocase, b);
          519  +    CREATE INDEX t4a ON t4(x, a);
          520  +    CREATE INDEX t4b ON t4(x, b);
          521  +  }
          522  +  2 {
          523  +    CREATE TABLE t4(x, a, b);
          524  +    CREATE INDEX t4a ON t4(x, a COLLATE nocase);
          525  +    CREATE INDEX t4b ON t4(x, b);
          526  +  }
          527  +} {
          528  +  drop_all_tables
          529  +  do_test 12.$tn.1 { execsql $schema } {}
          530  +
          531  +  do_test 12.$tn.2 {
          532  +    for {set i 0} {$i < 100} {incr i} {
          533  +      if { ($i % 10)==0 } { set a ABC } else { set a DEF }
          534  +      set b [expr $i % 5]
          535  +        execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) }
          536  +    }
          537  +    execsql ANALYZE
          538  +  } {}
          539  +
          540  +  do_eqp_test 12.$tn.3 {
          541  +    SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3;
          542  +  } {/t4 USING INDEX t4b/}
          543  +
          544  +  if {$tn==1} {
          545  +    set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;"
          546  +    do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/}
          547  +  } else {
          548  +    set sql {
          549  +      SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3
          550  +    }
          551  +    do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
          552  +    set sql {
          553  +      SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
          554  +    }
          555  +    do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
          556  +  }
          557  +}
   424    558   
   425    559   finish_test
          560  +
   426    561