Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Resolve names in CREATE INDEX WHERE clauses and detect errors. Disallow expressions that contain variables, subqueries, or functions. The expression is still not used for anything, however. still unused. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | partial-indices |
| Files: | files | file ages | folders |
| SHA1: |
f2aa7842c8b9df24294f09e2bde27b3f |
| User & Date: | drh 2013-07-31 19:05:22 |
Context
|
2013-07-31
| ||
| 23:22 | Add logic to the query planner to only use partial indices if the WHERE clause constrains the search to rows covered by the partial index. This is just infrastructure. The key routine, sqlite3ExprImpliesExpr(), is currently a no-op so that partial indices will never be used. check-in: 8ca3eac1 user: drh tags: partial-indices | |
| 19:05 | Resolve names in CREATE INDEX WHERE clauses and detect errors. Disallow expressions that contain variables, subqueries, or functions. The expression is still not used for anything, however. still unused. check-in: f2aa7842 user: drh tags: partial-indices | |
| 18:12 | Here begins an experimental branch for exploring the idea of a partial index. This check-in is able to parse a WHERE clause on a CREATE INDEX statement, but does not actually do anythingn with that WHERE clause yet. check-in: 6794b2dc user: drh tags: partial-indices | |
Changes
Changes to src/build.c.
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
....
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
|
iDb = sqlite3SchemaToIndex(db, p->pSchema);
#ifndef SQLITE_OMIT_CHECK
/* Resolve names in all CHECK constraint expressions.
*/
if( p->pCheck ){
SrcList sSrc; /* Fake SrcList for pParse->pNewTable */
NameContext sNC; /* Name context for pParse->pNewTable */
ExprList *pList; /* List of all CHECK constraints */
int i; /* Loop counter */
memset(&sNC, 0, sizeof(sNC));
memset(&sSrc, 0, sizeof(sSrc));
sSrc.nSrc = 1;
sSrc.a[0].zName = p->zName;
sSrc.a[0].pTab = p;
sSrc.a[0].iCursor = -1;
sNC.pParse = pParse;
sNC.pSrcList = &sSrc;
sNC.ncFlags = NC_IsCheck;
pList = p->pCheck;
for(i=0; i<pList->nExpr; i++){
if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
return;
}
}
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
/* If the db->init.busy is 1 it means we are reading the SQL off the
** "sqlite_master" or "sqlite_temp_master" table on the disk.
** So do not write to the disk again. Extract the root page number
** for the table from the db->init.newTnum field. (The page number
................................................................................
memcpy(pIndex->zName, zName, nName+1);
pIndex->pTable = pTab;
pIndex->nColumn = pList->nExpr;
pIndex->onError = (u8)onError;
pIndex->uniqNotNull = onError==OE_Abort;
pIndex->autoIndex = (u8)(pName==0);
pIndex->pSchema = db->aDb[iDb].pSchema;
pIndex->pPartIdxWhere = pPIWhere;
pPIWhere = 0;
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
/* Check to see if we should honor DESC requests on index columns
*/
if( pDb->pSchema->file_format>=4 ){
sortOrderMask = -1; /* Honor DESC */
}else{
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>
>
|
|
>
|
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
....
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
|
iDb = sqlite3SchemaToIndex(db, p->pSchema);
#ifndef SQLITE_OMIT_CHECK
/* Resolve names in all CHECK constraint expressions.
*/
if( p->pCheck ){
sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
/* If the db->init.busy is 1 it means we are reading the SQL off the
** "sqlite_master" or "sqlite_temp_master" table on the disk.
** So do not write to the disk again. Extract the root page number
** for the table from the db->init.newTnum field. (The page number
................................................................................
memcpy(pIndex->zName, zName, nName+1);
pIndex->pTable = pTab;
pIndex->nColumn = pList->nExpr;
pIndex->onError = (u8)onError;
pIndex->uniqNotNull = onError==OE_Abort;
pIndex->autoIndex = (u8)(pName==0);
pIndex->pSchema = db->aDb[iDb].pSchema;
if( pPIWhere ){
sqlite3ResolveSelfReference(pParse, pTab, NC_PartIdx, pPIWhere, 0);
pIndex->pPartIdxWhere = pPIWhere;
pPIWhere = 0;
}
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
/* Check to see if we should honor DESC requests on index columns
*/
if( pDb->pSchema->file_format>=4 ){
sortOrderMask = -1; /* Honor DESC */
}else{
|
Changes to src/resolve.c.
517 518 519 520 521 522 523 524 525 526 527 528 529 530 ... 617 618 619 620 621 622 623 624 625 626 627 628 629 630 ... 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 .... 1327 1328 1329 1330 1331 1332 1333 |
testcase( iCol==BMS-1 );
pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
}
ExprSetProperty(p, EP_Resolved);
}
return p;
}
/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current
** node in the expression tree. Return 0 to continue the search down
** the tree or 2 to abort the tree walk.
................................................................................
int nId; /* Number of characters in function name */
const char *zId; /* The function name. */
FuncDef *pDef; /* Information about the function */
u8 enc = ENC(pParse->db); /* The database encoding */
testcase( pExpr->op==TK_CONST_FUNC );
assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
zId = pExpr->u.zToken;
nId = sqlite3Strlen30(zId);
pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
if( pDef==0 ){
pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
if( pDef==0 ){
no_such_func = 1;
................................................................................
case TK_SELECT:
case TK_EXISTS: testcase( pExpr->op==TK_EXISTS );
#endif
case TK_IN: {
testcase( pExpr->op==TK_IN );
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
int nRef = pNC->nRef;
#ifndef SQLITE_OMIT_CHECK
if( (pNC->ncFlags & NC_IsCheck)!=0 ){
sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
}
#endif
sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
assert( pNC->nRef>=nRef );
if( nRef!=pNC->nRef ){
ExprSetProperty(pExpr, EP_VarSelect);
}
}
break;
}
#ifndef SQLITE_OMIT_CHECK
case TK_VARIABLE: {
if( (pNC->ncFlags & NC_IsCheck)!=0 ){
sqlite3ErrorMsg(pParse,"parameters prohibited in CHECK constraints");
}
break;
}
#endif
}
return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}
/*
** pEList is a list of expressions which are really the result set of the
** a SELECT statement. pE is a term in an ORDER BY or GROUP BY clause.
................................................................................
memset(&w, 0, sizeof(w));
w.xExprCallback = resolveExprStep;
w.xSelectCallback = resolveSelectStep;
w.pParse = pParse;
w.u.pNC = pOuterNC;
sqlite3WalkSelect(&w, p);
}
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | < < < < > | < < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 ... 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 ... 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 .... 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 |
testcase( iCol==BMS-1 );
pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
}
ExprSetProperty(p, EP_Resolved);
}
return p;
}
/*
** Report an error that an expression is not valid for a partial index WHERE
** clause.
*/
static void notValidPartIdxWhere(
Parse *pParse, /* Leave error message here */
NameContext *pNC, /* The name context */
const char *zMsg /* Type of error */
){
if( (pNC->ncFlags & NC_PartIdx)!=0 ){
sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses",
zMsg);
}
}
#ifndef SQLITE_OMIT_CHECK
/*
** Report an error that an expression is not valid for a CHECK constraint.
*/
static void notValidCheckConstraint(
Parse *pParse, /* Leave error message here */
NameContext *pNC, /* The name context */
const char *zMsg /* Type of error */
){
if( (pNC->ncFlags & NC_IsCheck)!=0 ){
sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg);
}
}
#else
# define notValidCheckConstraint(P,N,M)
#endif
/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current
** node in the expression tree. Return 0 to continue the search down
** the tree or 2 to abort the tree walk.
................................................................................
int nId; /* Number of characters in function name */
const char *zId; /* The function name. */
FuncDef *pDef; /* Information about the function */
u8 enc = ENC(pParse->db); /* The database encoding */
testcase( pExpr->op==TK_CONST_FUNC );
assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
notValidPartIdxWhere(pParse, pNC, "functions");
zId = pExpr->u.zToken;
nId = sqlite3Strlen30(zId);
pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
if( pDef==0 ){
pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
if( pDef==0 ){
no_such_func = 1;
................................................................................
case TK_SELECT:
case TK_EXISTS: testcase( pExpr->op==TK_EXISTS );
#endif
case TK_IN: {
testcase( pExpr->op==TK_IN );
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
int nRef = pNC->nRef;
notValidCheckConstraint(pParse, pNC, "subqueries");
notValidPartIdxWhere(pParse, pNC, "subqueries");
sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
assert( pNC->nRef>=nRef );
if( nRef!=pNC->nRef ){
ExprSetProperty(pExpr, EP_VarSelect);
}
}
break;
}
case TK_VARIABLE: {
notValidCheckConstraint(pParse, pNC, "parameters");
notValidPartIdxWhere(pParse, pNC, "parameters");
break;
}
}
return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}
/*
** pEList is a list of expressions which are really the result set of the
** a SELECT statement. pE is a term in an ORDER BY or GROUP BY clause.
................................................................................
memset(&w, 0, sizeof(w));
w.xExprCallback = resolveExprStep;
w.xSelectCallback = resolveSelectStep;
w.pParse = pParse;
w.u.pNC = pOuterNC;
sqlite3WalkSelect(&w, p);
}
/*
** Resolve names in expressions that can only reference a single table:
**
** * CHECK constraints
** * WHERE clauses on partial indices
**
** The Expr.iTable value for Expr.op==TK_COLUMN nodes of the expression
** is set to -1 and the Expr.iColumn value is set to the column number.
**
** Any errors cause an error message to be set in pParse.
*/
void sqlite3ResolveSelfReference(
Parse *pParse, /* Parsing context */
Table *pTab, /* The table being referenced */
int type, /* NC_IsCheck or NC_PartIdx */
Expr *pExpr, /* Expression to resolve. May be NULL. */
ExprList *pList /* Expression list to resolve. May be NUL. */
){
SrcList sSrc; /* Fake SrcList for pParse->pNewTable */
NameContext sNC; /* Name context for pParse->pNewTable */
int i; /* Loop counter */
assert( type==NC_IsCheck || type==NC_PartIdx );
memset(&sNC, 0, sizeof(sNC));
memset(&sSrc, 0, sizeof(sSrc));
sSrc.nSrc = 1;
sSrc.a[0].zName = pTab->zName;
sSrc.a[0].pTab = pTab;
sSrc.a[0].iCursor = -1;
sNC.pParse = pParse;
sNC.pSrcList = &sSrc;
sNC.ncFlags = type;
if( sqlite3ResolveExprNames(&sNC, pExpr) ) return;
if( pList ){
for(i=0; i<pList->nExpr; i++){
if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
return;
}
}
}
}
|
Changes to src/sqliteInt.h.
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
....
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
|
*/
#define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */
#define NC_HasAgg 0x02 /* One or more aggregate functions seen */
#define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */
#define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */
#define NC_AsMaybe 0x10 /* Resolve to AS terms of the result set only
** if no other resolution is available */
/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*);
void sqlite3Analyze(Parse*, Token*, Token*);
|
>
>
|
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
....
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
|
*/
#define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */
#define NC_HasAgg 0x02 /* One or more aggregate functions seen */
#define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */
#define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */
#define NC_AsMaybe 0x10 /* Resolve to AS terms of the result set only
** if no other resolution is available */
#define NC_PartIdx 0x20 /* True if resolving a partial index WHERE */
/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*);
void sqlite3Analyze(Parse*, Token*, Token*);
|
Changes to test/index6.test.
25 26 27 28 29 30 31 32 33 |
CREATE VIRTUAL TABLE nums USING wholenumber;
INSERT INTO t1(a,b)
SELECT CASE WHEN value%3!=0 THEN value END, value
FROM nums WHERE value<=20;
SELECT count(a), count(b) FROM t1;
}
} {14 20}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > |
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
CREATE VIRTUAL TABLE nums USING wholenumber;
INSERT INTO t1(a,b)
SELECT CASE WHEN value%3!=0 THEN value END, value
FROM nums WHERE value<=20;
SELECT count(a), count(b) FROM t1;
}
} {14 20}
do_test index6-1.2 {
catchsql {
CREATE INDEX bad1 ON t1(a,b) WHERE c IS NOT NULL;
}
} {1 {no such column: c}}
do_test index6-1.3 {
catchsql {
CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
}
} {1 {subqueries prohibited in partial index WHERE clauses}}
do_test index6-1.4 {
catchsql {
CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
}
} {1 {parameters prohibited in partial index WHERE clauses}}
do_test index6-1.5 {
catchsql {
CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
}
} {1 {functions prohibited in partial index WHERE clauses}}
do_test index6-1.6 {
catchsql {
CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
}
} {1 {functions prohibited in partial index WHERE clauses}}
finish_test
|