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 | branch-3.8.7 |
| Files: | files | file ages | folders |
| SHA1: |
98457a57d642b35917eb9ad8f70065e2 |
| User & Date: | drh 2014-11-14 15:42:23 |
Context
|
2014-11-17
| ||
| 15:22 | Update a couple of test cases to account for the fact that ROLLBACK does not always abort all running SELECT statements. (check-in: eba171e9 user: dan tags: branch-3.8.7) | |
|
2014-11-14
| ||
| 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
| ||
| 13:42 | When a transaction or savepoint rollback occurs, save the positions of all open read-cursors so that they can be restored following the rollback operation. Cherry-pick of check-in [dd03a2802f3f27] (check-in: 402780a9 user: drh tags: branch-3.8.7) | |
Changes
Changes to src/expr.c.
1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 |
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() */
|
< |
1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 |
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 |