/ Check-in [44bb1bfe]
Login

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

Overview
Comment:Fix the expression comparison logic to take the COLLATE operator into account. Ticket [360c6073e197]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 44bb1bfe5dedd8054ddd933941ee4112ed8d3b68
User & Date: drh 2010-01-07 15:17:02
References
2010-01-07
15:17 Fixed ticket [360c6073]: Aggregate MAX() function with COLLATE clause plus 2 other changes artifact: 0f3eb0ef user: drh
Context
2010-01-07
21:49
Small tool that attempts to repair a journal header. check-in: 05b18b5f user: shaneh tags: trunk
15:17
Fix the expression comparison logic to take the COLLATE operator into account. Ticket [360c6073e197] check-in: 44bb1bfe user: drh tags: trunk
11:27
Changes to test code so that testfixture compiles when OMIT_SHARED_CACHE and OMIT_UTF16 are defined. check-in: d6ee5ff6 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

3411
3412
3413
3414
3415
3416
3417
3418
3419


3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467


3468
3469
3470
3471
3472
3473
3474
3475
....
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
    }
  }
  sqlite3ReleaseTempReg(pParse, regFree1);
  sqlite3ReleaseTempReg(pParse, regFree2);
}

/*
** Do a deep comparison of two expression trees.  Return TRUE (non-zero)
** if they are identical and return FALSE if they differ in any way.


**
** Sometimes this routine will return FALSE even if the two expressions
** really are equivalent.  If we cannot prove that the expressions are
** identical, we return FALSE just to be safe.  So if this routine
** returns false, then you do not really know for certain if the two
** expressions are the same.  But if you get a TRUE return, then you
** can be sure the expressions are the same.  In the places where
** this routine is used, it does not hurt to get an extra FALSE - that
** just might result in some slightly slower code.  But returning
** an incorrect TRUE could lead to a malfunction.
*/
int sqlite3ExprCompare(Expr *pA, Expr *pB){
  int i;
  if( pA==0||pB==0 ){
    return pB==pA;
  }
  assert( !ExprHasAnyProperty(pA, EP_TokenOnly|EP_Reduced) );
  assert( !ExprHasAnyProperty(pB, EP_TokenOnly|EP_Reduced) );
  if( ExprHasProperty(pA, EP_xIsSelect) || ExprHasProperty(pB, EP_xIsSelect) ){
    return 0;
  }
  if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 0;
  if( pA->op!=pB->op ) return 0;
  if( !sqlite3ExprCompare(pA->pLeft, pB->pLeft) ) return 0;
  if( !sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 0;

  if( pA->x.pList && pB->x.pList ){
    if( pA->x.pList->nExpr!=pB->x.pList->nExpr ) return 0;
    for(i=0; i<pA->x.pList->nExpr; i++){
      Expr *pExprA = pA->x.pList->a[i].pExpr;
      Expr *pExprB = pB->x.pList->a[i].pExpr;
      if( !sqlite3ExprCompare(pExprA, pExprB) ) return 0;
    }
  }else if( pA->x.pList || pB->x.pList ){
    return 0;
  }

  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( ExprHasProperty(pA, EP_IntValue) ){
    if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){
      return 0;
    }
  }else if( pA->op!=TK_COLUMN && pA->u.zToken ){
    if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 0;
    if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ){
      return 0;
    }
  }


  return 1;
}


/*
** Add a new element to the pAggInfo->aCol[] array.  Return the index of
** the new element.  Return a negative number if malloc fails.
*/
................................................................................
      ** to be ignored */
      if( pNC->nDepth==0 ){
        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure
        */
        struct AggInfo_func *pItem = pAggInfo->aFunc;
        for(i=0; i<pAggInfo->nFunc; i++, pItem++){
          if( sqlite3ExprCompare(pItem->pExpr, pExpr) ){
            break;
          }
        }
        if( i>=pAggInfo->nFunc ){
          /* pExpr is original.  Make a new entry in pAggInfo->aFunc[]
          */
          u8 enc = ENC(pParse->db);







|
|
>
>

|

|
|
|

|

|




|




|

|
|
|
|


|



|


|


|


|


|

|


>
>
|







 







|







3411
3412
3413
3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
....
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
    }
  }
  sqlite3ReleaseTempReg(pParse, regFree1);
  sqlite3ReleaseTempReg(pParse, regFree2);
}

