/ Check-in [0303d6bc]
Login

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

Overview
Comment:Make sure the omit-noop-left-join optimization is not applied if columns of the LEFT JOIN are used in the ORDER BY clause. Ticket [be84e357c035]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0303d6bc7112e6f810ae1bd75cafc5ffc51f5212
User & Date: drh 2013-09-03 14:03:47
Context
2013-09-03
14:49
Merge in all the latest trunk changes, including the win32-longpath VFS and the fix for the segfault in the omit-left-join optimization. check-in: cdd3838b user: drh tags: sessions
14:43
Fix another problem in stat4 sample selection. check-in: d59f5809 user: dan tags: trunk
14:33
Make sure the omit-noop-left-join optimization is not applied if columns of the LEFT JOIN are used in the ORDER BY clause. Ticket [be84e357c035]. Increase version number to 3.8.0.2. check-in: cce54186 user: drh tags: branch-3.8.0
14:03
Make sure the omit-noop-left-join optimization is not applied if columns of the LEFT JOIN are used in the ORDER BY clause. Ticket [be84e357c035] check-in: 0303d6bc user: drh tags: trunk
2013-09-02
20:22
Simplify branch coverage testing by interchanging the order of two tests in the whereLoopInsert() function. check-in: f7079b53 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  5905   5905   #endif
  5906   5906     /* Attempt to omit tables from the join that do not effect the result */
  5907   5907     if( pWInfo->nLevel>=2
  5908   5908      && pResultSet!=0
  5909   5909      && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
  5910   5910     ){
  5911   5911       Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
  5912         -    if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
         5912  +    if( sWLB.pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, sWLB.pOrderBy);
  5913   5913       while( pWInfo->nLevel>=2 ){
  5914   5914         WhereTerm *pTerm, *pEnd;
  5915   5915         pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
  5916   5916         if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
  5917   5917         if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
  5918   5918          && (pLoop->wsFlags & WHERE_ONEROW)==0
  5919   5919         ){

Changes to test/where.test.

  1299   1299   do_test where-17.5 {
  1300   1300     execsql {
  1301   1301       CREATE TABLE tother(a, b);
  1302   1302       INSERT INTO tother VALUES(1, 3.7);
  1303   1303       SELECT id, a FROM tbooking, tother WHERE id>a;
  1304   1304     }
  1305   1305   } {42 1 43 1}
         1306  +
         1307  +# Ticket [be84e357c035d068135f20bcfe82761bbf95006b]  2013-09-03
         1308  +# Segfault during query involving LEFT JOIN column in the ORDER BY clause.
         1309  +#
         1310  +do_execsql_test where-18.1 {
         1311  +  CREATE TABLE t181(a);
         1312  +  CREATE TABLE t182(b,c);
         1313  +  INSERT INTO t181 VALUES(1);
         1314  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
         1315  +} {1}
         1316  +do_execsql_test where-18.2 {
         1317  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
         1318  +} {1}
         1319  +do_execsql_test where-18.3 {
         1320  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
         1321  +} {1}
         1322  +do_execsql_test where-18.4 {
         1323  +  INSERT INTO t181 VALUES(1),(1),(1),(1);
         1324  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
         1325  +} {1}
         1326  +do_execsql_test where-18.5 {
         1327  +  INSERT INTO t181 VALUES(2);
         1328  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
         1329  +} {1 2}
         1330  +do_execsql_test where-18.6 {
         1331  +  INSERT INTO t181 VALUES(2);
         1332  +  SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL;
         1333  +} {1 2}
         1334  +
  1306   1335   
  1307   1336   finish_test