SQLite

Check-in [83b1e163ef]
Login

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

Overview
Comment:Update this branch with latest trunk changes.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | server-edition
Files: files | file ages | folders
SHA3-256: 83b1e163ef6ac01d252268415f26a2a999670de583491becc091d294075980bc
User & Date: dan 2017-05-01 20:07:59.204
Context
2017-05-06
16:04
Update this branch with latest trunk changes. (check-in: ed6bad67f5 user: dan tags: server-edition)
2017-05-01
20:07
Update this branch with latest trunk changes. (check-in: 83b1e163ef user: dan tags: server-edition)
18:24
Enhance "PRAGMA integrity_check" to detect duplicate rowids within a leaf page. (check-in: adcad37b00 user: dan tags: trunk)
2017-04-29
15:48
Avoid reading the file-size before page 1 is read-locked. (check-in: f6b025bf63 user: dan tags: server-edition)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/btree.c.
8444
8445
8446
8447
8448
8449
8450

8451
8452
8453
8454
8455
8456
8457
      return SQLITE_OK;
    }
    dropCell(pPage, idx, info.nSize, &rc);
    if( rc ) goto end_insert;
  }else if( loc<0 && pPage->nCell>0 ){
    assert( pPage->leaf );
    idx = ++pCur->ix;

  }else{
    assert( pPage->leaf );
  }
  insertCell(pPage, idx, newCell, szNew, 0, 0, &rc);
  assert( pPage->nOverflow==0 || rc==SQLITE_OK );
  assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 );








>







8444
8445
8446
8447
8448
8449
8450
8451
8452
8453
8454
8455
8456
8457
8458
      return SQLITE_OK;
    }
    dropCell(pPage, idx, info.nSize, &rc);
    if( rc ) goto end_insert;
  }else if( loc<0 && pPage->nCell>0 ){
    assert( pPage->leaf );
    idx = ++pCur->ix;
    pCur->curFlags &= ~BTCF_ValidNKey;
  }else{
    assert( pPage->leaf );
  }
  insertCell(pPage, idx, newCell, szNew, 0, 0, &rc);
  assert( pPage->nOverflow==0 || rc==SQLITE_OK );
  assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 );

9568
9569
9570
9571
9572
9573
9574

