SQLite4
Check-in [2dc7b22339]
Not logged in

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

Overview
Comment:Fix for compound EXCEPT queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2dc7b223393f1b8a84d657dfc54f8aaede7a5e4a
User & Date: dan 2012-04-23 09:40:36
Context
2012-04-23
11:45
Fix a bug in CREATE INDEX where the number of columns in the index is fewer than the number of columns in the primary key. check-in: b0ac144cf5 user: drh tags: trunk
09:40
Fix for compound EXCEPT queries. check-in: 2dc7b22339 user: dan tags: trunk
2012-04-21
19:59
Additional comments on the key encoder. Remove an unused variable. check-in: fdaed18ddf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

632
633
634
635
636
637
638
639
640
641
642



643


644

645
646
647
648
649
650
651
      sqlite4VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
      sqlite4VdbeAddOp3(v, OP_IdxInsert, iParm, r1, r2);
      sqlite4ReleaseTempReg(pParse, r1);
      sqlite4ReleaseTempReg(pParse, r2);
      break;
    }

    /* Construct a record from the query result, but instead of
    ** saving that record, use it as a key to delete elements from
    ** the temporary table iParm.
    */



    case SRT_Except: {


      sqlite4VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);

      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:







|
|
|
|
>
>
>

>
>
|
>







632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
      sqlite4VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
      sqlite4VdbeAddOp3(v, OP_IdxInsert, iParm, r1, r2);
      sqlite4ReleaseTempReg(pParse, r1);
      sqlite4ReleaseTempReg(pParse, r2);
      break;
    }

    /* This is used for processing queries of the form:
    **
    **     <select-1> EXCEPT <select-2>
    **
    ** Temporary index iParm contains the results of <select-1>. This
    ** code is processing the results of <select-2>. For each row of
    ** <select-2>, remove any identical row from iParm.  */
    case SRT_Except: {
      int regKey = sqlite4GetTempReg(pParse);
      sqlite4VdbeAddOp4Int(v, OP_MakeIdxKey, iParm, regResult, regKey, 0);
      sqlite4VdbeAddOp3(v, OP_IdxDelete, iParm, 0, regKey);
      sqlite4ReleaseTempReg(pParse, regKey);
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:

Changes to src/vdbe.c.

3813
3814
3815
3816
3817
3818
3819
3820
3821
3822

















3823
3824
3825
3826
3827
3828
3829

  break;
}

/* Opcode: IdxDelete P1 * P3 * *
**
** P1 is a cursor open on a database index. P3 contains a key suitable for
** the index. Delete P3 from P1.
*/
case OP_IdxDelete: {

















  break;
}

/* Opcode: IdxRowid P1 P2 * * *
**
** Write into register P2 an integer which is the last entry in the record at
** the end of the index key pointed to by cursor P1.  This integer should be







|


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







3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846

  break;
}

/* Opcode: IdxDelete P1 * P3 * *
**
** P1 is a cursor open on a database index. P3 contains a key suitable for
** the index. Delete P3 from P1 if it is present.
*/
case OP_IdxDelete: {
  VdbeCursor *pC;
  Mem *pKey;

  pC = p->apCsr[pOp->p1];
  pKey = &aMem[pOp->p3];

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pC && pC->pKVCur && pC->pKVCur->pStore );
  assert( pKey->flags & MEM_Blob );

  rc = sqlite4KVCursorSeek(pC->pKVCur, pKey->z, pKey->n, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite4KVCursorDelete(pC->pKVCur);
  }else if( rc==SQLITE_NOTFOUND ){
    rc = SQLITE_OK;
  }

  break;
}

/* Opcode: IdxRowid P1 P2 * * *
**
** Write into register P2 an integer which is the last entry in the record at
** the end of the index key pointed to by cursor P1.  This integer should be

Changes to test/simple.test.

836
837
838
839
840
841
842
843
























844
845

do_execsql_test 44.3 {
  SELECT y FROM t1 ORDER BY y;
} [lsort -real $lVal]
do_execsql_test 44.4 {
  SELECT count(*) FROM t1;
} {60}

























finish_test









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


836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869

do_execsql_test 44.3 {
  SELECT y FROM t1 ORDER BY y;
} [lsort -real $lVal]
do_execsql_test 44.4 {
  SELECT count(*) FROM t1;
} {60}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 45.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);

  INSERT INTO t2 VALUES(3,   6,  9);
  INSERT INTO t2 VALUES(12, 15, 18);
  INSERT INTO t2 VALUES(21, 24, 27);
}

do_execsql_test 45.2 {
  SELECT d FROM t2 EXCEPT SELECT d FROM t2;
} {}

do_execsql_test 45.3 {
  SELECT d FROM t2 
  EXCEPT 
  SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
} {}



finish_test