SQLite

Check-in [e2ad30c8b5]
Login

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

Overview
Comment:Fix a problem with vector range constraints and mixed ASC/DESC indexes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: e2ad30c8b5366fd8e50f36c62345ed03ec613c47
User & Date: dan 2016-08-02 17:07:51.146
Context
2016-08-02
17:45
Fix SQLITE_OMIT_SUBQUERY builds. (check-in: 339f85f414 user: dan tags: rowvalue)
17:07
Fix a problem with vector range constraints and mixed ASC/DESC indexes. (check-in: e2ad30c8b5 user: dan tags: rowvalue)
16:24
Merge latest trunk changes into this branch. (check-in: d468101b42 user: dan tags: rowvalue)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
2222
2223
2224
2225
2226
2227
2228
2229
2230

2231
2232
2233

2234
2235
2236
2237
2238
2239
2240
    if( pRhs->flags & EP_xIsSelect ){
      pRhs = pRhs->x.pSelect->pEList->a[i].pExpr;
    }else{
      pRhs = pRhs->x.pList->a[i].pExpr;
    }

    /* Check that the LHS of the comparison is a column reference to
    ** the right column of the right source table. 
    */

    if( pLhs->op!=TK_COLUMN 
     || pLhs->iTable!=iCur 
     || pLhs->iColumn!=pIdx->aiColumn[i+nEq] 

    ){
      break;
    }

    aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
    idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
    if( aff!=idxaff ) break;







|
|
>



>







2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
    if( pRhs->flags & EP_xIsSelect ){
      pRhs = pRhs->x.pSelect->pEList->a[i].pExpr;
    }else{
      pRhs = pRhs->x.pList->a[i].pExpr;
    }

    /* Check that the LHS of the comparison is a column reference to
    ** the right column of the right source table. And that the sort
    ** order of the index column is the same as the sort order of the
    ** leftmost index column.  */
    if( pLhs->op!=TK_COLUMN 
     || pLhs->iTable!=iCur 
     || pLhs->iColumn!=pIdx->aiColumn[i+nEq] 
     || pIdx->aSortOrder[i]!=pIdx->aSortOrder[0]
    ){
      break;
    }

    aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
    idxaff = pIdx->pTable->aCol[pLhs->iColumn].affinity;
    if( aff!=idxaff ) break;
Changes to test/rowvalue3.test.
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# where the SELECT statement returns more than one column.
#

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

# Drop all auxiliary indexes from the main database opened by handle [db].
#
proc drop_all_indexes {} {
  set L [db eval {
    SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%'
  }]
  foreach idx $L { db eval "DROP INDEX $idx" }
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}







<
<
<
<
<
<
<
<
<







13
14
15
16
17
18
19









20
21
22
23
24
25
26
# where the SELECT statement returns more than one column.
#

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










do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}
Changes to test/rowvalue4.test.
13
14
15
16
17
18
19







20
21
22
23
24
25
26
27
# and sub-selects that return multiple arguments.
#

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








do_execsql_test 0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX t1bac ON t1(b, a, c);
}

foreach {tn e} {
  1 "(1, 2, 3)"
  2 "1 + (1, 2)"







>
>
>
>
>
>
>
|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# and sub-selects that return multiple arguments.
#

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

#-------------------------------------------------------------------------
# Test some error conditions:
# 
#   * row values used where they are not supported,
#   * row values or sub-selects that contain/return the wrong number
#     of elements.
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX t1bac ON t1(b, a, c);
}

foreach {tn e} {
  1 "(1, 2, 3)"
  2 "1 + (1, 2)"
40
41
42
43
44
45
46























































47
48

  6 "SELECT * FROM t1 WHERE (a, b) IN (SELECT * FROM t1)" 
                             {sub-select returns 3 columns - expected 2}
} {
  do_catchsql_test 2.$tn "$s" [list 1 $error]
}
























































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

  6 "SELECT * FROM t1 WHERE (a, b) IN (SELECT * FROM t1)" 
                             {sub-select returns 3 columns - expected 2}
} {
  do_catchsql_test 2.$tn "$s" [list 1 $error]
}

