/ Check-in [6dae62c4]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:When loading a database schema that contains an index definition that includes a COLLATE clause for which the collation sequence is unavailable, do not assume that that index uses BINARY instead. Fix for [0fc59f908b].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6dae62c4e505a9a1a22c2771ef3e1921407c4748
User & Date: dan 2013-05-15 15:16:50
References
2013-05-15
16:24
Extra test for commit [6dae62c4e5]. check-in: b10b9e75 user: dan tags: trunk
Context
2013-05-15
16:16
Add main.c to the list of files compiled with SQLITE_TEST defined when building testfixture. check-in: a58af814 user: dan tags: trunk
15:42
Merge latest trunk changes with this branch. check-in: 47dd65a8 user: dan tags: shared-cache-fixes
15:16
When loading a database schema that contains an index definition that includes a COLLATE clause for which the collation sequence is unavailable, do not assume that that index uses BINARY instead. Fix for [0fc59f908b]. check-in: 6dae62c4 user: dan tags: trunk
13:05
Add the rot13.c loadable extension. check-in: 8f9bd8e7 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
....
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743


2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761

  /* Figure out how many bytes of space are required to store explicitly
  ** specified collation sequence names.
  */
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
    if( pExpr ){
      CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
      if( pColl ){
        nExtra += (1 + sqlite3Strlen30(pColl->zName));
      }
    }
  }

  /* 
  ** Allocate the index structure. 
  */
  nName = sqlite3Strlen30(zName);
................................................................................
  ** same column more than once cannot be an error because that would 
  ** break backwards compatibility - it needs to be a warning.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;
    Column *pTabCol;
    int requestedSortOrder;
    CollSeq *pColl;                /* Collating sequence */
    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqlite3ErrorMsg(pParse, "table %s has no column named %s",
        pTab->zName, zColName);
      pParse->checkSchema = 1;
      goto exit_create_index;
    }
    pIndex->aiColumn[i] = j;
    if( pListItem->pExpr
     && (pColl = sqlite3ExprCollSeq(pParse, pListItem->pExpr))!=0
    ){
      int nColl;
      zColl = pColl->zName;


      nColl = sqlite3Strlen30(zColl) + 1;
      assert( nExtra>=nColl );
      memcpy(zExtra, zColl, nColl);
      zColl = zExtra;
      zExtra += nColl;
      nExtra -= nColl;
    }else{
      zColl = pTab->aCol[j].zColl;
      if( !zColl ){
        zColl = "BINARY";
      }
    }
    if( !db->init.busy && !sqlite3LocateCollSeq(pParse, zColl) ){
      goto exit_create_index;
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;







|
<
|
<







 







<












|
<
<

<
>
>








<
|
<







2655
2656
2657
2658
2659
2660
2661
2662

2663

2664
2665
2666
2667
2668
2669
2670
....
2717
2718
2719
2720
2721
2722
2723

2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736


2737

2738
2739
2740
2741
2742
2743
2744
2745
2746
2747

2748

2749
2750
2751
2752
2753
2754
2755

  /* Figure out how many bytes of space are required to store explicitly
  ** specified collation sequence names.
  */
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
    if( pExpr ){
      assert( pExpr->op==TK_COLLATE );

      nExtra += (1 + sqlite3Strlen30(pExpr->u.zToken));

    }
  }

  /* 
  ** Allocate the index structure. 
  */
  nName = sqlite3Strlen30(zName);
................................................................................
  ** same column more than once cannot be an error because that would 
  ** break backwards compatibility - it needs to be a warning.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;
    Column *pTabCol;
    int requestedSortOrder;

    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqlite3ErrorMsg(pParse, "table %s has no column named %s",
        pTab->zName, zColName);
      pParse->checkSchema = 1;
      goto exit_create_index;
    }
    pIndex->aiColumn[i] = j;
    if( pListItem->pExpr ){


      int nColl;

      assert( pListItem->pExpr->op==TK_COLLATE );
      zColl = pListItem->pExpr->u.zToken;
      nColl = sqlite3Strlen30(zColl) + 1;
      assert( nExtra>=nColl );
      memcpy(zExtra, zColl, nColl);
      zColl = zExtra;
      zExtra += nColl;
      nExtra -= nColl;
    }else{
      zColl = pTab->aCol[j].zColl;

      if( !zColl ) zColl = "BINARY";

    }
    if( !db->init.busy && !sqlite3LocateCollSeq(pParse, zColl) ){
      goto exit_create_index;
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;

Changes to test/collate3.test.

50
51
52
53
54
55
56


























































































57
58
59
60
61
62
63
    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
  }
} {1 {no such collation sequence: garbage}}

execsql {
  DROP TABLE collate3t1;
}



























































































#
# Create a table with a default collation sequence, then close
# and re-open the database without re-registering the collation
# sequence. Then make sure the library stops us from using
# the collation sequence in:
# * an explicitly collated ORDER BY







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







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
    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
  }
} {1 {no such collation sequence: garbage}}

execsql {
  DROP TABLE collate3t1;
}

proc caseless {a b} { string compare -nocase $a $b }
do_test collate3-1.4 {
  db collate caseless caseless
  execsql { 
    CREATE TABLE t1(a COLLATE caseless); 
    INSERT INTO t1 VALUES('Abc2');
    INSERT INTO t1 VALUES('abc1');
    INSERT INTO t1 VALUES('aBc3');
  }
  execsql { SELECT * FROM t1 ORDER BY a }
} {abc1 Abc2 aBc3}

do_test collate3-1.5 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.1 {
  db collate caseless caseless
  execsql { CREATE INDEX i1 ON t1(a) }
  execsql { SELECT * FROM t1 ORDER BY a }
} {abc1 Abc2 aBc3}

do_test collate3-1.6.2 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.3 {
  db close
  sqlite3 db test.db
  catchsql { PRAGMA integrity_check }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.1 {
  db collate caseless caseless
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a);
    CREATE INDEX i1 ON t1(a COLLATE caseless);
    INSERT INTO t1 VALUES('Abc2');
    INSERT INTO t1 VALUES('abc1');
    INSERT INTO t1 VALUES('aBc3');
    SELECT * FROM t1 ORDER BY a COLLATE caseless;
  }
} {abc1 Abc2 aBc3}

do_test collate3-1.7.2 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a COLLATE caseless}
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.3 {
  db close
  sqlite3 db test.db
  catchsql { PRAGMA integrity_check }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.5 {
  db close
  sqlite3 db test.db
  db collate caseless caseless
  catchsql { PRAGMA integrity_check }
} {0 ok}

do_test collate3-1.7.6 {
  execsql { DROP TABLE t1 }
} {}

#
# Create a table with a default collation sequence, then close
# and re-open the database without re-registering the collation
# sequence. Then make sure the library stops us from using
# the collation sequence in:
# * an explicitly collated ORDER BY