9575
9576
9577
9578
9579
9580
9581

    /* Check for integer primary key out of range */
    if( pPage->intKey ){
      if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){
        checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey);
      }
      maxKey = info.nKey;

    }

    /* Check the content overflow list */
    if( info.nPayload>info.nLocal ){
      int nPage;       /* Number of pages on the overflow chain */
      Pgno pgnoOvfl;   /* First page of the overflow chain */
      assert( pc + info.nSize - 4 <= usableSize );







>







9569
9570
9571
9572
9573
9574
9575
9576
9577
9578
9579
9580
9581
9582
9583

    /* Check for integer primary key out of range */
    if( pPage->intKey ){
      if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){
        checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey);
      }
      maxKey = info.nKey;
      keyCanBeEqual = 0;     /* Only the first key on the page may ==maxKey */
    }

    /* Check the content overflow list */
    if( info.nPayload>info.nLocal ){
      int nPage;       /* Number of pages on the overflow chain */
      Pgno pgnoOvfl;   /* First page of the overflow chain */
      assert( pc + info.nSize - 4 <= usableSize );
Changes to src/select.c.
5031
5032
5033
5034
5035
5036
5037




5038
5039
5040
5041
5042
5043
5044
    ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
    ** for example.  In that case, do not regenerate the code to manifest
    ** a view or the co-routine to implement a view.  The first instance
    ** is sufficient, though the subroutine to manifest the view does need
    ** to be invoked again. */
    if( pItem->addrFillSub ){
      if( pItem->fg.viaCoroutine==0 ){




        sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
      }
      continue;
    }

    /* Increment Parse.nHeight by the height of the largest expression
    ** tree referred to by this, the parent select. The child select







>
>
>
>







5031
5032
5033
5034
5035
5036
5037
5038
5039
5040
5041
5042
5043
5044
5045
5046
5047
5048
    ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
    ** for example.  In that case, do not regenerate the code to manifest
    ** a view or the co-routine to implement a view.  The first instance
    ** is sufficient, though the subroutine to manifest the view does need
    ** to be invoked again. */
    if( pItem->addrFillSub ){
      if( pItem->fg.viaCoroutine==0 ){
        /* The subroutine that manifests the view might be a one-time routine,
        ** or it might need to be rerun on each iteration because it
        ** encodes a correlated subquery. */
        testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
        sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
      }
      continue;
    }

    /* Increment Parse.nHeight by the height of the largest expression
    ** tree referred to by this, the parent select. The child select
Changes to src/vdbe.c.
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
** to the current line should be indented for EXPLAIN output.
*/
case OP_Goto: {             /* jump */
jump_to_p2_and_check_for_interrupt:
  pOp = &aOp[pOp->p2 - 1];

  /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev,
  ** OP_VNext, OP_RowSetNext, or OP_SorterNext) all jump here upon
  ** completion.  Check to see if sqlite3_interrupt() has been called
  ** or if the progress callback needs to be invoked. 
  **
  ** This code uses unstructured "goto" statements and does not look clean.
  ** But that is not due to sloppy coding habits. The code is written this
  ** way for performance, to avoid having to run the interrupt and progress
  ** checks on every opcode.  This helps sqlite3_step() to run about 1.5%







|







760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
** to the current line should be indented for EXPLAIN output.
*/
case OP_Goto: {             /* jump */
jump_to_p2_and_check_for_interrupt:
  pOp = &aOp[pOp->p2 - 1];

  /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev,
  ** OP_VNext, or OP_SorterNext) all jump here upon
  ** completion.  Check to see if sqlite3_interrupt() has been called
  ** or if the progress callback needs to be invoked. 
  **
  ** This code uses unstructured "goto" statements and does not look clean.
  ** But that is not due to sloppy coding habits. The code is written this
  ** way for performance, to avoid having to run the interrupt and progress
  ** checks on every opcode.  This helps sqlite3_step() to run about 1.5%
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
arithmetic_result_is_null:
  sqlite3VdbeMemSetNull(pOut);
  break;
}

/* Opcode: CollSeq P1 * * P4
**
** P4 is a pointer to a CollSeq struct. If the next call to a user function
** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will
** be returned. This is used by the built-in min(), max() and nullif()
** functions.
**
** If P1 is not zero, then it is a register that a subsequent min() or
** max() aggregate will set to 1 if the current row is not the minimum or
** maximum.  The P1 register is initialized to 0 by this instruction.







|







1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
arithmetic_result_is_null:
  sqlite3VdbeMemSetNull(pOut);
  break;
}

/* Opcode: CollSeq P1 * * P4
**
** P4 is a pointer to a CollSeq object. If the next call to a user function
** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will
** be returned. This is used by the built-in min(), max() and nullif()
** functions.
**
** If P1 is not zero, then it is a register that a subsequent min() or
** max() aggregate will set to 1 if the current row is not the minimum or
** maximum.  The P1 register is initialized to 0 by this instruction.
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
#ifndef SQLITE_OMIT_CAST
/* Opcode: Cast P1 P2 * * *
** Synopsis: affinity(r[P1])
**
** Force the value in register P1 to be the type defined by P2.
** 
** <ul>
** <li value="97"> TEXT
** <li value="98"> BLOB
** <li value="99"> NUMERIC
** <li value="100"> INTEGER
** <li value="101"> REAL
** </ul>
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_Cast: {                  /* in1 */
  assert( pOp->p2>=SQLITE_AFF_BLOB && pOp->p2<=SQLITE_AFF_REAL );
  testcase( pOp->p2==SQLITE_AFF_TEXT );







|
|
|
|
|







