SQLite

Check-in [55e453aadb]
Login

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

Overview
Comment:Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 55e453aadbb676dda07f0fa537d39ce184ef636c
User & Date: dan 2014-11-14 15:28:33.929
Context
2014-11-17
19:42
Avoid calling sqlite3BtreeKeysize() on a b-tree cursor in SKIPNEXT or SKIPPREV state. (check-in: 54e7d3fcb1 user: dan tags: trunk)
2014-11-15
19:08
Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm. (Closed-Leaf check-in: 00fe095057 user: drh tags: noskipscan-token)
2014-11-14
19:34
Consider using an automatic-index for a scan even if there exists a possible skip-scan that uses one or more "=" operators. (Closed-Leaf check-in: 93642a65ef user: dan tags: experimental-autoindex-fix)
15:42
Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. (check-in: 98457a57d6 user: drh tags: branch-3.8.7)
15:28
Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. (check-in: 55e453aadb user: dan tags: trunk)
2014-11-13
14:30
Modify the documentation for sqlite3_backup_init() to indicate that it will fail if there is already a read or read-write transaction open on the destination database. (check-in: ef03a20335 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867


        assert( !isRowid );
        sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
        dest.affSdst = (u8)affinity;
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );
        pSelect->iLimit = 0;
        testcase( pSelect->selFlags & SF_Distinct );
        pSelect->selFlags &= ~SF_Distinct;
        testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
        if( sqlite3Select(pParse, pSelect, &dest) ){
          sqlite3KeyInfoUnref(pKeyInfo);
          return 0;
        }
        pEList = pSelect->pEList;
        assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */








<








1853
1854
1855
1856
1857
1858
1859

1860
1861
1862
1863
1864
1865
1866


        assert( !isRowid );
        sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
        dest.affSdst = (u8)affinity;
        assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable );
        pSelect->iLimit = 0;
        testcase( pSelect->selFlags & SF_Distinct );

        testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
        if( sqlite3Select(pParse, pSelect, &dest) ){
          sqlite3KeyInfoUnref(pKeyInfo);
          return 0;
        }
        pEList = pSelect->pEList;
        assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */
︙︙
Changes to test/in5.test.
︙︙
8
9
10
11
12
13
14

15
16
17
18
19
20
21

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

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


do_test in5-1.1 {
  execsql {
    CREATE TABLE t1x(x INTEGER PRIMARY KEY);
    INSERT INTO t1x VALUES(1),(3),(5),(7),(9);
    CREATE TABLE t1y(y INTEGER UNIQUE);
    INSERT INTO t1y VALUES(2),(4),(6),(8);








>








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

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

do_test in5-1.1 {
  execsql {
    CREATE TABLE t1x(x INTEGER PRIMARY KEY);
    INSERT INTO t1x VALUES(1),(3),(5),(7),(9);
    CREATE TABLE t1y(y INTEGER UNIQUE);
    INSERT INTO t1y VALUES(2),(4),(6),(8);
︙︙
130
131
132
133
134
135
136
137















































138

  }
} {23g}
do_test in5-5.3 {
  regexp {OpenEphemeral} [db eval {
    EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
  }]
} {0}
















































finish_test









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


131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

  }
} {23g}
do_test in5-5.3 {
  regexp {OpenEphemeral} [db eval {
    EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z
  }]
} {0}

#-------------------------------------------------------------------------
# At one point SQLite was removing the DISTINCT keyword from expressions
# similar to:
#
#   <expr1> IN (SELECT DISTINCT <expr2> FROM...)
#
# However, there are a few obscure cases where this is incorrect. For
# example, if the SELECT features a LIMIT clause, or if the collation
# sequence or affinity used by the DISTINCT does not match the one used
# by the IN(...) expression.
#
do_execsql_test 6.1.1 {
  CREATE TABLE t1(a COLLATE nocase);
  INSERT INTO t1 VALUES('one');
  INSERT INTO t1 VALUES('ONE');
}
do_execsql_test 6.1.2 {
  SELECT count(*) FROM t1 WHERE a COLLATE BINARY IN (SELECT DISTINCT a FROM t1)
} {1}

do_execsql_test 6.2.1 {
  CREATE TABLE t3(a, b);
  INSERT INTO t3 VALUES(1, 1);
  INSERT INTO t3 VALUES(1, 2);
  INSERT INTO t3 VALUES(1, 3);
  INSERT INTO t3 VALUES(2, 4);
  INSERT INTO t3 VALUES(2, 5);
  INSERT INTO t3 VALUES(2, 6);
  INSERT INTO t3 VALUES(3, 7);
  INSERT INTO t3 VALUES(3, 8);
  INSERT INTO t3 VALUES(3, 9);
}
do_execsql_test 6.2.2 {
  SELECT count(*) FROM t3 WHERE b IN (SELECT DISTINCT a FROM t3 LIMIT 5);
} {3}
do_execsql_test 6.2.3 {
  SELECT count(*) FROM t3 WHERE b IN (SELECT          a FROM t3 LIMIT 5);
} {2}

do_execsql_test 6.3.1 {
  CREATE TABLE x1(a);
  CREATE TABLE x2(b);
  INSERT INTO x1 VALUES(1), (1), (2);
  INSERT INTO x2 VALUES(1), (2);
  SELECT count(*) FROM x2 WHERE b IN (SELECT DISTINCT a FROM x1 LIMIT 2);
} {2}

finish_test