Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Continue to support the (broken) legacy syntax of allowing strings for column names in CREATE INDEX statements and in UNIQUE and PRIMARY KEY constraints. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3d3df79bfaf9dbc7aa711c08a19d2f6d |
User & Date: | drh 2015-09-04 12:54:01.394 |
Context
2015-09-04
| ||
17:32 | Add support for CREATE INDEX statements that use deterministic expressions rather than only column names. (check-in: 2131a5ca53 user: drh tags: trunk) | |
17:22 | Merge the latest trunk changes, and especially the fix for allowing strings as identifiers in CREATE INDEX statements. (check-in: a9b84885aa user: drh tags: begin-concurrent) | |
13:02 | Merge trunk enhancements, and espeically the fix for allowing strings as column identifers in CREATE INDEX statements. (Closed-Leaf check-in: 5ff8552938 user: drh tags: index-expr) | |
12:54 | Continue to support the (broken) legacy syntax of allowing strings for column names in CREATE INDEX statements and in UNIQUE and PRIMARY KEY constraints. (check-in: 3d3df79bfa user: drh tags: trunk) | |
11:13 | Enhance showfts5.tcl so that it can optionally display the number of terms in each segment. (check-in: d648ddd93d user: dan tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 | p->aSortOrder = (u8*)pExtra; p->nColumn = nCol; p->nKeyCol = nCol - 1; *ppExtra = ((char*)p) + nByte; } return p; } /* ** Create a new index for an SQL table. pName1.pName2 is the name of the index ** and pTblList is the name of the table that is to be indexed. Both will ** be NULL for a primary key or an index that is created to satisfy a ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is | > > > > > > > > > > > > > > > > > > > > > > > > | 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 | p->aSortOrder = (u8*)pExtra; p->nColumn = nCol; p->nKeyCol = nCol - 1; *ppExtra = ((char*)p) + nByte; } return p; } /* ** Backwards Compatibility Hack: ** ** Historical versions of SQLite accepted strings as column names in ** indexes and PRIMARY KEY constraints and in UNIQUE constraints. Example: ** ** CREATE TABLE xyz(a,b,c,d,e,PRIMARY KEY('a'),UNIQUE('b','c' COLLATE trim) ** CREATE INDEX abc ON xyz('c','d' DESC,'e' COLLATE nocase DESC); ** ** This is goofy. But to preserve backwards compatibility we continue to ** accept it. This routine does the necessary conversion. It converts ** the expression given in its argument from a TK_STRING into a TK_ID ** if the expression is just a TK_STRING with an optional COLLATE clause. ** If the epxression is anything other than TK_STRING, the expression is ** unchanged. */ static void sqlite3StringToId(Expr *p){ if( p->op==TK_STRING ){ p->op = TK_ID; }else if( p->op==TK_COLLATE && p->pLeft->op==TK_STRING ){ p->pLeft->op = TK_ID; } } /* ** Create a new index for an SQL table. pName1.pName2 is the name of the index ** and pTblList is the name of the table that is to be indexed. Both will ** be NULL for a primary key or an index that is created to satisfy a ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is |
︙ | ︙ | |||
3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 | */ for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){ const char *zColName; Expr *pCExpr; int requestedSortOrder; char *zColl; /* Collation sequence name */ pCExpr = sqlite3ExprSkipCollate(pListItem->pExpr); if( pCExpr->op!=TK_ID ){ sqlite3ErrorMsg(pParse, "indexes on expressions not yet supported"); continue; } zColName = pCExpr->u.zToken; for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){ | > | 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 | */ for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){ const char *zColName; Expr *pCExpr; int requestedSortOrder; char *zColl; /* Collation sequence name */ sqlite3StringToId(pListItem->pExpr); pCExpr = sqlite3ExprSkipCollate(pListItem->pExpr); if( pCExpr->op!=TK_ID ){ sqlite3ErrorMsg(pParse, "indexes on expressions not yet supported"); continue; } zColName = pCExpr->u.zToken; for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){ |
︙ | ︙ |
Changes to test/index2.test.
|
| | < | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 2005-01-11 # # 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. The # focus of this file is testing the CREATE INDEX statement. # set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a large number of columns # do_test index2-1.1 { |
︙ | ︙ |
Changes to test/index3.test.
|
| | < | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 2005-02-14 # # 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. The # focus of this file is testing the CREATE INDEX statement. # set testdir [file dirname $argv0] source $testdir/tester.tcl # Ticket #1115. Make sure that when a UNIQUE index is created on a # non-unique column (or columns) that it fails and that it leaves no |
︙ | ︙ | |||
35 36 37 38 39 40 41 42 43 44 45 46 47 48 | CREATE UNIQUE INDEX i1 ON t1(a); } } {1 {UNIQUE constraint failed: t1.a}} do_test index3-1.3 { catchsql COMMIT; } {0 {}} integrity_check index3-1.4 # This test corrupts the database file so it must be the last test # in the series. # do_test index3-99.1 { execsql { PRAGMA writable_schema=on; | > > > > > > > > > > > > > > > > > > > > > > > > > | | | | 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 | CREATE UNIQUE INDEX i1 ON t1(a); } } {1 {UNIQUE constraint failed: t1.a}} do_test index3-1.3 { catchsql COMMIT; } {0 {}} integrity_check index3-1.4 # Backwards compatibility test: # # Verify that CREATE INDEX statements that use strings instead of # identifiers for the the column names continue to work correctly. # This is undocumented behavior retained for backwards compatiblity. # do_execsql_test index3-2.1 { DROP TABLE t1; CREATE TABLE t1(a, b, c, d, e, PRIMARY KEY('a'), UNIQUE('b' COLLATE nocase DESC)); CREATE INDEX t1c ON t1('c'); CREATE INDEX t1d ON t1('d' COLLATE binary ASC); WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30) INSERT INTO t1(a,b,c,d,e) SELECT x, printf('ab%03xxy',x), x, x, x FROM c; } {} do_execsql_test index3-2.2 { SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase; } {5} do_execsql_test index3-2.2eqp { EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase; } {/USING INDEX/} # This test corrupts the database file so it must be the last test # in the series. # do_test index3-99.1 { execsql { PRAGMA writable_schema=on; UPDATE sqlite_master SET sql='nonsense' WHERE name='t1d' } db close catch { sqlite3 db test.db } catchsql { DROP INDEX t1c } } {1 {malformed database schema (t1d)}} finish_test |
Changes to test/where.test.
︙ | ︙ | |||
38 39 40 41 42 43 44 | set maxy [execsql {select max(y) from t1}] execsql " INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1; " } execsql { | | | | 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | set maxy [execsql {select max(y) from t1}] execsql " INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1; " } execsql { CREATE INDEX i1w ON t1("w"); -- Verify quoted identifier names CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility CREATE INDEX i2p ON t2(p); CREATE INDEX i2r ON t2(r); CREATE INDEX i2qs ON t2(q, s); } } {} # Do an SQL statement. Append the search count to the end of the result. |
︙ | ︙ |
Changes to test/where4.test.
︙ | ︙ | |||
132 133 134 135 136 137 138 | # do_test where4-3.1 { execsql { CREATE TABLE t2(a); INSERT INTO t2 VALUES(1); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(3); | | | 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | # do_test where4-3.1 { execsql { CREATE TABLE t2(a); INSERT INTO t2 VALUES(1); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(3); CREATE TABLE t3(x,y,UNIQUE("x",'y' ASC)); -- Goofy syntax allowed INSERT INTO t3 VALUES(1,11); INSERT INTO t3 VALUES(2,NULL); SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL; } } {2 2 {} 3 {} {}} do_test where4-3.2 { |
︙ | ︙ | |||
196 197 198 199 200 201 202 | } {} # Ticket #2273. Problems with IN operators and NULLs. # ifcapable subquery { do_test where4-5.1 { execsql { | > | | 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 | } {} # Ticket #2273. Problems with IN operators and NULLs. # ifcapable subquery { do_test where4-5.1 { execsql { -- Allow the 'x' syntax for backwards compatibility CREATE TABLE t4(x,y,z,PRIMARY KEY('x' ASC, "y" ASC)); } execsql { SELECT * FROM t2 LEFT JOIN t4 b1 LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y); } } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}} |
︙ | ︙ | |||
300 301 302 303 304 305 306 | do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2} do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2} finish_test | < | 301 302 303 304 305 306 307 | do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2} do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2} finish_test |