SQLite

Check-in [2afcb2c752]
Login

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

Overview
Comment:Fixed crash bugs. Still sometimes gets the wrong answers.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | skip-ahead-distinct
Files: files | file ages | folders
SHA1: 2afcb2c75280136b061225a026f4fb5f290d9a7c
User & Date: drh 2016-04-15 13:24:20.650
Context
2016-04-15
14:13
Several new test cases that cause failures. (check-in: 0379f2cff6 user: drh tags: skip-ahead-distinct)
13:24
Fixed crash bugs. Still sometimes gets the wrong answers. (check-in: 2afcb2c752 user: drh tags: skip-ahead-distinct)
01:55
Progress toward getting DISTINCT to use a seek to advance to the next distinct row, when driven by an appropriate index. (check-in: 9e14aa14cf user: drh tags: skip-ahead-distinct)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
4577
4578
4579
4580
4581
4582
4583
4584



4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596

4597
4598
4599
4600
4601
4602







4603
4604
4605
4606
4607
4608
4609

4577
4578
4579
4580
4581
4582
4583

4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599






4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613








-
+
+
+












+
-
-
-
-
-
-
+
+
+
+
+
+
+








  sqlite3ExprCacheClear(pParse);
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    if( pLevel->op!=OP_Noop ){
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
      ){
        int j, k, op;
        int r1 = pParse->nMem+1;
        int n = 0;
        ExprList *pX = pWInfo->pDistinctSet;
        for(j=0; j<pX->nExpr; j++){
          Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
          if( pE->op==TK_COLUMN && pE->iTable==pLevel->iTabCur ) n++;
        }
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        if( n>0 ){
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);
        VdbeCoverageIf(v, op==OP_SeekGT);
        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
        sqlite3VdbeJumpHere(v, k);
          op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
          k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
          VdbeCoverageIf(v, op==OP_SeekLT);
          VdbeCoverageIf(v, op==OP_SeekGT);
          sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
          sqlite3VdbeJumpHere(v, k);
        }
      }else{
        sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
        sqlite3VdbeChangeP5(v, pLevel->p5);
        VdbeCoverage(v);
        VdbeCoverageIf(v, pLevel->op==OP_Next);
        VdbeCoverageIf(v, pLevel->op==OP_Prev);
        VdbeCoverageIf(v, pLevel->op==OP_VNext);
︙
Added test/distinct2.test. 





















































1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

# 2016-04-15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is DISTINCT queries using the skip-ahead 
# optimization.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

set testprefix distinct2

do_execsql_test 100 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES(0),(1),(2);
  CREATE TABLE t2 AS
     SELECT DISTINCT a.x AS aa, b.x AS bb
      FROM t1 a, t1 b;
  SELECT *, '|' FROM t2 ORDER BY aa, bb;
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
do_execsql_test 110 {
  DROP TABLE t2;
  CREATE TABLE t2 AS
     SELECT DISTINCT a.x AS aa, b.x AS bb
       FROM t1 a, t1 b
      WHERE a.x IN t1 AND b.x IN t1;
  SELECT *, '|' FROM t2 ORDER BY aa, bb;
} {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
do_execsql_test 120 {
  CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL);
  INSERT INTO t102 VALUES ('0'),('1'),('2');
  DROP TABLE t2;
  CREATE TABLE t2 AS
    SELECT DISTINCT * 
    FROM t102 AS t0 
    JOIN t102 AS t4 ON (t2.i0 IN t102)
    NATURAL JOIN t102 AS t3
    JOIN t102 AS t1 ON (t0.i0 IN t102)
    JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0));
  SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5;
} {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |}


finish_test