/ Check-in [3d3df79b]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3d3df79bfaf9dbc7aa711c08a19d2f6dbe744b32
User & Date: drh 2015-09-04 12:54:01
Context
2015-09-04
17:32
Add support for CREATE INDEX statements that use deterministic expressions rather than only column names. check-in: 2131a5ca 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: a9b84885 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: 5ff85529 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: 3d3df79b user: drh tags: trunk
11:13
Enhance showfts5.tcl so that it can optionally display the number of terms in each segment. check-in: d648ddd9 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  2843   2843       p->aSortOrder = (u8*)pExtra;
  2844   2844       p->nColumn = nCol;
  2845   2845       p->nKeyCol = nCol - 1;
  2846   2846       *ppExtra = ((char*)p) + nByte;
  2847   2847     }
  2848   2848     return p;
  2849   2849   }
         2850  +
         2851  +/*
         2852  +** Backwards Compatibility Hack:
         2853  +** 
         2854  +** Historical versions of SQLite accepted strings as column names in
         2855  +** indexes and PRIMARY KEY constraints and in UNIQUE constraints.  Example:
         2856  +**
         2857  +**     CREATE TABLE xyz(a,b,c,d,e,PRIMARY KEY('a'),UNIQUE('b','c' COLLATE trim)
         2858  +**     CREATE INDEX abc ON xyz('c','d' DESC,'e' COLLATE nocase DESC);
         2859  +**
         2860  +** This is goofy.  But to preserve backwards compatibility we continue to
         2861  +** accept it.  This routine does the necessary conversion.  It converts
         2862  +** the expression given in its argument from a TK_STRING into a TK_ID
         2863  +** if the expression is just a TK_STRING with an optional COLLATE clause.
         2864  +** If the epxression is anything other than TK_STRING, the expression is
         2865  +** unchanged.
         2866  +*/
         2867  +static void sqlite3StringToId(Expr *p){
         2868  +  if( p->op==TK_STRING ){
         2869  +    p->op = TK_ID;
         2870  +  }else if( p->op==TK_COLLATE && p->pLeft->op==TK_STRING ){
         2871  +    p->pLeft->op = TK_ID;
         2872  +  }
         2873  +}
  2850   2874   
  2851   2875   /*
  2852   2876   ** Create a new index for an SQL table.  pName1.pName2 is the name of the index 
  2853   2877   ** and pTblList is the name of the table that is to be indexed.  Both will 
  2854   2878   ** be NULL for a primary key or an index that is created to satisfy a
  2855   2879   ** UNIQUE constraint.  If pTable and pIndex are NULL, use pParse->pNewTable
  2856   2880   ** as the table to be indexed.  pParse->pNewTable is a table that is
................................................................................
  3114   3138     */
  3115   3139     for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
  3116   3140       const char *zColName;
  3117   3141       Expr *pCExpr;
  3118   3142       int requestedSortOrder;
  3119   3143       char *zColl;                   /* Collation sequence name */
  3120   3144   
         3145  +    sqlite3StringToId(pListItem->pExpr);
  3121   3146       pCExpr = sqlite3ExprSkipCollate(pListItem->pExpr);
  3122   3147       if( pCExpr->op!=TK_ID ){
  3123   3148         sqlite3ErrorMsg(pParse, "indexes on expressions not yet supported");
  3124   3149         continue;
  3125   3150       }
  3126   3151       zColName = pCExpr->u.zToken;
  3127   3152       for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){

Changes to test/index2.test.

     1         -# 2005 January 11
            1  +# 2005-01-11
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE INDEX statement.
    13     13   #
    14         -# $Id: index2.test,v 1.3 2006/03/03 19:12:30 drh Exp $
    15     14   
    16     15   set testdir [file dirname $argv0]
    17     16   source $testdir/tester.tcl
    18     17   
    19     18   # Create a table with a large number of columns
    20     19   #
    21     20   do_test index2-1.1 {

Changes to test/index3.test.

     1         -# 2005 February 14
            1  +# 2005-02-14
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the CREATE INDEX statement.
    13     13   #
    14         -# $Id: index3.test,v 1.3 2008/03/19 13:03:34 drh Exp $
    15     14   
    16     15   
    17     16   set testdir [file dirname $argv0]
    18     17   source $testdir/tester.tcl
    19     18   
    20     19   # Ticket #1115.  Make sure that when a UNIQUE index is created on a
    21     20   # non-unique column (or columns) that it fails and that it leaves no
................................................................................
    35     34       CREATE UNIQUE INDEX i1 ON t1(a);
    36     35     }
    37     36   } {1 {UNIQUE constraint failed: t1.a}}
    38     37   do_test index3-1.3 {
    39     38     catchsql COMMIT;
    40     39   } {0 {}}
    41     40   integrity_check index3-1.4
           41  +
           42  +# Backwards compatibility test:
           43  +#
           44  +# Verify that CREATE INDEX statements that use strings instead of 
           45  +# identifiers for the the column names continue to work correctly.
           46  +# This is undocumented behavior retained for backwards compatiblity.
           47  +#
           48  +do_execsql_test index3-2.1 {
           49  +  DROP TABLE t1;
           50  +  CREATE TABLE t1(a, b, c, d, e, 
           51  +                  PRIMARY KEY('a'), UNIQUE('b' COLLATE nocase DESC));
           52  +  CREATE INDEX t1c ON t1('c');
           53  +  CREATE INDEX t1d ON t1('d' COLLATE binary ASC);
           54  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<30)
           55  +    INSERT INTO t1(a,b,c,d,e) 
           56  +      SELECT x, printf('ab%03xxy',x), x, x, x FROM c;
           57  +} {}
           58  +do_execsql_test index3-2.2 {
           59  +  SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase;
           60  +} {5}
           61  +do_execsql_test index3-2.2eqp {
           62  +  EXPLAIN QUERY PLAN
           63  +  SELECT a FROM t1 WHERE b='ab005xy' COLLATE nocase;
           64  +} {/USING INDEX/}
           65  +
    42     66   
    43     67   # This test corrupts the database file so it must be the last test
    44     68   # in the series.
    45     69   #
    46     70   do_test index3-99.1 {
    47     71     execsql {
    48     72       PRAGMA writable_schema=on;
    49         -    UPDATE sqlite_master SET sql='nonsense';
           73  +    UPDATE sqlite_master SET sql='nonsense' WHERE name='t1d'
    50     74     }
    51     75     db close
    52     76     catch { sqlite3 db test.db }
    53         -  catchsql { DROP INDEX i1 }
    54         -} {1 {malformed database schema (t1)}}
           77  +  catchsql { DROP INDEX t1c }
           78  +} {1 {malformed database schema (t1d)}}
    55     79   
    56     80   finish_test

