Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Get the conflict-target clause parsing working correctly, with test cases. This change involves an enhancement to sqlite3ExprCompare() which needs to be reviewed on trunk prior to merging. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | upsert |
Files: | files | file ages | folders |
SHA3-256: |
5bf704256206f84b3db7a5d852321560 |
User & Date: | drh 2018-04-13 14:27:01.434 |
Context
2018-04-13
| ||
15:14 | Get the ON CONFLICT DO NOTHING form of upsert working by mapping it into INSERT OR IGNORE. (check-in: d07f05e98b user: drh tags: upsert) | |
14:27 | Get the conflict-target clause parsing working correctly, with test cases. This change involves an enhancement to sqlite3ExprCompare() which needs to be reviewed on trunk prior to merging. (check-in: 5bf7042562 user: drh tags: upsert) | |
13:44 | Improved conflict-target matching logic. (check-in: 98d32ba661 user: drh tags: upsert) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
4895 4896 4897 4898 4899 4900 4901 4902 | return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ if( pA->op==TK_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ | > > | | 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 | return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ if( pA->op==TK_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; }else if( pA->op==TK_COLLATE ){ return sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ? 2 : 0; }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ return 2; } } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){ if( combinedFlags & EP_xIsSelect ) return 2; if( sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2; if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2; |
︙ | ︙ |
Changes to src/upsert.c.
︙ | ︙ | |||
77 78 79 80 81 82 83 | ** is wrong. */ int sqlite3UpsertAnalyzeTarget( Parse *pParse, /* The parsing context */ SrcList *pTabList, /* Table into which we are inserting */ Upsert *pUpsert /* The ON CONFLICT clauses */ ){ | < | > > | | | > | < | 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | ** is wrong. */ int sqlite3UpsertAnalyzeTarget( Parse *pParse, /* The parsing context */ SrcList *pTabList, /* Table into which we are inserting */ Upsert *pUpsert /* The ON CONFLICT clauses */ ){ Table *pTab; /* That table into which we are inserting */ int rc; /* Result code */ int iCursor; /* Cursor used by pTab */ Index *pIdx; /* One of the indexes of pTab */ ExprList *pTarget; /* The conflict-target clause */ Expr *pTerm; /* One term of the conflict-target clause */ NameContext sNC; /* Context for resolving symbolic names */ Expr sCol[2]; /* Index column converted into an Expr */ assert( pTabList->nSrc==1 ); assert( pTabList->a[0].pTab!=0 ); assert( pUpsert!=0 ); assert( pUpsert->pUpsertTarget!=0 ); /* Resolve all symbolic names in the conflict-target clause, which |
︙ | ︙ | |||
105 106 107 108 109 110 111 112 113 114 115 116 117 118 | if( rc ) return rc; rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere); if( rc ) return rc; /* Check to see if the conflict target matches the rowid. */ pTab = pTabList->a[0].pTab; pTarget = pUpsert->pUpsertTarget; if( HasRowid(pTab) && pTarget->nExpr==1 && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey) ){ /* The conflict-target is the rowid of the primary table */ assert( pUpsert->pUpsertIdx==0 ); | > | 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | if( rc ) return rc; rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere); if( rc ) return rc; /* Check to see if the conflict target matches the rowid. */ pTab = pTabList->a[0].pTab; pTarget = pUpsert->pUpsertTarget; iCursor = pTabList->a[0].iCursor; if( HasRowid(pTab) && pTarget->nExpr==1 && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey) ){ /* The conflict-target is the rowid of the primary table */ assert( pUpsert->pUpsertIdx==0 ); |
︙ | ︙ | |||
135 136 137 138 139 140 141 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int ii, jj, nn; if( !IsUniqueIndex(pIdx) ) continue; if( pTarget->nExpr!=pIdx->nKeyCol ) continue; if( pIdx->pPartIdxWhere ){ if( pUpsert->pUpsertTargetWhere==0 ) continue; if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere, | | | < < | 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int ii, jj, nn; if( !IsUniqueIndex(pIdx) ) continue; if( pTarget->nExpr!=pIdx->nKeyCol ) continue; if( pIdx->pPartIdxWhere ){ if( pUpsert->pUpsertTargetWhere==0 ) continue; if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere, pIdx->pPartIdxWhere, iCursor)!=0 ){ continue; } } nn = pIdx->nKeyCol; for(ii=0; ii<nn; ii++){ Expr *pExpr; if( pIdx->aiColumn[ii]==XN_EXPR ){ assert( pIdx->aColExpr!=0 ); assert( pIdx->aColExpr->nExpr>ii ); pExpr = pIdx->aColExpr->a[ii].pExpr; }else{ sCol[1].iColumn = pIdx->aiColumn[ii]; sCol[0].u.zToken = (char*)pIdx->azColl[ii]; pExpr = &sCol[0]; } for(jj=0; jj<nn; jj++){ if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,iCursor)<2 ){ break; /* Column ii of the index matches column jj of target */ } } if( jj>=nn ){ /* The target contains no match for column jj of the index */ break; } |
︙ | ︙ |
Changes to test/upsert1.test.
︙ | ︙ | |||
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} breakpoint do_catchsql_test upsert1-130 { INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_catchsql_test upsert1-200 { DROP INDEX t1x1; DELETE FROM t1; CREATE UNIQUE INDEX t1x1 ON t1(a||b); INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING; SELECT * FROM t1; } {0 {5 6 0}} do_catchsql_test upsert1-210 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||+b) DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} finish_test | > > > > > > > > > > > > > > > > > > > > > > | 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} breakpoint do_catchsql_test upsert1-130 { INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_execsql_test upsert1-140 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING; SELECT * FROM t1; } {5 6 0} do_catchsql_test upsert1-200 { DROP INDEX t1x1; DELETE FROM t1; CREATE UNIQUE INDEX t1x1 ON t1(a||b); INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING; SELECT * FROM t1; } {0 {5 6 0}} do_catchsql_test upsert1-210 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||+b) DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_catchsql_test upsert1-300 { DROP INDEX t1x1; DELETE FROM t1; CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_catchsql_test upsert1-310 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b!=10 DO NOTHING; SELECT * FROM t1; } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}} do_execsql_test upsert1-320 { DELETE FROM t1; INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b>10 DO NOTHING; SELECT * FROM t1; } {5 6 0} finish_test |