/ Check-in [d02490a2]
Login

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

Overview
Comment:Avoid assuming that "column IS ?", where column is declared UNIQUE, matches only a single row (as "?" might be NULL). Fix for [b8689402].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d02490a2f0cae047087130b4ad8f55f265845c2ffb3bde3b7d507edb54acea6d
User & Date: dan 2019-08-21 14:54:50
References
2019-08-21
14:55 Closed ticket [b8689402]: DISTINCT malfunctions for IS NULL plus 6 other changes artifact: e33394d3 user: dan
Context
2019-08-22
00:53
Fix the OP_SeekGE, OP_SeekGT, OP_SeekLE, and OP_SeekLT opcodes so that they preserve the datatype of the value in the register used as the key. Ticket [d9f584e936c7a8d0] check-in: 81b9f0f5 user: drh tags: trunk
2019-08-21
15:41
Update this branch with latest trunk changes. check-in: 6153bcf4 user: dan tags: nulls-last
14:54
Avoid assuming that "column IS ?", where column is declared UNIQUE, matches only a single row (as "?" might be NULL). Fix for [b8689402]. check-in: d02490a2 user: dan tags: trunk
11:31
Fix a broken assert() in the fts3 snippet code that was failing for queries containging more than 64 phrases. check-in: 4c01e017 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

3761
3762
3763
3764
3765
3766
3767
3768



3769
3770
3771
3772
3773
3774

3775

3776

3777
3778
3779
3780
3781
3782
3783
        assert( j>=pLoop->u.btree.nEq 
            || (pLoop->aLTerm[j]==0)==(j<pLoop->nSkip)
        );
        if( j<pLoop->u.btree.nEq && j>=pLoop->nSkip ){
          u16 eOp = pLoop->aLTerm[j]->eOperator;

          /* Skip over == and IS and ISNULL terms.  (Also skip IN terms when
          ** doing WHERE_ORDERBY_LIMIT processing). 



          **
          ** If the current term is a column of an ((?,?) IN (SELECT...)) 
          ** expression for which the SELECT returns more than one column,
          ** check that it is the only column used by this loop. Otherwise,
          ** if it is one of two or more, none of the columns can be
          ** considered to match an ORDER BY term.  */

          if( (eOp & eqOpMask)!=0 ){

            if( eOp & WO_ISNULL ){

              testcase( isOrderDistinct );
              isOrderDistinct = 0;
            }
            continue;  
          }else if( ALWAYS(eOp & WO_IN) ){
            /* ALWAYS() justification: eOp is an equality operator due to the
            ** j<pLoop->u.btree.nEq constraint above.  Any equality other







|
>
>
>





|
>

>
|
>







3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
        assert( j>=pLoop->u.btree.nEq 
            || (pLoop->aLTerm[j]==0)==(j<pLoop->nSkip)
        );
        if( j<pLoop->u.btree.nEq && j>=pLoop->nSkip ){
          u16 eOp = pLoop->aLTerm[j]->eOperator;

          /* Skip over == and IS and ISNULL terms.  (Also skip IN terms when
          ** doing WHERE_ORDERBY_LIMIT processing).  Except, IS and ISNULL
          ** terms imply that the index is not UNIQUE NOT NULL in which case
          ** the loop need to be marked as not order-distinct because it can
          ** have repeated NULL rows.
          **
          ** If the current term is a column of an ((?,?) IN (SELECT...)) 
          ** expression for which the SELECT returns more than one column,
          ** check that it is the only column used by this loop. Otherwise,
          ** if it is one of two or more, none of the columns can be
          ** considered to match an ORDER BY term.
          */
          if( (eOp & eqOpMask)!=0 ){
            if( eOp & (WO_ISNULL|WO_IS) ){
              testcase( eOp & WO_ISNULL );
              testcase( eOp & WO_IS );
              testcase( isOrderDistinct );
              isOrderDistinct = 0;
            }
            continue;  
          }else if( ALWAYS(eOp & WO_IN) ){
            /* ALWAYS() justification: eOp is an equality operator due to the
            ** j<pLoop->u.btree.nEq constraint above.  Any equality other

Changes to test/distinct2.test.

270
271
272
273
274
275
276

277





278



















279

} {
  one 0 1
  one 1 1
  two 0 1
  two 1 1
}




























finish_test








>
|
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
} {
  one 0 1
  one 1 1
  two 0 1
  two 1 1
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3000 {
  CREATE TABLE t0 (c0, c1 NOT NULL DEFAULT 1, c2, PRIMARY KEY (c0, c1));
  INSERT INTO t0(c2) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
  INSERT INTO t0(c2) VALUES('a');
}

do_execsql_test 3010 {
  SELECT DISTINCT * FROM t0 WHERE NULL IS t0.c0;
} {
  {} 1 {}
  {} 1 a
}

do_execsql_test 3020 {
  ANALYZE;
}

do_execsql_test 3030 {
  SELECT DISTINCT * FROM t0 WHERE NULL IS c0;
} {
  {} 1 {}
  {} 1 a
}

finish_test