Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Prevent NULLS FIRST/LAST from being used in CREATE INDEX and other statements. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nulls-last |
Files: | files | file ages | folders |
SHA3-256: |
bb9767a287097a615aeb4abdba689b10 |
User & Date: | dan 2019-08-19 17:26:32.202 |
Context
2019-08-19
| ||
19:59 | Fix problems with window frames that use ORDER BY ... NULLS LAST etc. (check-in: 75d665a494 user: dan tags: nulls-last) | |
17:26 | Prevent NULLS FIRST/LAST from being used in CREATE INDEX and other statements. (check-in: bb9767a287 user: dan tags: nulls-last) | |
2019-08-17
| ||
18:16 | Add tests to ensure that this branch really does fix ticket [f8a7060ece]. (check-in: f892066425 user: dan tags: nulls-last) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 | p->aSortOrder = (u8*)pExtra; p->nColumn = nCol; p->nKeyCol = nCol - 1; *ppExtra = ((char*)p) + nByte; } return p; } /* ** Create a new index for an SQL table. pName1.pName2 is the name of the index ** and pTblList is the name of the table that is to be indexed. Both will ** be NULL for a primary key or an index that is created to satisfy a ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is | > > > > > > > > > > > > > > > > > > > > > | 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 | p->aSortOrder = (u8*)pExtra; p->nColumn = nCol; p->nKeyCol = nCol - 1; *ppExtra = ((char*)p) + nByte; } return p; } /* ** If expression list pList contains an expression that was parsed with ** an explicit "NULLS FIRST" or "NULLS LAST" clause, leave an error in ** pParse and return non-zero. Otherwise, return zero. */ int sqlite3HasExplicitNulls(Parse *pParse, ExprList *pList){ if( pList ){ int i; for(i=0; i<pList->nExpr; i++){ if( pList->a[i].bNulls ){ u8 sf = pList->a[i].sortFlags; sqlite3ErrorMsg(pParse, "unsupported use of NULLS %s", (sf==0 || sf==3) ? "FIRST" : "LAST" ); return 1; } } } return 0; } /* ** Create a new index for an SQL table. pName1.pName2 is the name of the index ** and pTblList is the name of the table that is to be indexed. Both will ** be NULL for a primary key or an index that is created to satisfy a ** UNIQUE constraint. If pTable and pIndex are NULL, use pParse->pNewTable ** as the table to be indexed. pParse->pNewTable is a table that is |
︙ | ︙ | |||
3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 | goto exit_create_index; } if( IN_DECLARE_VTAB && idxType!=SQLITE_IDXTYPE_PRIMARYKEY ){ goto exit_create_index; } if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ goto exit_create_index; } /* ** Find the table that is to be indexed. Return early if not found. */ if( pTblName!=0 ){ | > > > | 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 | goto exit_create_index; } if( IN_DECLARE_VTAB && idxType!=SQLITE_IDXTYPE_PRIMARYKEY ){ goto exit_create_index; } if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ goto exit_create_index; } if( sqlite3HasExplicitNulls(pParse, pList) ){ goto exit_create_index; } /* ** Find the table that is to be indexed. Return early if not found. */ if( pTblName!=0 ){ |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 | return pList; } /* ** Set the sort order for the last element on the given ExprList. */ void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder, int eNulls){ if( p==0 ) return; assert( p->nExpr>0 ); assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC==0 && SQLITE_SO_DESC>0 ); assert( iSortOrder==SQLITE_SO_UNDEFINED || iSortOrder==SQLITE_SO_ASC || iSortOrder==SQLITE_SO_DESC ); assert( eNulls==SQLITE_SO_UNDEFINED || eNulls==SQLITE_SO_ASC || eNulls==SQLITE_SO_DESC ); | > > > | > > | > | > > | > | 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 | return pList; } /* ** Set the sort order for the last element on the given ExprList. */ void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder, int eNulls){ struct ExprList_item *pItem; if( p==0 ) return; assert( p->nExpr>0 ); assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC==0 && SQLITE_SO_DESC>0 ); assert( iSortOrder==SQLITE_SO_UNDEFINED || iSortOrder==SQLITE_SO_ASC || iSortOrder==SQLITE_SO_DESC ); assert( eNulls==SQLITE_SO_UNDEFINED || eNulls==SQLITE_SO_ASC || eNulls==SQLITE_SO_DESC ); pItem = &p->a[p->nExpr-1]; assert( pItem->bNulls==0 ); if( iSortOrder==SQLITE_SO_UNDEFINED ){ iSortOrder = SQLITE_SO_ASC; } pItem->sortFlags = (u8)iSortOrder; if( eNulls!=SQLITE_SO_UNDEFINED ){ pItem->bNulls = 1; if( iSortOrder!=eNulls ){ pItem->sortFlags |= KEYINFO_ORDER_BIGNULL; } } } /* ** Set the ExprList.a[].zName element of the most recently added item ** on the expression list. ** |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
828 829 830 831 832 833 834 835 836 837 838 839 840 841 | } #ifndef SQLITE_OMIT_UPSERT if( pUpsert ){ if( IsVirtual(pTab) ){ sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"", pTab->zName); goto insert_cleanup; } pTabList->a[0].iCursor = iDataCur; pUpsert->pUpsertSrc = pTabList; pUpsert->regData = regData; pUpsert->iDataCur = iDataCur; pUpsert->iIdxCur = iIdxCur; if( pUpsert->pUpsertTarget ){ | > > > | 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 | } #ifndef SQLITE_OMIT_UPSERT if( pUpsert ){ if( IsVirtual(pTab) ){ sqlite3ErrorMsg(pParse, "UPSERT not implemented for virtual table \"%s\"", pTab->zName); goto insert_cleanup; } if( sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget) ){ goto insert_cleanup; } pTabList->a[0].iCursor = iDataCur; pUpsert->pUpsertSrc = pTabList; pUpsert->regData = regData; pUpsert->iDataCur = iDataCur; pUpsert->iIdxCur = iIdxCur; if( pUpsert->pUpsertTarget ){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2601 2602 2603 2604 2605 2606 2607 | */ struct ExprList { int nExpr; /* Number of expressions on the list */ struct ExprList_item { /* For each expression in the list */ Expr *pExpr; /* The parse tree for this expression */ char *zName; /* Token associated with this expression */ char *zSpan; /* Original text of the expression */ | | > | 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 | */ struct ExprList { int nExpr; /* Number of expressions on the list */ struct ExprList_item { /* For each expression in the list */ Expr *pExpr; /* The parse tree for this expression */ char *zName; /* Token associated with this expression */ char *zSpan; /* Original text of the expression */ u8 sortFlags; /* Mask of KEYINFO_ORDER_* flags */ unsigned done :1; /* A flag to indicate when processing is finished */ unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */ unsigned reusable :1; /* Constant expression is reusable */ unsigned bSorterRef :1; /* Defer evaluation until after sorting */ unsigned bNulls: 1; /* True if explicit "NULLS FIRST/LAST" */ union { struct { u16 iOrderByCol; /* For ORDER BY, column number in result set */ u16 iAlias; /* Index into Parse.aAlias[] for zName */ } x; int iConstExprReg; /* Register in which Expr value is cached */ } u; |
︙ | ︙ | |||
4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 | Schema *sqlite3SchemaGet(sqlite3 *, Btree *); int sqlite3SchemaToIndex(sqlite3 *db, Schema *); KeyInfo *sqlite3KeyInfoAlloc(sqlite3*,int,int); void sqlite3KeyInfoUnref(KeyInfo*); KeyInfo *sqlite3KeyInfoRef(KeyInfo*); KeyInfo *sqlite3KeyInfoOfIndex(Parse*, Index*); KeyInfo *sqlite3KeyInfoFromExprList(Parse*, ExprList*, int, int); #ifdef SQLITE_DEBUG int sqlite3KeyInfoIsWriteable(KeyInfo*); #endif int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *, void (*)(sqlite3_context*,int,sqlite3_value **), void (*)(sqlite3_context*,int,sqlite3_value **), | > | 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 | Schema *sqlite3SchemaGet(sqlite3 *, Btree *); int sqlite3SchemaToIndex(sqlite3 *db, Schema *); KeyInfo *sqlite3KeyInfoAlloc(sqlite3*,int,int); void sqlite3KeyInfoUnref(KeyInfo*); KeyInfo *sqlite3KeyInfoRef(KeyInfo*); KeyInfo *sqlite3KeyInfoOfIndex(Parse*, Index*); KeyInfo *sqlite3KeyInfoFromExprList(Parse*, ExprList*, int, int); int sqlite3HasExplicitNulls(Parse*, ExprList*); #ifdef SQLITE_DEBUG int sqlite3KeyInfoIsWriteable(KeyInfo*); #endif int sqlite3CreateFunc(sqlite3 *, const char *, int, int, void *, void (*)(sqlite3_context*,int,sqlite3_value **), void (*)(sqlite3_context*,int,sqlite3_value **), |
︙ | ︙ |
Changes to src/trigger.c.
︙ | ︙ | |||
459 460 461 462 463 464 465 466 467 468 469 470 471 472 | pSelect = 0; }else{ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); } pTriggerStep->pIdList = pColumn; pTriggerStep->pUpsert = pUpsert; pTriggerStep->orconf = orconf; }else{ testcase( pColumn ); sqlite3IdListDelete(db, pColumn); testcase( pUpsert ); sqlite3UpsertDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); | > > > | 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 | pSelect = 0; }else{ pTriggerStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); } pTriggerStep->pIdList = pColumn; pTriggerStep->pUpsert = pUpsert; pTriggerStep->orconf = orconf; if( pUpsert ){ sqlite3HasExplicitNulls(pParse, pUpsert->pUpsertTarget); } }else{ testcase( pColumn ); sqlite3IdListDelete(db, pColumn); testcase( pUpsert ); sqlite3UpsertDelete(db, pUpsert); } sqlite3SelectDelete(db, pSelect); |
︙ | ︙ |
Changes to test/nulls1.test.
︙ | ︙ | |||
78 79 80 81 82 83 84 85 86 87 | SELECT * FROM t2 WHERE a=1 ORDER BY b DESC NULLS FIRST } { 1 {} 3 1 {} 2 1 4 4 1 1 1 } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | SELECT * FROM t2 WHERE a=1 ORDER BY b DESC NULLS FIRST } { 1 {} 3 1 {} 2 1 4 4 1 1 1 } #------------------------------------------------------------------------- reset_db do_execsql_test 3.0 { CREATE TABLE t1(a, b, c, d, UNIQUE (b)); } foreach {tn sql err} { 1 { CREATE INDEX i1 ON t1(a ASC NULLS LAST) } LAST 2 { CREATE INDEX i1 ON t1(a ASC NULLS FIRST) } FIRST 3 { CREATE INDEX i1 ON t1(a, b ASC NULLS LAST) } LAST 4 { CREATE INDEX i1 ON t1(a, b ASC NULLS FIRST) } FIRST 5 { CREATE INDEX i1 ON t1(a DESC NULLS LAST) } LAST 6 { CREATE INDEX i1 ON t1(a DESC NULLS FIRST) } FIRST 7 { CREATE INDEX i1 ON t1(a, b DESC NULLS LAST) } LAST 8 { CREATE INDEX i1 ON t1(a, b DESC NULLS FIRST) } FIRST 9 { CREATE TABLE t2(a, b, PRIMARY KEY(a DESC, b NULLS FIRST)) } FIRST 10 { CREATE TABLE t2(a, b, UNIQUE(a DESC NULLS FIRST, b)) } FIRST 11 { INSERT INTO t1 VALUES(1, 2, 3, 4) ON CONFLICT (b DESC NULLS LAST) DO UPDATE SET a = a+1 } LAST 12 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN INSERT INTO t1 VALUES(1, 2, 3, 4) ON CONFLICT (b DESC NULLS FIRST) DO UPDATE SET a = a+1; END } FIRST } { do_catchsql_test 3.1.$tn $sql "1 {unsupported use of NULLS $err}" } do_execsql_test 3.2 { CREATE TABLE first(nulls, last); INSERT INTO first(last, nulls) VALUES(100,200), (300,400), (200,300); SELECT * FROM first ORDER BY nulls; } { 200 100 300 200 400 300 } finish_test |