Changes to test/where.test.

    38     38       set maxy [execsql {select max(y) from t1}]
    39     39       execsql "
    40     40         INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
    41     41       "
    42     42     }
    43     43   
    44     44     execsql {
    45         -    CREATE INDEX i1w ON t1(w);
    46         -    CREATE INDEX i1xy ON t1(x,y);
           45  +    CREATE INDEX i1w ON t1("w");  -- Verify quoted identifier names
           46  +    CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility
    47     47       CREATE INDEX i2p ON t2(p);
    48     48       CREATE INDEX i2r ON t2(r);
    49     49       CREATE INDEX i2qs ON t2(q, s);
    50     50     }
    51     51   } {}
    52     52   
    53     53   # Do an SQL statement.  Append the search count to the end of the result.

Changes to test/where4.test.

   132    132   #
   133    133   do_test where4-3.1 {
   134    134     execsql {
   135    135       CREATE TABLE t2(a);
   136    136       INSERT INTO t2 VALUES(1);
   137    137       INSERT INTO t2 VALUES(2);
   138    138       INSERT INTO t2 VALUES(3);
   139         -    CREATE TABLE t3(x,y,UNIQUE(x,y));
          139  +    CREATE TABLE t3(x,y,UNIQUE("x",'y' ASC)); -- Goofy syntax allowed
   140    140       INSERT INTO t3 VALUES(1,11);
   141    141       INSERT INTO t3 VALUES(2,NULL);
   142    142    
   143    143       SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
   144    144     }
   145    145   } {2 2 {} 3 {} {}}
   146    146   do_test where4-3.2 {
................................................................................
   196    196   } {}
   197    197   
   198    198   # Ticket #2273.  Problems with IN operators and NULLs.
   199    199   #
   200    200   ifcapable subquery {
   201    201   do_test where4-5.1 {
   202    202     execsql {
   203         -    CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y));
          203  +    -- Allow the 'x' syntax for backwards compatibility
          204  +    CREATE TABLE t4(x,y,z,PRIMARY KEY('x' ASC, "y" ASC));
   204    205     }
   205    206     execsql {
   206    207       SELECT *
   207    208         FROM t2 LEFT JOIN t4 b1
   208    209                 LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
   209    210     }
   210    211   } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
................................................................................
   300    301   do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL  } {{} 1 {} 2}
   301    302   do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2}
   302    303   
   303    304   
   304    305   
   305    306   
   306    307   finish_test
   307         -