/*
** Do a deep comparison of two expression trees.  Return 0 if the two
** expressions are completely identical.  Return 1 if they differ only
** by a COLLATE operator at the top level.  Return 2 if there are differences
** other than the top-level COLLATE operator.
**
** Sometimes this routine will return 2 even if the two expressions
** really are equivalent.  If we cannot prove that the expressions are
** identical, we return 2 just to be safe.  So if this routine
** returns 2, then you do not really know for certain if the two
** expressions are the same.  But if you get a 0 or 1 return, then you
** can be sure the expressions are the same.  In the places where
** this routine is used, it does not hurt to get an extra 2 - that
** just might result in some slightly slower code.  But returning
** an incorrect 0 or 1 could lead to a malfunction.
*/
int sqlite3ExprCompare(Expr *pA, Expr *pB){
  int i;
  if( pA==0||pB==0 ){
    return pB==pA ? 0 : 2;
  }
  assert( !ExprHasAnyProperty(pA, EP_TokenOnly|EP_Reduced) );
  assert( !ExprHasAnyProperty(pB, EP_TokenOnly|EP_Reduced) );
  if( ExprHasProperty(pA, EP_xIsSelect) || ExprHasProperty(pB, EP_xIsSelect) ){
    return 2;
  }
  if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  if( pA->op!=pB->op ) return 2;
  if( sqlite3ExprCompare(pA->pLeft, pB->pLeft) ) return 2;
  if( sqlite3ExprCompare(pA->pRight, pB->pRight) ) return 2;

  if( pA->x.pList && pB->x.pList ){
    if( pA->x.pList->nExpr!=pB->x.pList->nExpr ) return 2;
    for(i=0; i<pA->x.pList->nExpr; i++){
      Expr *pExprA = pA->x.pList->a[i].pExpr;
      Expr *pExprB = pB->x.pList->a[i].pExpr;
      if( sqlite3ExprCompare(pExprA, pExprB) ) return 2;
    }
  }else if( pA->x.pList || pB->x.pList ){
    return 2;
  }

  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 2;
  if( ExprHasProperty(pA, EP_IntValue) ){
    if( !ExprHasProperty(pB, EP_IntValue) || pA->u.iValue!=pB->u.iValue ){
      return 2;
    }
  }else if( pA->op!=TK_COLUMN && pA->u.zToken ){
    if( ExprHasProperty(pB, EP_IntValue) || NEVER(pB->u.zToken==0) ) return 2;
    if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ){
      return 2;
    }
  }
  if( (pA->flags & EP_ExpCollate)!=(pB->flags & EP_ExpCollate) ) return 1;
  if( (pA->flags & EP_ExpCollate)!=0 && pA->pColl!=pB->pColl ) return 2;
  return 0;
}


/*
** Add a new element to the pAggInfo->aCol[] array.  Return the index of
** the new element.  Return a negative number if malloc fails.
*/
................................................................................
      ** to be ignored */
      if( pNC->nDepth==0 ){
        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure
        */
        struct AggInfo_func *pItem = pAggInfo->aFunc;
        for(i=0; i<pAggInfo->nFunc; i++, pItem++){
          if( sqlite3ExprCompare(pItem->pExpr, pExpr)==0 ){
            break;
          }
        }
        if( i>=pAggInfo->nFunc ){
          /* pExpr is original.  Make a new entry in pAggInfo->aFunc[]
          */
          u8 enc = ENC(pParse->db);

Changes to src/insert.c.

1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
    }
    if( pSrcIdx==0 ){
      return 0;    /* pDestIdx has no corresponding index in pSrc */
    }
  }
#ifndef SQLITE_OMIT_CHECK
  if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
    return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
  }
#endif

  /* If we get this far, it means either:
  **
  **    *   We can always do the transfer if the table contains an







|







1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
    }
    if( pSrcIdx==0 ){
      return 0;    /* pDestIdx has no corresponding index in pSrc */
    }
  }
#ifndef SQLITE_OMIT_CHECK
  if( pDest->pCheck && sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
    return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
  }
#endif

  /* If we get this far, it means either:
  **
  **    *   We can always do the transfer if the table contains an

Changes to src/resolve.c.

682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
  }

  /* Try to match the ORDER BY expression against an expression
  ** in the result set.  Return an 1-based index of the matching
  ** result-set entry.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqlite3ExprCompare(pEList->a[i].pExpr, pE) ){
      return i+1;
    }
  }

  /* If no match, return 0. */
  return 0;
}







|







682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
  }

  /* Try to match the ORDER BY expression against an expression
  ** in the result set.  Return an 1-based index of the matching
  ** result-set entry.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqlite3ExprCompare(pEList->a[i].pExpr, pE)<2 ){
      return i+1;
    }
  }

  /* If no match, return 0. */
  return 0;
}

Changes to test/insert4.test.

297
298
299
300
301
302
303





304




305














306
    CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
    INSERT INTO t2 SELECT * FROM t3;
  }
  set ::sqlite3_xferopt_count
} {0}



























finish_test







>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>

