Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -2845,10 +2845,34 @@ 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 @@ -3116,10 +3140,11 @@ 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; } Index: test/index2.test ================================================================== --- test/index2.test +++ test/index2.test @@ -1,6 +1,6 @@ -# 2005 January 11 +# 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. @@ -9,11 +9,10 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE INDEX statement. # -# $Id: index2.test,v 1.3 2006/03/03 19:12:30 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a large number of columns Index: test/index3.test ================================================================== --- test/index3.test +++ test/index3.test @@ -1,6 +1,6 @@ -# 2005 February 14 +# 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. @@ -9,11 +9,10 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the CREATE INDEX statement. # -# $Id: index3.test,v 1.3 2008/03/19 13:03:34 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -37,20 +36,45 @@ } {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'; + UPDATE sqlite_master SET sql='nonsense' WHERE name='t1d' } db close catch { sqlite3 db test.db } - catchsql { DROP INDEX i1 } -} {1 {malformed database schema (t1)}} + catchsql { DROP INDEX t1c } +} {1 {malformed database schema (t1d)}} finish_test Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -40,12 +40,12 @@ INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1; " } execsql { - CREATE INDEX i1w ON t1(w); - CREATE INDEX i1xy ON t1(x,y); + 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); } } {} Index: test/where4.test ================================================================== --- test/where4.test +++ test/where4.test @@ -134,11 +134,11 @@ 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)); + 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; } @@ -198,11 +198,12 @@ # Ticket #2273. Problems with IN operators and NULLs. # ifcapable subquery { do_test where4-5.1 { execsql { - CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y)); + -- 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); @@ -302,6 +303,5 @@ finish_test -