Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Get the column affinities right when processing an IN operator where the right-hand side is a subquery with an ORDER BY clause. Ticket #2295. This bug has likely been present since version 3.0.0 and it has just now been noticed, so it seems to be a obscure case. (CVS 3837) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4062ddf3c7f4fd150292304fa33ca76d |
User & Date: | drh 2007-04-12 03:54:39.000 |
Context
2007-04-12
| ||
21:25 | Changes toward fixes for tickets #2296 and #2291. (CVS 3838) (check-in: 0dd3e2e47b user: drh tags: trunk) | |
03:54 | Get the column affinities right when processing an IN operator where the right-hand side is a subquery with an ORDER BY clause. Ticket #2295. This bug has likely been present since version 3.0.0 and it has just now been noticed, so it seems to be a obscure case. (CVS 3837) (check-in: 4062ddf3c7 user: drh tags: trunk) | |
2007-04-11
| ||
17:54 | Blind check-in of changes to fix #2294 - I have no ability to test. Gentle wince users: please test this change and if it works leave a remark on the #2294 ticket. Tnx. (CVS 3836) (check-in: ca56c3e225 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.334 2007/04/12 03:54:39 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
549 550 551 552 553 554 555 556 557 558 559 560 561 562 | int addr1 = sqlite3VdbeCurrentAddr(v); int addr2; assert( nColumn==1 ); sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3); sqlite3VdbeAddOp(v, OP_Pop, 1, 0); addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); if( pOrderBy ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ pushOntoSorter(pParse, pOrderBy, p); }else{ | > < < | | 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 | int addr1 = sqlite3VdbeCurrentAddr(v); int addr2; assert( nColumn==1 ); sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3); sqlite3VdbeAddOp(v, OP_Pop, 1, 0); addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0); p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr,(iParm>>16)&0xff); if( pOrderBy ){ /* At first glance you would think we could optimize out the ** ORDER BY in this case since the order of entries in the set ** does not matter. But there might be a LIMIT clause, in which ** case the order does matter */ pushOntoSorter(pParse, pOrderBy, p); }else{ sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1); sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0); } sqlite3VdbeJumpHere(v, addr2); break; } /* If any row exist in the result set, record that fact and abort. |
︙ | ︙ | |||
719 720 721 722 723 724 725 | } #ifndef SQLITE_OMIT_SUBQUERY case SRT_Set: { assert( nColumn==1 ); sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp(v, OP_Pop, 1, 0); sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3); | | | 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 | } #ifndef SQLITE_OMIT_SUBQUERY case SRT_Set: { assert( nColumn==1 ); sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp(v, OP_Pop, 1, 0); sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1); sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0); break; } case SRT_Mem: { assert( nColumn==1 ); sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1); /* The LIMIT clause will terminate the loop for us */ |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.551 2007/04/12 03:54:39 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ #if defined(SQLITE_TCL) || defined(TCLSH) # include <tcl.h> #endif |
︙ | ︙ | |||
1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 | ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u8 isDistinct; /* True if the DISTINCT keyword is present */ u8 isResolved; /* True once sqlite3SelectResolve() has run. */ u8 isAgg; /* True if this is an aggregate query */ u8 usesEphm; /* True if uses an OpenEphemeral opcode */ u8 disallowOrderBy; /* Do not allow an ORDER BY to be attached if TRUE */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pRightmost; /* Right-most select in a compound select statement */ | > | 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 | ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ u8 isDistinct; /* True if the DISTINCT keyword is present */ u8 isResolved; /* True once sqlite3SelectResolve() has run. */ u8 isAgg; /* True if this is an aggregate query */ u8 usesEphm; /* True if uses an OpenEphemeral opcode */ u8 disallowOrderBy; /* Do not allow an ORDER BY to be attached if TRUE */ char affinity; /* MakeRecord with this affinity for SRT_Set */ SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pRightmost; /* Right-most select in a compound select statement */ |
︙ | ︙ |
Changes to test/subselect.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that are part of # expressions. # | | | 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing SELECT statements that are part of # expressions. # # $Id: subselect.test,v 1.14 2007/04/12 03:54:39 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Omit this whole file if the library is build without subquery support. ifcapable !subquery { finish_test |
︙ | ︙ | |||
170 171 172 173 174 175 176 177 178 | } {4} do_test subselect-3.10 { execsql { SELECT x FROM t3 WHERE x IN (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2); } } {4} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > | 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 | } {4} do_test subselect-3.10 { execsql { SELECT x FROM t3 WHERE x IN (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2); } } {4} # Ticket #2295. # Make sure type affinities work correctly on subqueries with # an ORDER BY clause. # do_test subselect-4.1 { execsql { CREATE TABLE t4(a TEXT, b TEXT); INSERT INTO t4 VALUES('a','1'); INSERT INTO t4 VALUES('b','2'); INSERT INTO t4 VALUES('c','3'); SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b); } } {a b c} do_test subselect-4.2 { execsql { SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1); } } {a} do_test subselect-4.3 { execsql { SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1); } } {c} finish_test |