Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure affinities and implicit collation sequences are correctly used by comparison expressions in the select-list or having clause of an aggregate query. Ticket #3493. (CVS 5889) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
b8ceaa00500f29bbbd0cc5aec71a6ffa |
User & Date: | danielk1977 2008-11-12 08:07:12.000 |
Context
2008-11-12
| ||
08:49 | Fix a database corruption related crash uncovered by corruptC.test. (CVS 5890) (check-in: 395084acbb user: danielk1977 tags: trunk) | |
08:07 | Make sure affinities and implicit collation sequences are correctly used by comparison expressions in the select-list or having clause of an aggregate query. Ticket #3493. (CVS 5889) (check-in: b8ceaa0050 user: danielk1977 tags: trunk) | |
04:55 | Fixed a few more crashes when dealing with corrupt db files. (CVS 5888) (check-in: f8bb34e409 user: shane tags: trunk) | |
Changes
Changes to src/expr.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 routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions 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 routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** ** $Id: expr.c,v 1.402 2008/11/12 08:07:12 danielk1977 Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** Return the 'affinity' of the expression pExpr if any. ** |
︙ | ︙ | |||
39 40 41 42 43 44 45 | return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr); } #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ return sqlite3AffinityType(&pExpr->token); } #endif | > | > | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr); } #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ return sqlite3AffinityType(&pExpr->token); } #endif if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) && pExpr->pTab!=0 ){ /* op==TK_REGISTER && pExpr->pTab!=0 happens when pExpr was originally ** a TK_COLUMN but was previously evaluated and cached in a register */ int j = pExpr->iColumn; if( j<0 ) return SQLITE_AFF_INTEGER; assert( pExpr->pTab && j<pExpr->pTab->nCol ); return pExpr->pTab->aCol[j].affinity; } |
︙ | ︙ | |||
85 86 87 88 89 90 91 | CollSeq *pColl = 0; Expr *p = pExpr; while( p ){ int op; pColl = p->pColl; if( pColl ) break; op = p->op; | | | 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | CollSeq *pColl = 0; Expr *p = pExpr; while( p ){ int op; pColl = p->pColl; if( pColl ) break; op = p->op; if( (op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_REGISTER) && p->pTab!=0 ){ /* op==TK_REGISTER && p->pTab!=0 happens when pExpr was originally ** a TK_COLUMN but was previously evaluated and cached in a register */ const char *zColl; int j = p->iColumn; if( j>=0 ){ sqlite3 *db = pParse->db; zColl = p->pTab->aCol[j].zColl; |
︙ | ︙ |
Added test/tkt3493.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 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 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | # 2008 October 13 # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. Specifically, # it tests that affinities and collation sequences are correctly applied # in aggregate queries. # # $Id: tkt3493.test,v 1.1 2008/11/12 08:07:12 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt3493-1.1 { execsql { BEGIN; CREATE TABLE A (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT); INSERT INTO A VALUES(1,'123'); INSERT INTO A VALUES(2,'456'); CREATE TABLE B (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT); INSERT INTO B VALUES(1,1); INSERT INTO B VALUES(2,2); CREATE TABLE A_B (B_id INTEGER NOT NULL, A_id INTEGER); INSERT INTO A_B VALUES(1,1); INSERT INTO A_B VALUES(2,2); COMMIT; } } {} do_test tkt3493-1.2 { execsql { SELECT CASE WHEN B.val = 1 THEN 'XYZ' ELSE A.val END AS Col1 FROM B LEFT OUTER JOIN A_B ON B.id = A_B.B_id LEFT OUTER JOIN A ON A.id = A_B.A_id ORDER BY Col1 ASC; } } {456 XYZ} do_test tkt3493-1.3 { execsql { SELECT DISTINCT CASE WHEN B.val = 1 THEN 'XYZ' ELSE A.val END AS Col1 FROM B LEFT OUTER JOIN A_B ON B.id = A_B.B_id LEFT OUTER JOIN A ON A.id = A_B.A_id ORDER BY Col1 ASC; } } {456 XYZ} do_test tkt3493-1.4 { execsql { SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b; } } {1 xyz 2 2} do_test tkt3493-1.5 { execsql { SELECT DISTINCT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b; } } {1 xyz 2 2} do_test tkt3493-1.6 { execsql { SELECT DISTINCT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b; } } {1 xyz 2 2} do_test tkt3493-2.1 { execsql { CREATE TABLE t1(a TEXT, b INT); INSERT INTO t1 VALUES(123, 456); } } {} do_test tkt3493-2.2.1 { execsql { SELECT a=123 FROM t1 GROUP BY a } } {1} do_test tkt3493-2.2.2 { execsql { SELECT a=123 FROM t1 } } {1} do_test tkt3493-2.2.3 { execsql { SELECT a='123' FROM t1 } } {1} do_test tkt3493-2.2.4 { execsql { SELECT count(*), a=123 FROM t1 } } {1 1} do_test tkt3493-2.2.5 { execsql { SELECT count(*), +a=123 FROM t1 } } {1 0} do_test tkt3493-2.3.3 { execsql { SELECT b='456' FROM t1 GROUP BY a } } {1} do_test tkt3493-2.3.1 { execsql { SELECT b='456' FROM t1 GROUP BY b } } {1} do_test tkt3493-2.3.2 { execsql { SELECT b='456' FROM t1 } } {1} do_test tkt3493-2.4.1 { execsql { SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123 } } {text 123} do_test tkt3493-2.4.2 { execsql { SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123 } } {text 123} do_test tkt3493-2.5.1 { execsql { SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456' } } {integer 456} do_test tkt3493-2.5.2 { execsql { SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456' } } {integer 456} do_test tkt3493-3.1 { execsql { CREATE TABLE t2(a COLLATE NOCASE, b COLLATE BINARY); INSERT INTO t2 VALUES('aBc', 'DeF'); } } {} do_test tkt3493-3.2.1 { execsql { SELECT a='abc' FROM t2 GROUP BY a } } {1} do_test tkt3493-3.2.2 { execsql { SELECT a='abc' FROM t2 } } {1} do_test tkt3493-3.3.1 { execsql { SELECT a>b FROM t2 GROUP BY a, b} } {0} do_test tkt3493-3.3.2 { execsql { SELECT a>b COLLATE BINARY FROM t2 GROUP BY a, b} } {1} do_test tkt3493-3.3.3 { execsql { SELECT b>a FROM t2 GROUP BY a, b} } {0} do_test tkt3493-3.3.4 { execsql { SELECT b>a COLLATE NOCASE FROM t2 GROUP BY a, b} } {1} finish_test |