/ Check-in [5bf70425]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 5bf704256206f84b3db7a5d8523215604eeb218ef8db86316d1e43ecd7248d6a
User & Date: drh 2018-04-13 14:27:01
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: d07f05e9 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: 5bf70425 user: drh tags: upsert
13:44
Improved conflict-target matching logic. check-in: 98d32ba6 user: drh tags: upsert
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  4895   4895         return 1;
  4896   4896       }
  4897   4897       return 2;
  4898   4898     }
  4899   4899     if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){
  4900   4900       if( pA->op==TK_FUNCTION ){
  4901   4901         if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
         4902  +    }else if( pA->op==TK_COLLATE ){
         4903  +      return sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ? 2 : 0;
  4902   4904       }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
  4903         -      return pA->op==TK_COLLATE ? 1 : 2;
         4905  +      return 2;
  4904   4906       }
  4905   4907     }
  4906   4908     if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  4907   4909     if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){
  4908   4910       if( combinedFlags & EP_xIsSelect ) return 2;
  4909   4911       if( sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2;
  4910   4912       if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2;

Changes to src/upsert.c.

    77     77   ** is wrong.
    78     78   */
    79     79   int sqlite3UpsertAnalyzeTarget(
    80     80     Parse *pParse,     /* The parsing context */
    81     81     SrcList *pTabList, /* Table into which we are inserting */
    82     82     Upsert *pUpsert    /* The ON CONFLICT clauses */
    83     83   ){
    84         -  NameContext sNC;
    85         -  Table *pTab;
    86         -  Index *pIdx;
    87         -  ExprList *pTarget;
    88         -  Expr *pTerm;
    89         -  Expr sCol[2];
    90         -  int rc;
           84  +  Table *pTab;            /* That table into which we are inserting */
           85  +  int rc;                 /* Result code */
           86  +  int iCursor;            /* Cursor used by pTab */
           87  +  Index *pIdx;            /* One of the indexes of pTab */
           88  +  ExprList *pTarget;      /* The conflict-target clause */
           89  +  Expr *pTerm;            /* One term of the conflict-target clause */
           90  +  NameContext sNC;        /* Context for resolving symbolic names */
           91  +  Expr sCol[2];           /* Index column converted into an Expr */
    91     92   
    92     93     assert( pTabList->nSrc==1 );
    93     94     assert( pTabList->a[0].pTab!=0 );
    94     95     assert( pUpsert!=0 );
    95     96     assert( pUpsert->pUpsertTarget!=0 );
    96     97   
    97     98     /* Resolve all symbolic names in the conflict-target clause, which
................................................................................
   105    106     if( rc ) return rc;
   106    107     rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
   107    108     if( rc ) return rc;
   108    109   
   109    110     /* Check to see if the conflict target matches the rowid. */  
   110    111     pTab = pTabList->a[0].pTab;
   111    112     pTarget = pUpsert->pUpsertTarget;
          113  +  iCursor = pTabList->a[0].iCursor;
   112    114     if( HasRowid(pTab) 
   113    115      && pTarget->nExpr==1
   114    116      && (pTerm = pTarget->a[0].pExpr)->op==TK_COLUMN
   115    117      && (pTerm->iColumn==XN_ROWID || pTerm->iColumn==pTab->iPKey)
   116    118     ){
   117    119       /* The conflict-target is the rowid of the primary table */
   118    120       assert( pUpsert->pUpsertIdx==0 );
................................................................................
   135    137     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   136    138       int ii, jj, nn;
   137    139       if( !IsUniqueIndex(pIdx) ) continue;
   138    140       if( pTarget->nExpr!=pIdx->nKeyCol ) continue;
   139    141       if( pIdx->pPartIdxWhere ){
   140    142         if( pUpsert->pUpsertTargetWhere==0 ) continue;
   141    143         if( sqlite3ExprCompare(pParse, pUpsert->pUpsertTargetWhere,
   142         -                             pIdx->pPartIdxWhere, pTabList->a[0].iCursor)!=0 ){
          144  +                             pIdx->pPartIdxWhere, iCursor)!=0 ){
   143    145           continue;
   144    146         }
   145    147       }
   146    148       nn = pIdx->nKeyCol;
   147    149       for(ii=0; ii<nn; ii++){
   148    150         Expr *pExpr;
   149    151         if( pIdx->aiColumn[ii]==XN_EXPR ){
................................................................................
   152    154           pExpr = pIdx->aColExpr->a[ii].pExpr;
   153    155         }else{
   154    156           sCol[1].iColumn = pIdx->aiColumn[ii];
   155    157           sCol[0].u.zToken = (char*)pIdx->azColl[ii];
   156    158           pExpr = &sCol[0];
   157    159         }
   158    160         for(jj=0; jj<nn; jj++){
   159         -        if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,
   160         -                               pTabList->a[0].iCursor)<2
   161         -        ){
          161  +        if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr,iCursor)<2 ){
   162    162             break;  /* Column ii of the index matches column jj of target */
   163    163           }
   164    164         }
   165    165         if( jj>=nn ){
   166    166           /* The target contains no match for column jj of the index */
   167    167           break;
   168    168         }

Changes to test/upsert1.test.

    40     40     SELECT * FROM t1;
    41     41   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    42     42   breakpoint
    43     43   do_catchsql_test upsert1-130 {
    44     44     INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE nocase) DO NOTHING;
    45     45     SELECT * FROM t1;
    46     46   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           47  +do_execsql_test upsert1-140 {
           48  +  DELETE FROM t1;
           49  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
           50  +  SELECT * FROM t1;
           51  +} {5 6 0}
    47     52   
    48     53   do_catchsql_test upsert1-200 {
    49     54     DROP INDEX t1x1;
    50     55     DELETE FROM t1;
    51     56     CREATE UNIQUE INDEX t1x1 ON t1(a||b);
    52     57     INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||b) DO NOTHING;
    53     58     SELECT * FROM t1;
................................................................................
    54     59   } {0 {5 6 0}}
    55     60   do_catchsql_test upsert1-210 {
    56     61     DELETE FROM t1;
    57     62     INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(a||+b) DO NOTHING;
    58     63     SELECT * FROM t1;
    59     64   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
    60     65   
           66  +do_catchsql_test upsert1-300 {
           67  +  DROP INDEX t1x1;
           68  +  DELETE FROM t1;
           69  +  CREATE UNIQUE INDEX t1x1 ON t1(b) WHERE b>10;
           70  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) DO NOTHING;
           71  +  SELECT * FROM t1;
           72  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           73  +do_catchsql_test upsert1-310 {
           74  +  DELETE FROM t1;
           75  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b!=10 DO NOTHING;
           76  +  SELECT * FROM t1;
           77  +} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           78  +do_execsql_test upsert1-320 {
           79  +  DELETE FROM t1;
           80  +  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b) WHERE b>10 DO NOTHING;
           81  +  SELECT * FROM t1;
           82  +} {5 6 0}
    61     83   
    62     84   finish_test