SQLite

Check-in [98d32ba661]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved conflict-target matching logic.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | upsert
Files: files | file ages | folders
SHA3-256: 98d32ba661f4ba662b639994b74352b695d53a33bc8a498bd09b9e02f794c81e
User & Date: drh 2018-04-13 13:44:48.035
Context
2018-04-13
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)
13:06
Back off of the extended upsert syntax that allows multiple ON CONFLICT clauses. The syntax now is exactly as in PostgreSQL and MySQL. Add support for WHERE clauses on the conflict-target phrase, for partial indexes. (check-in: 2c1b1987d8 user: drh tags: upsert)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/upsert.c.
︙︙
82
83
84
85
86
87
88

89
90
91
92
93
94
95

  Upsert *pUpsert    /* The ON CONFLICT clauses */
){
  NameContext sNC;
  Table *pTab;
  Index *pIdx;
  ExprList *pTarget;
  Expr *pTerm;

  int rc;

  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );
  assert( pUpsert!=0 );
  assert( pUpsert->pUpsertTarget!=0 );









>








82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

  Upsert *pUpsert    /* The ON CONFLICT clauses */
){
  NameContext sNC;
  Table *pTab;
  Index *pIdx;
  ExprList *pTarget;
  Expr *pTerm;
  Expr sCol[2];
  int rc;

  assert( pTabList->nSrc==1 );
  assert( pTabList->a[0].pTab!=0 );
  assert( pUpsert!=0 );
  assert( pUpsert->pUpsertTarget!=0 );
︙︙
113
114
115
116
117
118
119












120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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

   && (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 );
    return SQLITE_OK;
  }













  /* Check for matches against other indexes */
  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, pTabList->a[0].iCursor)!=0 ){
        continue;
      }
    }
    nn = pIdx->nKeyCol;
    for(ii=0; ii<nn; ii++){
      if( pIdx->aiColumn[ii]!=XN_EXPR ){
        for(jj=0; jj<nn; jj++){
          if( pTarget->a[jj].pExpr->op!=TK_COLUMN ) continue;
          if( pTarget->a[jj].pExpr->iColumn!=pIdx->aiColumn[ii] ) continue;
          break;
        }
      }else{
        Expr *pExpr;

        assert( pIdx->aColExpr!=0 );
        assert( pIdx->aColExpr->nExpr>ii );
        pExpr = pIdx->aColExpr->a[ii].pExpr;





        for(jj=0; jj<nn; jj++){
          if( sqlite3ExprCompare(pParse, pTarget->a[jj].pExpr, pExpr, -1)==0 ){


            break;
          }
        }



      }
      if( jj<nn ) break;
    }



    if( ii>=nn ) continue;

    pUpsert->pUpsertIdx = pIdx;
    return SQLITE_OK;
  }
  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
                          "PRIMARY KEY or UNIQUE constraint");
  return SQLITE_ERROR;
}

#endif /* SQLITE_OMIT_UPSERT */








>
>
>
>
>
>
>
>
>
>
>
>















<
<
<
<
<
<
<
|
>



>
>
>
>
>
|
|
>
>
|
|
|
>
>
>

<

>
>
>
|
>










114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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

169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

   && (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 );
    return SQLITE_OK;
  }

  /* Initialize sCol[0..1] to be an expression parse tree for a
  ** single column of an index.  The sCol[0] node will be the TK_COLLATE
  ** operator and sCol[1] will be the TK_COLUMN operator.  Code below
  ** will populate the specific collation and column number values
  ** prior to comparing against the conflict-target expression.
  */
  memset(sCol, 0, sizeof(sCol));
  sCol[0].op = TK_COLLATE;
  sCol[0].pLeft = &sCol[1];
  sCol[1].op = TK_COLUMN;
  sCol[1].iTable = pTabList->a[0].iCursor;

  /* Check for matches against other indexes */
  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, pTabList->a[0].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,
                               pTabList->a[0].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;
      }

    }
    if( ii<nn ){
      /* Column ii of the index did not match any term of the conflict target.
      ** Continue the search with the next index. */
      continue;
    }
    pUpsert->pUpsertIdx = pIdx;
    return SQLITE_OK;
  }
  sqlite3ErrorMsg(pParse, "ON CONFLICT clause does not match any "
                          "PRIMARY KEY or UNIQUE constraint");
  return SQLITE_ERROR;
}

#endif /* SQLITE_OMIT_UPSERT */
Changes to test/upsert1.test.
︙︙
35
36
37
38
39
40
41

42
43
44
45
46
47
48
49
50
51
52
53
54





55
56
57

  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
  SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
  SELECT * FROM t1;
} {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}

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}}

if 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}}





}

finish_test








>





<






|
>
>
>
>
>
|



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
61
62

  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(x) DO NOTHING;
  SELECT * FROM t1;
} {1 {no such column: x}}
do_catchsql_test upsert1-120 {
  INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(c) DO NOTHING;
  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