1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
#ifndef SQLITE_OMIT_CAST
/* Opcode: Cast P1 P2 * * *
** Synopsis: affinity(r[P1])
**
** Force the value in register P1 to be the type defined by P2.
** 
** <ul>
** <li> P2=='A' &rarr; BLOB
** <li> P2=='B' &rarr; TEXT
** <li> P2=='C' &rarr; NUMERIC
** <li> P2=='D' &rarr; INTEGER
** <li> P2=='E' &rarr; REAL
** </ul>
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_Cast: {                  /* in1 */
  assert( pOp->p2>=SQLITE_AFF_BLOB && pOp->p2<=SQLITE_AFF_REAL );
  testcase( pOp->p2==SQLITE_AFF_TEXT );
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
}

/* Opcode: Affinity P1 P2 * P4 *
** Synopsis: affinity(r[P1@P2])
**
** Apply affinities to a range of P2 registers starting with P1.
**
** P4 is a string that is P2 characters long. The nth character of the
** string indicates the column affinity that should be used for the nth
** memory cell in the range.
*/
case OP_Affinity: {
  const char *zAffinity;   /* The affinity to be applied */

  zAffinity = pOp->p4.z;
  assert( zAffinity!=0 );







|
|







2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
}

/* Opcode: Affinity P1 P2 * P4 *
** Synopsis: affinity(r[P1@P2])
**
** Apply affinities to a range of P2 registers starting with P1.
**
** P4 is a string that is P2 characters long. The N-th character of the
** string indicates the column affinity that should be used for the N-th
** memory cell in the range.
*/
case OP_Affinity: {
  const char *zAffinity;   /* The affinity to be applied */

  zAffinity = pOp->p4.z;
  assert( zAffinity!=0 );
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
/* Opcode: MakeRecord P1 P2 P3 P4 *
** Synopsis: r[P3]=mkrec(r[P1@P2])
**
** Convert P2 registers beginning with P1 into the [record format]
** use as a data record in a database table or as a key
** in an index.  The OP_Column opcode can decode the record later.
**
** P4 may be a string that is P2 characters long.  The nth character of the
** string indicates the column affinity that should be used for the nth
** field of the index key.
**
** The mapping from character to affinity is given by the SQLITE_AFF_
** macros defined in sqliteInt.h.
**
** If P4 is NULL then all index fields have the affinity BLOB.
*/







|
|







2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
/* Opcode: MakeRecord P1 P2 P3 P4 *
** Synopsis: r[P3]=mkrec(r[P1@P2])
**
** Convert P2 registers beginning with P1 into the [record format]
** use as a data record in a database table or as a key
** in an index.  The OP_Column opcode can decode the record later.
**
** P4 may be a string that is P2 characters long.  The N-th character of the
** string indicates the column affinity that should be used for the N-th
** field of the index key.
**
** The mapping from character to affinity is given by the SQLITE_AFF_
** macros defined in sqliteInt.h.
**
** If P4 is NULL then all index fields have the affinity BLOB.
*/
5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
5756
5757
5758
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771

5772
5773
5774
5775
5776
5777
5778
5779
  break;
}
#endif /* SQLITE_OMIT_INTEGRITY_CHECK */

/* Opcode: RowSetAdd P1 P2 * * *
** Synopsis: rowset(P1)=r[P2]
**
** Insert the integer value held by register P2 into a boolean index
** held in register P1.
**
** An assertion fails if P2 is not an integer.
*/
case OP_RowSetAdd: {       /* in1, in2 */
  pIn1 = &aMem[pOp->p1];
  pIn2 = &aMem[pOp->p2];
  assert( (pIn2->flags & MEM_Int)!=0 );
  if( (pIn1->flags & MEM_RowSet)==0 ){
    sqlite3VdbeMemSetRowSet(pIn1);
    if( (pIn1->flags & MEM_RowSet)==0 ) goto no_mem;
  }
  sqlite3RowSetInsert(pIn1->u.pRowSet, pIn2->u.i);
  break;
}

