/ Check-in [5bf70425]
Login
Home Timeline Branches Tags Tickets Wiki More...

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 Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c. 

4895
4896
4897
4898
4899
4900
4901


4902
4903

4904
4905
4906
4907
4908
4909
4910

      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 ){
      return pA->op==TK_COLLATE ? 1 : 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;








>
>

<
>








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
84
85
86

87
88
89
90

91
92
93
94
95
96
97
...
105
106
107
108
109
110
111

112
113
114
115
116
117
118
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168

** is wrong.
*/
int sqlite3UpsertAnalyzeTarget(
  Parse *pParse,     /* The parsing context */
  SrcList *pTabList, /* Table into which we are inserting */
  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 );

  /* Resolve all symbolic names in the conflict-target clause, which
................................................................................
  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 );
................................................................................
  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 ){
................................................................................
        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;
      }








|
|
|
>
|
<
|
|
>







 







>







 







|







 







|
<
<








77
78
79
80
81
82
83
84
85
86
87
88

89
90
91
92
93
94
95
96
97
98
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
...
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
...
154
155
156
157
158
159
160
161


162
163
164
165
166
167
168

** 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
................................................................................
  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 );
................................................................................
  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 ){
................................................................................
        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

This page was generated in about 0.011s by Fossil 2.11 [eee161e823] 2020-01-22 19:08:54