#-------------------------------------------------------------------------
do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c, d);
  INSERT INTO t2 VALUES(1, 1, 1,   1);
  INSERT INTO t2 VALUES(1, 1, 2,   2);
  INSERT INTO t2 VALUES(1, 1, 3,   3);
  INSERT INTO t2 VALUES(1, 2, 1,   4);
  INSERT INTO t2 VALUES(1, 2, 2,   5);
  INSERT INTO t2 VALUES(1, 2, 3,   6);
  INSERT INTO t2 VALUES(1, 3, 1,   7);
  INSERT INTO t2 VALUES(1, 3, 2,   8);
  INSERT INTO t2 VALUES(1, 3, 3,   9);

  INSERT INTO t2 VALUES(2, 1, 1,   10);
  INSERT INTO t2 VALUES(2, 1, 2,   11);
  INSERT INTO t2 VALUES(2, 1, 3,   12);
  INSERT INTO t2 VALUES(2, 2, 1,   13);
  INSERT INTO t2 VALUES(2, 2, 2,   14);
  INSERT INTO t2 VALUES(2, 2, 3,   15);
  INSERT INTO t2 VALUES(2, 3, 1,   16);
  INSERT INTO t2 VALUES(2, 3, 2,   17);
  INSERT INTO t2 VALUES(2, 3, 3,   18);

  INSERT INTO t2 VALUES(3, 1, 1,   19);
  INSERT INTO t2 VALUES(3, 1, 2,   20);
  INSERT INTO t2 VALUES(3, 1, 3,   21);
  INSERT INTO t2 VALUES(3, 2, 1,   22);
  INSERT INTO t2 VALUES(3, 2, 2,   23);
  INSERT INTO t2 VALUES(3, 2, 3,   24);
  INSERT INTO t2 VALUES(3, 3, 1,   25);
  INSERT INTO t2 VALUES(3, 3, 2,   26);
  INSERT INTO t2 VALUES(3, 3, 3,   27);
}

foreach {nm idx} {
  idx1 {}
  idx2 { CREATE INDEX t2abc ON t2(a, b, c); }
  idx3 { CREATE INDEX t2abc ON t2(a, b DESC, c); }
  idx4 { CREATE INDEX t2abc ON t2(a DESC, b DESC, c DESC); }
  idx5 { CREATE INDEX t2abc ON t2(a ASC, b ASC, c ASC); }
  idx6 { CREATE INDEX t2abc ON t2(a DESC, b, c); }
} {
  drop_all_indexes
  execsql $idx

  foreach {tn where res} {
    1 "(a, b, c) < (2, 2, 2)"  {1 2 3 4 5 6 7 8 9 10 11 12 13}
    2 "(a, b, c) <= (2, 2, 2)" {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
    3 "(a, b, c) > (2, 2, 2)"  {15 16 17 18 19 20 21 22 23 24 25 26 27}
    4 "(a, b, c) >= (2, 2, 2)" {14 15 16 17 18 19 20 21 22 23 24 25 26 27}
  } {
    set result [db eval "SELECT d FROM t2 WHERE $where"]
    do_test 2.$nm.$tn { lsort -integer $result } $res
  }
}
finish_test

Changes to test/tester.tcl.
21
22
23
24
25
26
27

28
29
30
31
32
33
34
#
#      is_relative_file
#      test_pwd
#      get_pwd
#      copy_file              FROM TO
#      delete_file            FILENAME
#      drop_all_tables        ?DB?

#      forcecopy              FROM TO
#      forcedelete            FILENAME
#
# Test the capability of the SQLite version built into the interpreter to
# determine if a specific test can be run:
#
#      capable                EXPR







>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#
#      is_relative_file
#      test_pwd
#      get_pwd
#      copy_file              FROM TO
#      delete_file            FILENAME
#      drop_all_tables        ?DB?
#      drop_all_indexes       ?DB?
#      forcecopy              FROM TO
#      forcedelete            FILENAME
#
# Test the capability of the SQLite version built into the interpreter to
# determine if a specific test can be run:
#
#      capable                EXPR
1946
1947
1948
1949
1950
1951
1952










1953
1954
1955
1956
1957
1958
1959
      $db eval "DROP $type \"$t\""
    }
  }
  ifcapable trigger&&foreignkey {
    $db eval "PRAGMA foreign_keys = $pk"
  }
}











#-------------------------------------------------------------------------
# If a test script is executed with global variable $::G(perm:name) set to
# "wal", then the tests are run in WAL mode. Otherwise, they should be run
# in rollback mode. The following Tcl procs are used to make this less
# intrusive:
#







>
>
>
>
>
>
>
>
>
>







1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
      $db eval "DROP $type \"$t\""
    }
  }
  ifcapable trigger&&foreignkey {
    $db eval "PRAGMA foreign_keys = $pk"
  }
}

# Drop all auxiliary indexes from the main database opened by handle [db].
#
proc drop_all_indexes {{db db}} {
  set L [$db eval {
    SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%'
  }]
  foreach idx $L { $db eval "DROP INDEX $idx" }
}


#-------------------------------------------------------------------------
# If a test script is executed with global variable $::G(perm:name) set to
# "wal", then the tests are run in WAL mode. Otherwise, they should be run
# in rollback mode. The following Tcl procs are used to make this less
# intrusive:
#