/* Opcode: RowSetRead P1 P2 P3 * *
** Synopsis: r[P3]=rowset(P1)
**
** Extract the smallest value from boolean index P1 and put that value into

** register P3.  Or, if boolean index P1 is initially empty, leave P3
** unchanged and jump to instruction P2.
*/
case OP_RowSetRead: {       /* jump, in1, out3 */
  i64 val;

  pIn1 = &aMem[pOp->p1];
  if( (pIn1->flags & MEM_RowSet)==0 







|



















|
>
|







5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
5756
5757
5758
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771
5772
5773
5774
5775
5776
5777
5778
5779
5780
  break;
}
#endif /* SQLITE_OMIT_INTEGRITY_CHECK */

/* Opcode: RowSetAdd P1 P2 * * *
** Synopsis: rowset(P1)=r[P2]
**
** Insert the integer value held by register P2 into a RowSet object
** held in register P1.
**
** An assertion fails if P2 is not an integer.
*/
case OP_RowSetAdd: {       /* in1, in2 */
  pIn1 = &aMem[pOp->p1];
  pIn2 = &aMem[pOp->p2];
  assert( (pIn2->flags & MEM_Int)!=0 );
  if( (pIn1->flags & MEM_RowSet)==0 ){
    sqlite3VdbeMemSetRowSet(pIn1);
    if( (pIn1->flags & MEM_RowSet)==0 ) goto no_mem;
  }
  sqlite3RowSetInsert(pIn1->u.pRowSet, pIn2->u.i);
  break;
}

/* Opcode: RowSetRead P1 P2 P3 * *
** Synopsis: r[P3]=rowset(P1)
**
** Extract the smallest value from the RowSet object in P1
** and put that value into register P3.
** Or, if RowSet object P1 is initially empty, leave P3
** unchanged and jump to instruction P2.
*/
case OP_RowSetRead: {       /* jump, in1, out3 */
  i64 val;

  pIn1 = &aMem[pOp->p1];
  if( (pIn1->flags & MEM_RowSet)==0 
5796
5797
5798
5799
5800
5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
**
** Register P3 is assumed to hold a 64-bit integer value. If register P1
** contains a RowSet object and that RowSet object contains
** the value held in P3, jump to register P2. Otherwise, insert the
** integer in P3 into the RowSet and continue on to the
** next opcode.
**
** The RowSet object is optimized for the case where successive sets
** of integers, where each set contains no duplicates. Each set
** of values is identified by a unique P4 value. The first set
** must have P4==0, the final set P4=-1.  P4 must be either -1 or
** non-negative.  For non-negative values of P4 only the lower 4
** bits are significant.
**
** This allows optimizations: (a) when P4==0 there is no need to test
** the rowset object for P3, as it is guaranteed not to contain it,
** (b) when P4==-1 there is no need to insert the value, as it will
** never be tested for, and (c) when a value that is part of set X is
** inserted, there is no need to search to see if the same value was
** previously inserted as part of set X (only if it was previously
** inserted as part of some other set).
*/
case OP_RowSetTest: {                     /* jump, in1, in3 */







|
|
|
|
<
|


|







5797
5798
5799
5800
5801
5802
5803
5804
5805
5806
5807

5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
**
** Register P3 is assumed to hold a 64-bit integer value. If register P1
** contains a RowSet object and that RowSet object contains
** the value held in P3, jump to register P2. Otherwise, insert the
** integer in P3 into the RowSet and continue on to the
** next opcode.
**
** The RowSet object is optimized for the case where sets of integers
** are inserted in distinct phases, which each set contains no duplicates.
** Each set is identified by a unique P4 value. The first set
** must have P4==0, the final set must have P4==-1, and for all other sets

** must have P4>0.
**
** This allows optimizations: (a) when P4==0 there is no need to test
** the RowSet object for P3, as it is guaranteed not to contain it,
** (b) when P4==-1 there is no need to insert the value, as it will
** never be tested for, and (c) when a value that is part of set X is
** inserted, there is no need to search to see if the same value was
** previously inserted as part of set X (only if it was previously
** inserted as part of some other set).
*/
case OP_RowSetTest: {                     /* jump, in1, in3 */
Changes to src/wherecode.c.
1125
1126
1127
1128
1129
1130
1131


1132
1133
1134
1135
1136
1137
1138
  Vdbe *v;                        /* The prepared stmt under constructions */
  struct SrcList_item *pTabItem;  /* FROM clause term being coded */
  int addrBrk;                    /* Jump here to break out of the loop */
  int addrHalt;                   /* addrBrk for the outermost loop */
  int addrCont;                   /* Jump here to continue with next cycle */
  int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
  int iReleaseReg = 0;      /* Temp register to free before returning */



  pParse = pWInfo->pParse;
  v = pParse->pVdbe;
  pWC = &pWInfo->sWC;
  db = pParse->db;
  pLevel = &pWInfo->a[iLevel];
  pLoop = pLevel->pWLoop;







>
>







1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
  Vdbe *v;                        /* The prepared stmt under constructions */
  struct SrcList_item *pTabItem;  /* FROM clause term being coded */
  int addrBrk;                    /* Jump here to break out of the loop */
  int addrHalt;                   /* addrBrk for the outermost loop */
  int addrCont;                   /* Jump here to continue with next cycle */
  int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
  int iReleaseReg = 0;      /* Temp register to free before returning */
  Index *pIdx = 0;          /* Index used by loop (if any) */
  int loopAgain;            /* True if constraint generator loop should repeat */

  pParse = pWInfo->pParse;
  v = pParse->pVdbe;
  pWC = &pWInfo->sWC;
  db = pParse->db;
  pLevel = &pWInfo->a[iLevel];
  pLoop = pLevel->pWLoop;
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
    int regBase;                 /* Base register holding constraint values */
    WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
    WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
    int startEq;                 /* True if range start uses ==, >= or <= */
    int endEq;                   /* True if range end uses ==, >= or <= */
    int start_constraints;       /* Start of range is constrained */
    int nConstraint;             /* Number of constraint terms */
    Index *pIdx;                 /* The index we will be using */
    int iIdxCur;                 /* The VDBE cursor for the index */
    int nExtraReg = 0;           /* Number of extra registers needed */
    int op;                      /* Instruction opcode */
    char *zStartAff;             /* Affinity for start of range constraint */
    char *zEndAff = 0;           /* Affinity for end of range constraint */
    u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
    u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */







<







1452
1453
1454
1455
1456
1457
1458

1459
1460
1461
1462
1463
1464
1465
    int regBase;                 /* Base register holding constraint values */
    WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
    WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
    int startEq;                 /* True if range start uses ==, >= or <= */
    int endEq;                   /* True if range end uses ==, >= or <= */
    int start_constraints;       /* Start of range is constrained */
    int nConstraint;             /* Number of constraint terms */

    int iIdxCur;                 /* The VDBE cursor for the index */
    int nExtraReg = 0;           /* Number of extra registers needed */
    int op;                      /* Instruction opcode */
    char *zStartAff;             /* Affinity for start of range constraint */
    char *zEndAff = 0;           /* Affinity for end of range constraint */
    u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
    u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */
1701
1702
1703
1704
1705
1706
1707

1708
1709
1710
1711
1712
1713
1714
    pLevel->p1 = iIdxCur;
    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }else{
      assert( pLevel->p5==0 );
    }

  }else

#ifndef SQLITE_OMIT_OR_OPTIMIZATION
  if( pLoop->wsFlags & WHERE_MULTI_OR ){
    /* Case 5:  Two or more separately indexed terms connected by OR
    **
    ** Example:







>







1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
    pLevel->p1 = iIdxCur;
    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }else{
      assert( pLevel->p5==0 );
    }
    if( omitTable ) pIdx = 0;
  }else

#ifndef SQLITE_OMIT_OR_OPTIMIZATION
  if( pLoop->wsFlags & WHERE_MULTI_OR ){
    /* Case 5:  Two or more separately indexed terms connected by OR
    **
    ** Example:
2018
2019
2020
2021
2022
2023
2024





2025


2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042




2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061


2062
2063
2064
2065
2066
2067
2068

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
#endif

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.





  */


  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    int skipLikeAddr = 0;
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
    testcase( pTerm->wtFlags & TERM_CODED );
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
      testcase( pWInfo->untestedTerms==0
               && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
      pWInfo->untestedTerms = 1;
      continue;
    }
    pE = pTerm->pExpr;
    assert( pE!=0 );
    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
      continue;
    }




    if( pTerm->wtFlags & TERM_LIKECOND ){
      /* If the TERM_LIKECOND flag is set, that means that the range search
      ** is sufficient to guarantee that the LIKE operator is true, so we
      ** can skip the call to the like(A,B) function.  But this only works
      ** for strings.  So do not skip the call to the function on the pass
      ** that compares BLOBs. */
#ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
      continue;
#else
      u32 x = pLevel->iLikeRepCntr;
      assert( x>0 );
      skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)? OP_IfNot : OP_If, (int)(x>>1));
      VdbeCoverage(v);
#endif
    }
    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
    pTerm->wtFlags |= TERM_CODED;
  }



  /* Insert code to test for implied constraints based on transitivity
  ** of the "==" operator.
  **
  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
  ** and we are coding the t1 loop and the t2 loop has not yet coded,
  ** then we cannot use the "t1.a=t2.b" constraint, but we can code







>
>
>
>
>

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

|

|
|
|
|

|
|
|
|
|
>
>







2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
#endif

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  **
  ** This loop may run either once (pIdx==0) or twice (pIdx!=0). If
  ** it is run twice, then the first iteration codes those sub-expressions
  ** that can be computed using columns from pIdx only (without seeking
  ** the main table cursor). 
  */
  do{
    loopAgain = 0;
    for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
      Expr *pE;
      int skipLikeAddr = 0;
      testcase( pTerm->wtFlags & TERM_VIRTUAL );
      testcase( pTerm->wtFlags & TERM_CODED );
      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
      if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
        testcase( pWInfo->untestedTerms==0
            && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
        pWInfo->untestedTerms = 1;
        continue;
      }
      pE = pTerm->pExpr;
      assert( pE!=0 );
      if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
        continue;
      }
      if( pIdx && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
        loopAgain = 1;
        continue;
      }
      if( pTerm->wtFlags & TERM_LIKECOND ){
        /* If the TERM_LIKECOND flag is set, that means that the range search
        ** is sufficient to guarantee that the LIKE operator is true, so we
        ** can skip the call to the like(A,B) function.  But this only works
        ** for strings.  So do not skip the call to the function on the pass
        ** that compares BLOBs. */
#ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
        continue;
#else
        u32 x = pLevel->iLikeRepCntr;
        assert( x>0 );
        skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)?OP_IfNot:OP_If, (int)(x>>1));
        VdbeCoverage(v);
