/ Check-in [4678cb10]
Login

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

Overview
Comment:Fix the OP_OpenEphemeral opcode in the bytecode engine so that if it is called a second or subsequent time, it merely clears the existing table rather than creating a new one. Proposed fix for ticket [d0866b26f83e9c55e30de0821f5d].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:4678cb1044f0b4dc813e48f3bd0f85240a66e2ecf8763280d66726cc031c93a7
User & Date: drh 2018-12-31 16:36:42
References
2019-01-01
13:02 Open ticket [d0866b26]: Window function in correlated subquery causes assertion fault plus 4 other changes artifact: 55b13ed5 user: drh
Context
2018-12-31
17:58
Small changes to the OP_OpenEphemeral opcode to improve testability. check-in: f856676c user: drh tags: trunk
16:36
Fix the OP_OpenEphemeral opcode in the bytecode engine so that if it is called a second or subsequent time, it merely clears the existing table rather than creating a new one. Proposed fix for ticket [d0866b26f83e9c55e30de0821f5d]. check-in: 4678cb10 user: drh tags: trunk
2018-12-29
14:23
Additional small performance increase and size reduction to the sqlite3VdbeMakeLabel() mechanism. check-in: 1bdee199 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

  3623   3623   /* Opcode: OpenEphemeral P1 P2 * P4 P5
  3624   3624   ** Synopsis: nColumn=P2
  3625   3625   **
  3626   3626   ** Open a new cursor P1 to a transient table.
  3627   3627   ** The cursor is always opened read/write even if 
  3628   3628   ** the main database is read-only.  The ephemeral
  3629   3629   ** table is deleted automatically when the cursor is closed.
         3630  +**
         3631  +** If the cursor P1 is already opened on an ephermal table, the table
         3632  +** is cleared (all content is erased).
  3630   3633   **
  3631   3634   ** P2 is the number of columns in the ephemeral table.
  3632   3635   ** The cursor points to a BTree table if P4==0 and to a BTree index
  3633   3636   ** if P4 is not 0.  If P4 is not NULL, it points to a KeyInfo structure
  3634   3637   ** that defines the format of keys in the index.
  3635   3638   **
  3636   3639   ** The P5 parameter can be a mask of the BTREE_* flags defined
................................................................................
  3655   3658         SQLITE_OPEN_READWRITE |
  3656   3659         SQLITE_OPEN_CREATE |
  3657   3660         SQLITE_OPEN_EXCLUSIVE |
  3658   3661         SQLITE_OPEN_DELETEONCLOSE |
  3659   3662         SQLITE_OPEN_TRANSIENT_DB;
  3660   3663     assert( pOp->p1>=0 );
  3661   3664     assert( pOp->p2>=0 );
         3665  +  pCx = p->apCsr[pOp->p1];
         3666  +  if( pCx ){
         3667  +    /* If the ephermeral table is already open, erase all existing content
         3668  +    ** so that the table is empty again, rather than creating a new table. */
         3669  +    rc = sqlite3BtreeClearTable(pCx->pBtx, pCx->pgnoRoot, 0);
         3670  +    if( rc ) goto abort_due_to_error;
         3671  +    break;
         3672  +  }
  3662   3673     pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, CURTYPE_BTREE);
  3663   3674     if( pCx==0 ) goto no_mem;
  3664   3675     pCx->nullRow = 1;
  3665   3676     pCx->isEphemeral = 1;
  3666   3677     rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBtx, 
  3667   3678                           BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags);
  3668   3679     if( rc==SQLITE_OK ){
................................................................................
  3671   3682     if( rc==SQLITE_OK ){
  3672   3683       /* If a transient index is required, create it by calling
  3673   3684       ** sqlite3BtreeCreateTable() with the BTREE_BLOBKEY flag before
  3674   3685       ** opening it. If a transient table is required, just use the
  3675   3686       ** automatically created table with root-page 1 (an BLOB_INTKEY table).
  3676   3687       */
  3677   3688       if( (pCx->pKeyInfo = pKeyInfo = pOp->p4.pKeyInfo)!=0 ){
  3678         -      int pgno;
  3679   3689         assert( pOp->p4type==P4_KEYINFO );
  3680         -      rc = sqlite3BtreeCreateTable(pCx->pBtx, &pgno, BTREE_BLOBKEY | pOp->p5); 
         3690  +      rc = sqlite3BtreeCreateTable(pCx->pBtx, (int*)&pCx->pgnoRoot,
         3691  +                                   BTREE_BLOBKEY | pOp->p5); 
  3681   3692         if( rc==SQLITE_OK ){
  3682         -        assert( pgno==MASTER_ROOT+1 );
         3693  +        assert( pCx->pgnoRoot==MASTER_ROOT+1 );
  3683   3694           assert( pKeyInfo->db==db );
  3684   3695           assert( pKeyInfo->enc==ENC(db) );
  3685         -        rc = sqlite3BtreeCursor(pCx->pBtx, pgno, BTREE_WRCSR,
         3696  +        rc = sqlite3BtreeCursor(pCx->pBtx, pCx->pgnoRoot, BTREE_WRCSR,
  3686   3697                                   pKeyInfo, pCx->uc.pCursor);
  3687   3698         }
  3688   3699         pCx->isTable = 0;
  3689   3700       }else{
         3701  +      pCx->pgnoRoot = MASTER_ROOT;
  3690   3702         rc = sqlite3BtreeCursor(pCx->pBtx, MASTER_ROOT, BTREE_WRCSR,
  3691   3703                                 0, pCx->uc.pCursor);
  3692   3704         pCx->isTable = 1;
  3693   3705       }
  3694   3706     }
  3695   3707     if( rc ) goto abort_due_to_error;
  3696   3708     pCx->isOrdered = (pOp->p5!=BTREE_UNORDERED);

Changes to test/window1.test.

   616    616     SELECT y, y+1, y+2 FROM (
   617    617       SELECT c IN (
   618    618         SELECT (row_number() OVER()) FROM t1
   619    619       ) AS y FROM t2
   620    620     );
   621    621   } {1 2 3}
   622    622   
          623  +# 2018-12-31
          624  +# https://www.sqlite.org/src/info/d0866b26f83e9c55
          625  +# Window function in correlated subquery causes assertion fault 
          626  +#
          627  +do_catchsql_test 15.0 {
          628  +  WITH t(id, parent) AS (
          629  +  SELECT CAST(1 AS INT), CAST(NULL AS INT)
          630  +  UNION ALL
          631  +  SELECT 2, NULL
          632  +  UNION ALL
          633  +  SELECT 3, 1
          634  +  UNION ALL
          635  +  SELECT 4, 1
          636  +  UNION ALL
          637  +  SELECT 5, 2
          638  +  UNION ALL
          639  +  SELECT 6, 2
          640  +  ), q AS (
          641  +  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
          642  +    FROM t
          643  +   WHERE parent IS NULL
          644  +   UNION ALL
          645  +  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
          646  +    FROM q
          647  +    JOIN t
          648  +      ON t.parent = q.id
          649  +  )
          650  +  SELECT *
          651  +    FROM q;
          652  +} {1 {cannot use window functions in recursive queries}}
          653  +do_execsql_test 15.1 {
          654  +  DROP TABLE IF EXISTS t1;
          655  +  DROP TABLE IF EXISTS t2;
          656  +  CREATE TABLE t1(x);
          657  +  INSERT INTO t1 VALUES('a'), ('b'), ('c');
          658  +  CREATE TABLE t2(a, b);
          659  +  INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3);
          660  +  SELECT x, (
          661  +    SELECT sum(b)
          662  +      OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING
          663  +                                    AND UNBOUNDED FOLLOWING)
          664  +    FROM t2 WHERE b<x
          665  +  ) FROM t1;
          666  +} {a 3 b 3 c 3}
          667  +
   623    668   finish_test