Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with vector IN operators used with an index where the affinities and collations for the various vector terms are different. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
86e8c782e7494377de3c27b750cd83a7 |
| User & Date: | drh 2024-04-23 12:02:03 |
Context
|
2024-04-24
| ||
| 06:43 | Remove an undocumented feature from the JS Worker1.open API because it relies on broken behavior which has no VFS-agnostic workaround. (check-in: 3a640038 user: stephan tags: trunk) | |
|
2024-04-23
| ||
| 12:02 | Fix a problem with vector IN operators used with an index where the affinities and collations for the various vector terms are different. (check-in: 86e8c782 user: drh tags: trunk) | |
| 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) | |
| 06:49 | Remove some dead WASM-side code. (check-in: 0a07ee27 user: stephan tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
297 298 299 300 301 302 303 304 305 306 307 308 309 310 |
static Expr *whereRightSubexprIsColumn(Expr *p){
p = sqlite3ExprSkipCollateAndLikely(p->pRight);
if( ALWAYS(p!=0) && p->op==TK_COLUMN && !ExprHasProperty(p, EP_FixedCol) ){
return p;
}
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.
*/
static WhereTerm *whereScanNext(WhereScan *pScan){
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 |
static Expr *whereRightSubexprIsColumn(Expr *p){
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.flags = 0;
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.
*/
static WhereTerm *whereScanNext(WhereScan *pScan){
|
| ︙ | ︙ | |||
348 349 350 351 352 353 354 |
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 ){
| | > > > > > > | | | | | > > | | | 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 415 |
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
|