#endif
      }
      sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
      if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
      pTerm->wtFlags |= TERM_CODED;
    }
    pIdx = 0;
  }while( loopAgain );

  /* Insert code to test for implied constraints based on transitivity
  ** of the "==" operator.
  **
  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
  ** and we are coding the t1 loop and the t2 loop has not yet coded,
  ** then we cannot use the "t1.a=t2.b" constraint, but we can code
Changes to test/conflict3.test.
15
16
17
18
19
20
21

22
23
24
25
26
27
28
29
30
31
32
33
34
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353













354
355
356
#
# This file focuses on making sure that combinations of REPLACE,
# IGNORE, and FAIL conflict resolution play well together.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable !conflict {
  finish_test
  return
}

do_execsql_test conflict-1.1 {
  CREATE TABLE t1(
    a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-1.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-1.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-1.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete the tests above, but this time on a table non-INTEGER primary key.
#
do_execsql_test conflict-2.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-2.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-2.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-2.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete again on a WITHOUT ROWID table.
#
do_execsql_test conflict-3.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-3.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-3.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-3.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-4.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-4.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-4.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-4.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-5.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-5.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-5.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-5.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test conflict-6.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    b UNIQUE ON CONFLICT IGNORE
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-6.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-6.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-6.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-7.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-7.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-7.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-7.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-8.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-8.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-8.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-8.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-9.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-9.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-9.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-9.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-10.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c INTEGER PRIMARY KEY ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-10.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-10.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-10.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test conflict-11.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c PRIMARY KEY ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test conflict-11.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test conflict-11.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test conflict-11.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}















finish_test







>






|











|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|





|












|







|


|


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



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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
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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
#
# This file focuses on making sure that combinations of REPLACE,
# IGNORE, and FAIL conflict resolution play well together.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix conflict3

ifcapable !conflict {
  finish_test
  return
}

do_execsql_test 1.1 {
  CREATE TABLE t1(
    a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 1.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 1.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 1.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete the tests above, but this time on a table non-INTEGER primary key.
#
do_execsql_test 2.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 2.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 2.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 2.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Replete again on a WITHOUT ROWID table.
#
do_execsql_test 3.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a INT PRIMARY KEY ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 3.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 3.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 3.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test 4.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 4.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 4.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 4.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test 5.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    b UNIQUE ON CONFLICT IGNORE,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 5.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 5.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 5.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Arrange the table rows in a different order and repeat.
#
do_execsql_test 6.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    c UNIQUE ON CONFLICT FAIL,
    a INT PRIMARY KEY ON CONFLICT REPLACE,
    b UNIQUE ON CONFLICT IGNORE
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 6.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 6.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 6.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test 7.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 7.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 7.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 7.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test 8.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 8.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 8.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 8.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test 9.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b INT PRIMARY KEY ON CONFLICT IGNORE,
    c UNIQUE ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 9.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 9.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 9.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test 10.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c INTEGER PRIMARY KEY ON CONFLICT FAIL
  );
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 10.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 10.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 10.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Change which column is the PRIMARY KEY
#
do_execsql_test 11.1 {
  DROP TABLE t1;
  CREATE TABLE t1(
    a UNIQUE ON CONFLICT REPLACE, 
    b UNIQUE ON CONFLICT IGNORE,
    c PRIMARY KEY ON CONFLICT FAIL
  ) WITHOUT ROWID;
  INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert a row that conflicts on column B.  The insert should be ignored.
#
do_execsql_test 11.2 {
  INSERT INTO t1(a,b,c) VALUES(3,2,5);
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4}

# Insert two rows where the second conflicts on C.  The first row show go
# and and then there should be a constraint error.
#
do_test 11.3 {
  catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
} {1 {UNIQUE constraint failed: t1.c}}
do_execsql_test 11.4 {
  SELECT a,b,c FROM t1 ORDER BY a;
} {1 2 3 2 3 4 4 5 6}

# Check that ticket [f68dc596c4] has been fixed.
#
do_execsql_test 12.1 {
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t2 VALUES(111, '111');
}
do_execsql_test 12.2 {
  REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B');
}
do_execsql_test 12.3 {
  SELECT * FROM t2;
} {111 111B 112 112}


finish_test
Added test/pushdown.test.






















































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
# 2017 April 29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix pushdown

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(1, 'b1', 'c1');
  INSERT INTO t1 VALUES(2, 'b2', 'c2');
  INSERT INTO t1 VALUES(3, 'b3', 'c3');
  INSERT INTO t1 VALUES(4, 'b4', 'c4');
  CREATE INDEX i1 ON t1(a, c);
}

proc f {val} {
  lappend ::L $val
  return 0
}
db func f f 

do_test 1.1 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  set L
} {c2}

do_test 1.2 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  set L
} {c3}

do_execsql_test 1.3 {
  DROP INDEX i1;
  CREATE INDEX i1 ON t1(a, b);
}
do_test 1.4 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) }
  set L
} {b2}

do_test 1.5 {
  set L [list]
  execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) }
  set L
} {b3}
  
finish_test