SQLite

Check-in [085e863713]
Login

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

Overview
Comment:Test cases.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | multikey-opt-idea
Files: files | file ages | folders
SHA3-256: 085e863713a3f2d420c0076b275a6ac445a59d4d93f9eb0e8503b4e3f5589249
User & Date: drh 2018-06-07 16:07:00.142
Context
2018-06-07
17:32
Remove the NextIfOpen and PrevIfOpen opcodes which are no longer needed when the IN-early-out optimization is working. (check-in: 439c816227 user: drh tags: multikey-opt-idea)
16:07
Test cases. (check-in: 085e863713 user: drh tags: multikey-opt-idea)
15:28
Merge the ".stat/.eqp" CLI fix from trunk. (check-in: a91cad3381 user: drh tags: multikey-opt-idea)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/wherecode.c.
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
            int iCol = aiMap ? aiMap[iMap++] : 0;
            pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
          }
          sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
          if( i==iEq ){
            pIn->iCur = iTab;
            pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
            if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
              pIn->iBase = iReg - i;
              pIn->nPrefix = i;
              pLoop->wsFlags |= WHERE_IN_EARLYOUT;
            }else{
              pIn->nPrefix = 0;
            }
          }else{







|







588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
            int iCol = aiMap ? aiMap[iMap++] : 0;
            pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
          }
          sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
          if( i==iEq ){
            pIn->iCur = iTab;
            pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
            if( iEq>0 && (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
              pIn->iBase = iReg - i;
              pIn->nPrefix = i;
              pLoop->wsFlags |= WHERE_IN_EARLYOUT;
            }else{
              pIn->nPrefix = 0;
            }
          }else{
Added test/in6.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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# 2018-06-07
#
# 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.
#
#***********************************************************************
#
# A multi-key index that uses an IN operator on one of the keys other
# than the left-most key is able to abort the IN-operator loop early
# if key terms further to the left do not match.
#
# Call this the "multikey-IN-operator early-out optimization" or
# just "IN-early-out" optimization for short.
#

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

do_test in6-1.1 {
  db eval {
    CREATE TABLE t1(a,b,c,d);
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
      INSERT INTO t1(a,b,c,d)
        SELECT 100, 200+x/2, 300+x/5, x FROM c;
    CREATE INDEX t1abc ON t1(a,b,c);
  }
  set ::sqlite_search_count 0
  db eval {
    SELECT d FROM t1
     WHERE a=99
       AND b IN (200,205,201,204)
       AND c IN (304,302,309,308);
  }
} {}
do_test in6-1.2 {
  set ::sqlite_search_count
} {0}  ;# Without the IN-early-out optimization, this value would be 15

# The multikey-IN-operator early-out optimization does not apply
# when the IN operator is on the left-most column of the index.
#
do_test in6-1.3 {
  db eval {
    EXPLAIN
    SELECT d FROM t1
      WHERE a IN (98,99,100,101)
        AND b=200 AND c=300;
  }
} {~/(IfNoHope|SeekHit)/}

set sqlite_search_count 0
do_execsql_test in6-1.4 {
 SELECT d FROM t1
  WHERE a=100
    AND b IN (200,201,202,204)
    AND c IN (300,302,301,305)
  ORDER BY +d;
} {1 2 3 4 5 8 9}
do_test in6-1.5 {
  set ::sqlite_search_count
} {39}

do_execsql_test in6-2.1 {
  CREATE TABLE t2(e INT UNIQUE, f TEXT);
  SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d)
  WHERE a=100
    AND b IN (200,201,202,204)
    AND c IN (300,302,301,305)
  ORDER BY +d;
} {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}}

finish_test
Changes to test/where.test.
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
      SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
    }
  } {2 1 9 3 1 16 6}
  do_test where-5.14 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
    }
  } {2 1 9 5}
  do_test where-5.15 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
    }
  } {2 1 9 3 1 16 9}
  do_test where-5.100 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x, y
    }
  } {2 1 9 54 5 3025 62 5 3969}
  do_test where-5.101 {







|




|







486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
      SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
    }
  } {2 1 9 3 1 16 6}
  do_test where-5.14 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
    }
  } {2 1 9 4}
  do_test where-5.15 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
    }
  } {2 1 9 3 1 16 8}
  do_test where-5.100 {
    db eval {
      SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
       ORDER BY x, y
    }
  } {2 1 9 54 5 3025 62 5 3969}
  do_test where-5.101 {