Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -52,30 +52,37 @@ assert( pExpr->pTab && jpTab->nCol ); return pExpr->pTab->aCol[j].affinity; } return pExpr->affinity; } + +/* +** Set the explicit collating sequence for an expression to the +** collating sequence supplied in the second argument. +*/ +Expr *sqlite3ExprSetColl(Expr *pExpr, CollSeq *pColl){ + if( pExpr && pColl ){ + pExpr->pColl = pColl; + pExpr->flags |= EP_ExpCollate; + } + return pExpr; +} /* ** Set the collating sequence for expression pExpr to be the collating ** sequence named by pToken. Return a pointer to the revised expression. ** The collating sequence is marked as "explicit" using the EP_ExpCollate ** flag. An explicit collating sequence will override implicit ** collating sequences. */ -Expr *sqlite3ExprSetColl(Parse *pParse, Expr *pExpr, Token *pCollName){ +Expr *sqlite3ExprSetCollByToken(Parse *pParse, Expr *pExpr, Token *pCollName){ char *zColl = 0; /* Dequoted name of collation sequence */ CollSeq *pColl; sqlite3 *db = pParse->db; zColl = sqlite3NameFromToken(db, pCollName); - if( pExpr && zColl ){ - pColl = sqlite3LocateCollSeq(pParse, zColl); - if( pColl ){ - pExpr->pColl = pColl; - pExpr->flags |= EP_ExpCollate; - } - } + pColl = sqlite3LocateCollSeq(pParse, zColl); + sqlite3ExprSetColl(pExpr, pColl); sqlite3DbFree(db, zColl); return pExpr; } /* Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -778,11 +778,11 @@ spanExpr(&A, pParse, TK_VARIABLE, &X); sqlite3ExprAssignVarNumber(pParse, A.pExpr); spanSet(&A, &X, &X); } expr(A) ::= expr(E) COLLATE ids(C). { - A.pExpr = sqlite3ExprSetColl(pParse, E.pExpr, &C); + A.pExpr = sqlite3ExprSetCollByToken(pParse, E.pExpr, &C); A.zStart = E.zStart; A.zEnd = &C.z[C.n]; } %ifndef SQLITE_OMIT_CAST expr(A) ::= CAST(X) LP expr(E) AS typetoken(T) RP(Y). { @@ -1106,11 +1106,11 @@ idxlist_opt(A) ::= LP idxlist(X) RP. {A = X;} idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z). { Expr *p = 0; if( C.n>0 ){ p = sqlite3Expr(pParse->db, TK_COLUMN, 0); - sqlite3ExprSetColl(pParse, p, &C); + sqlite3ExprSetCollByToken(pParse, p, &C); } A = sqlite3ExprListAppend(pParse,X, p); sqlite3ExprListSetName(pParse,A,&Y,1); sqlite3ExprListCheckLength(pParse, A, "index"); if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z; @@ -1117,11 +1117,11 @@ } idxlist(A) ::= nm(Y) collate(C) sortorder(Z). { Expr *p = 0; if( C.n>0 ){ p = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0); - sqlite3ExprSetColl(pParse, p, &C); + sqlite3ExprSetCollByToken(pParse, p, &C); } A = sqlite3ExprListAppend(pParse,0, p); sqlite3ExprListSetName(pParse, A, &Y, 1); sqlite3ExprListCheckLength(pParse, A, "index"); if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z; Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2867,11 +2867,12 @@ const char *sqlite3ErrStr(int); int sqlite3ReadSchema(Parse *pParse); CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int); CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName); CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr); -Expr *sqlite3ExprSetColl(Parse *pParse, Expr *, Token *); +Expr *sqlite3ExprSetColl(Expr*, CollSeq*); +Expr *sqlite3ExprSetCollByToken(Parse *pParse, Expr*, Token*); int sqlite3CheckCollSeq(Parse *, CollSeq *); int sqlite3CheckObjectName(Parse *, const char *); void sqlite3VdbeSetChanges(sqlite3 *, int); const void *sqlite3ValueText(sqlite3_value*, u8); Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -633,11 +633,10 @@ Expr *pRight, *pLeft; /* Right and left size of LIKE operator */ ExprList *pList; /* List of operands to the LIKE operator */ int c; /* One character in z[] */ int cnt; /* Number of non-wildcard prefix characters */ char wc[3]; /* Wildcard characters */ - CollSeq *pColl; /* Collating sequence for LHS */ sqlite3 *db = pParse->db; /* Database connection */ sqlite3_value *pVal = 0; int op; /* Opcode of pRight */ if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){ @@ -652,23 +651,10 @@ /* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must ** be the name of an indexed column with TEXT affinity. */ return 0; } assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */ - pColl = sqlite3ExprCollSeq(pParse, pLeft); - if( pColl==0 ) return 0; /* Happens when LHS has an undefined collation */ - if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) && - (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){ - /* IMP: R-09003-32046 For the GLOB operator, the column must use the - ** default BINARY collating sequence. - ** IMP: R-41408-28306 For the LIKE operator, if case_sensitive_like mode - ** is enabled then the column must use the default BINARY collating - ** sequence, or if case_sensitive_like mode is disabled then the column - ** must use the built-in NOCASE collating sequence. - */ - return 0; - } pRight = pList->a[0].pExpr; op = pRight->op; if( op==TK_REGISTER ){ op = pRight->op2; @@ -1244,10 +1230,11 @@ Expr *pStr2; /* Copy of pStr1 - RHS of LIKE/GLOB operator */ Expr *pNewExpr1; Expr *pNewExpr2; int idxNew1; int idxNew2; + CollSeq *pColl; /* Collating sequence to use */ pLeft = pExpr->x.pList->a[1].pExpr; pStr2 = sqlite3ExprDup(db, pStr1, 0); if( !db->mallocFailed ){ u8 c, *pC; /* Last character before the first wildcard */ @@ -1264,15 +1251,20 @@ c = sqlite3UpperToLower[c]; } *pC = c + 1; } - pNewExpr1 = sqlite3PExpr(pParse, TK_GE, sqlite3ExprDup(db,pLeft,0),pStr1,0); + pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, noCase ? "NOCASE" : "BINARY",0); + pNewExpr1 = sqlite3PExpr(pParse, TK_GE, + sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl), + pStr1, 0); idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew1==0 ); exprAnalyze(pSrc, pWC, idxNew1); - pNewExpr2 = sqlite3PExpr(pParse, TK_LT, sqlite3ExprDup(db,pLeft,0),pStr2,0); + pNewExpr2 = sqlite3PExpr(pParse, TK_LT, + sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl), + pStr2, 0); idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew2==0 ); exprAnalyze(pSrc, pWC, idxNew2); pTerm = &pWC->a[idxTerm]; if( isComplete ){ Index: test/like.test ================================================================== --- test/like.test +++ test/like.test @@ -775,8 +775,97 @@ do_test like-10.15 { count { SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a; } } {12 123 scan 5 like 6} + +# LIKE and GLOB where the default collating sequence is not appropriate +# but an index with the appropriate collating sequence exists. +# +do_test like-11.0 { + execsql { + CREATE TABLE t11( + a INTEGER PRIMARY KEY, + b TEXT COLLATE nocase, + c TEXT COLLATE binary + ); + INSERT INTO t11 VALUES(1, 'a','a'); + INSERT INTO t11 VALUES(2, 'ab','ab'); + INSERT INTO t11 VALUES(3, 'abc','abc'); + INSERT INTO t11 VALUES(4, 'abcd','abcd'); + INSERT INTO t11 VALUES(5, 'A','A'); + INSERT INTO t11 VALUES(6, 'AB','AB'); + INSERT INTO t11 VALUES(7, 'ABC','ABC'); + INSERT INTO t11 VALUES(8, 'ABCD','ABCD'); + INSERT INTO t11 VALUES(9, 'x','x'); + INSERT INTO t11 VALUES(10, 'yz','yz'); + INSERT INTO t11 VALUES(11, 'X','X'); + INSERT INTO t11 VALUES(12, 'YZ','YZ'); + SELECT count(*) FROM t11; + } +} {12} +do_test like-11.1 { + queryplan { + PRAGMA case_sensitive_like=OFF; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd ABC ABCD nosort t11 *} +do_test like-11.2 { + queryplan { + PRAGMA case_sensitive_like=ON; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd nosort t11 *} +do_test like-11.3 { + queryplan { + PRAGMA case_sensitive_like=OFF; + CREATE INDEX t11b ON t11(b); + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd ABC ABCD sort {} t11b} +do_test like-11.4 { + queryplan { + PRAGMA case_sensitive_like=ON; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd nosort t11 *} +do_test like-11.5 { + queryplan { + PRAGMA case_sensitive_like=OFF; + DROP INDEX t11b; + CREATE INDEX t11bnc ON t11(b COLLATE nocase); + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd ABC ABCD sort {} t11bnc} +do_test like-11.6 { + queryplan { + CREATE INDEX t11bb ON t11(b COLLATE binary); + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd ABC ABCD sort {} t11bnc} +do_test like-11.7 { + queryplan { + PRAGMA case_sensitive_like=ON; + SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; + } +} {abc abcd sort {} t11bb} +do_test like-11.8 { + queryplan { + PRAGMA case_sensitive_like=OFF; + SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY a; + } +} {abc abcd sort {} t11bb} +do_test like-11.9 { + queryplan { + CREATE INDEX t11cnc ON t11(c COLLATE nocase); + CREATE INDEX t11cb ON t11(c COLLATE binary); + SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY a; + } +} {abc abcd ABC ABCD sort {} t11cnc} +do_test like-11.10 { + queryplan { + SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY a; + } +} {abc abcd sort {} t11cb} finish_test