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 | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 55e453aadbb676dda07f0fa537d39ce1 |
| User & Date: | dan 2014-11-14 15:28:33 |
Context
|
2014-11-17
| ||
| 19:42 | Avoid calling sqlite3BtreeKeysize() on a b-tree cursor in SKIPNEXT or SKIPPREV state. check-in: 54e7d3fc 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: 00fe0950 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: 93642a65 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: 98457a57 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: 55e453aa 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: ef03a203 user: dan tags: trunk | |
Changes
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
...
130
131
132
133
134
135
136
137
138
|
# 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);
................................................................................
}
} {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
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
|
# 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); ................................................................................ } } {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 |