297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
    CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM);
    INSERT INTO t2 SELECT * FROM t3;
  }
  set ::sqlite3_xferopt_count
} {0}


do_test insert4-6.5 {
  execsql {
    CREATE TABLE t6a(x CHECK( x<>'abc' ));
    INSERT INTO t6a VALUES('ABC');
    SELECT * FROM t6a;
  }
} {ABC}
do_test insert4-6.6 {
  execsql {
    CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase ));
  }
  catchsql {
    INSERT INTO t6b SELECT * FROM t6a;
  }
} {1 {constraint failed}}
do_test insert4-6.7 {
  execsql {
    DROP TABLE t6b;
    CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' ));
  }
  catchsql {
    INSERT INTO t6b SELECT * FROM t6a;
  }
} {1 {constraint failed}}

finish_test

Changes to test/minmax3.test.

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

































































217
do_test minmax3-2.7 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
} {{}}
do_test minmax3-2.8 {
  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
} {{}}

do_test minmax3-2.1 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(a, b);
    CREATE INDEX i3 ON t2(a, b DESC);
    INSERT INTO t2 VALUES(1, NULL);
    INSERT INTO t2 VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 2);
................................................................................
    INSERT INTO t2 VALUES(2, 2);
    INSERT INTO t2 VALUES(2, 3);
    INSERT INTO t2 VALUES(3, 1);
    INSERT INTO t2 VALUES(3, 2);
    INSERT INTO t2 VALUES(3, 3);
  }
} {}
do_test minmax3-2.2 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-2.3 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
} {2}
do_test minmax3-2.4 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
} {1}
do_test minmax3-2.5 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-2.6 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
} {1}
do_test minmax3-2.7 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
} {{}}
do_test minmax3-2.8 {
  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
} {{}}


































































finish_test







|







 







|


|


|


|


|


|


|



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

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
do_test minmax3-2.7 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
} {{}}
do_test minmax3-2.8 {
  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
} {{}}

do_test minmax3-3.1 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(a, b);
    CREATE INDEX i3 ON t2(a, b DESC);
    INSERT INTO t2 VALUES(1, NULL);
    INSERT INTO t2 VALUES(1, 1);
    INSERT INTO t2 VALUES(1, 2);
................................................................................
    INSERT INTO t2 VALUES(2, 2);
    INSERT INTO t2 VALUES(2, 3);
    INSERT INTO t2 VALUES(3, 1);
    INSERT INTO t2 VALUES(3, 2);
    INSERT INTO t2 VALUES(3, 3);
  }
} {}
do_test minmax3-3.2 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-3.3 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
} {2}
do_test minmax3-3.4 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
} {1}
do_test minmax3-3.5 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
} {1}
do_test minmax3-3.6 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
} {1}
do_test minmax3-3.7 {
  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
} {{}}
do_test minmax3-3.8 {
  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
} {{}}

do_test minmax3-4.1 {
  execsql {
    CREATE TABLE t4(x);
    INSERT INTO t4 VALUES('abc');
    INSERT INTO t4 VALUES('BCD');
    SELECT max(x) FROM t4;
  }
} {abc}
do_test minmax3-4.2 {
  execsql {
    SELECT max(x COLLATE nocase) FROM t4;
  }
} {BCD}
do_test minmax3-4.3 {
  execsql {
    SELECT max(x), max(x COLLATE nocase) FROM t4;
  }
} {abc BCD}
do_test minmax3-4.4 {
  execsql {
    SELECT max(x COLLATE binary), max(x COLLATE nocase) FROM t4;
  }
} {abc BCD}
do_test minmax3-4.5 {
  execsql {
    SELECT max(x COLLATE nocase), max(x COLLATE rtrim) FROM t4;
  }
} {BCD abc}
do_test minmax3-4.6 {
  execsql {
    SELECT max(x COLLATE nocase), max(x) FROM t4;
  }
} {BCD abc}
do_test minmax3-4.10 {
  execsql {
    SELECT min(x) FROM t4;
  }
} {BCD}
do_test minmax3-4.11 {
  execsql {
    SELECT min(x COLLATE nocase) FROM t4;
  }
} {abc}
do_test minmax3-4.12 {
  execsql {
    SELECT min(x), min(x COLLATE nocase) FROM t4;
  }
} {BCD abc}
do_test minmax3-4.13 {
  execsql {
    SELECT min(x COLLATE binary), min(x COLLATE nocase) FROM t4;
  }
} {BCD abc}
do_test minmax3-4.14 {
  execsql {
    SELECT min(x COLLATE nocase), min(x COLLATE rtrim) FROM t4;
  }
} {abc BCD}
do_test minmax3-4.15 {
  execsql {
    SELECT min(x COLLATE nocase), min(x) FROM t4;
  }
} {abc BCD}


finish_test