Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Slight performance improvement for the new code on this branch. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | vector-in-fix |
| Files: | files | file ages | folders |
| SHA3-256: |
500c67f1341fe2a7e7333d525c90df20 |
| User & Date: | dan 2024-04-20 19:19:30 |
Context
|
2024-04-23
| ||
| 11:56 | Fix an uninitialized variable problem in the new code on this branch. (Closed-Leaf check-in: 85625b38 user: dan tags: vector-in-fix) | |
|
2024-04-20
| ||
| 19:19 | Slight performance improvement for the new code on this branch. (check-in: 500c67f1 user: dan tags: vector-in-fix) | |
| 15:14 | Fix a problem where an expression like (a, b) IN (SELECT ...) might not use an index on (a, b) if the affinities and collation sequences of "a" and "b" are not identical. (check-in: 4d870fd8 user: dan tags: vector-in-fix) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
298 299 300 301 302 303 304 |
p = sqlite3ExprSkipCollateAndLikely(p->pRight);
if( ALWAYS(p!=0) && p->op==TK_COLUMN && !ExprHasProperty(p, EP_FixedCol) ){
return p;
}
return 0;
}
| > > > > > > > > > | | < > > > | > > | | 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 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 |
p = sqlite3ExprSkipCollateAndLikely(p->pRight);
if( ALWAYS(p!=0) && p->op==TK_COLUMN && !ExprHasProperty(p, EP_FixedCol) ){
return p;
}
return 0;
}
/*
** Term pTerm is guaranteed to be a WO_IN term. It may be a component term
** of a vector IN expression of the form "(x, y, ...) IN (SELECT ...)".
** This function checks to see if the term is compatible with an index
** column with affinity idxaff (one of the SQLITE_AFF_XYZ values). If so,
** it returns a pointer to the name of the collation sequence (e.g. "BINARY"
** or "NOCASE") used by the comparison in pTerm. If it is not compatible
** with affinity idxaff, NULL is returned.
*/
static SQLITE_NOINLINE const char *indexInAffinityOk(
Parse *pParse,
WhereTerm *pTerm,
u8 idxaff
){
Expr *pX = pTerm->pExpr;
Expr inexpr;
assert( pTerm->eOperator & WO_IN );
if( sqlite3ExprIsVector(pX->pLeft) ){
int iField = pTerm->u.x.iField - 1;
inexpr.op = TK_EQ;
inexpr.pLeft = pX->pLeft->x.pList->a[iField].pExpr;
assert( ExprUseXSelect(pX) );
inexpr.pRight = pX->x.pSelect->pEList->a[iField].pExpr;
pX = &inexpr;
}
if( sqlite3IndexAffinityOk(pX, idxaff) ){
CollSeq *pRet = sqlite3ExprCompareCollSeq(pParse, pX);
return pRet ? pRet->zName : sqlite3StrBINARY;
}
return 0;
}
/*
** Advance to the next WhereTerm that matches according to the criteria
** established when the pScan object was initialized by whereScanInit().
** Return NULL if there are no more matching WhereTerms.
*/
|
| ︙ | ︙ | |||
370 371 372 373 374 375 376 |
pScan->aiColumn[j] = pX->iColumn;
pScan->nEquiv++;
}
}
if( (pTerm->eOperator & pScan->opMask)!=0 ){
/* Verify the affinity and collating sequence match */
if( pScan->zCollName && (pTerm->eOperator & WO_ISNULL)==0 ){
| | | | < > > < | | 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 |
pScan->aiColumn[j] = pX->iColumn;
pScan->nEquiv++;
}
}
if( (pTerm->eOperator & pScan->opMask)!=0 ){
/* Verify the affinity and collating sequence match */
if( pScan->zCollName && (pTerm->eOperator & WO_ISNULL)==0 ){
const char *zCollName;
Parse *pParse = pWC->pWInfo->pParse;
pX = pTerm->pExpr;
if( (pTerm->eOperator & WO_IN) ){
zCollName = indexInAffinityOk(pParse, pTerm, pScan->idxaff);
if( !zCollName ) continue;
}else{
CollSeq *pColl;
if( !sqlite3IndexAffinityOk(pX, pScan->idxaff) ){
continue;
}
assert(pX->pLeft);
pColl = sqlite3ExprCompareCollSeq(pParse, pX);
zCollName = pColl ? pColl->zName : sqlite3StrBINARY;
}
if( sqlite3StrICmp(zCollName, pScan->zCollName) ){
continue;
}
}
if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0
&& (pX = pTerm->pExpr->pRight, ALWAYS(pX!=0))
&& pX->op==TK_COLUMN
&& pX->iTable==pScan->aiCur[0]
|
| ︙ | ︙ |
Changes to test/in5.test.
| ︙ | ︙ | |||
261 262 263 264 265 266 267 268 269 |
do_execsql_test 9.1 {
SELECT lower('1e500') FROM t0 WHERE rowid NOT IN (0, 0, lower('1e500'));
} {1e500}
do_execsql_test 9.2 {
SELECT lower('1e500') FROM t0 WHERE rowid != lower('1e500');
} {1e500}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > | 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 |
do_execsql_test 9.1 {
SELECT lower('1e500') FROM t0 WHERE rowid NOT IN (0, 0, lower('1e500'));
} {1e500}
do_execsql_test 9.2 {
SELECT lower('1e500') FROM t0 WHERE rowid != lower('1e500');
} {1e500}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 10.0 {
CREATE TABLE t1(a, b TEXT COLLATE NOCASE);
INSERT INTO t1 VALUES('abc', 'def');
INSERT INTO t1 VALUES('ghi', 'jkl');
}
do_execsql_test 10.1 {
SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );
} {1 2}
do_execsql_test 10.2 {
CREATE INDEX i1 ON t1(a, b COLLATE BINARY);
}
do_execsql_test 10.3 {
SELECT rowid FROM t1 WHERE (a, b) IN ( VALUES('abc', 'def'), ('ghi', 'JKL') );
} {1 2}
finish_test
|