/ Check-in [a120c9235f]
Login

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

Overview
Comment:Add the "VALUES-as-coroutine" optimization. Large VALUES clauses on an INSERT, for example, prepare and run in about half the time and with half the memory. This check-in also includes enhancements to the internal sqlite3ExprIsConstant() routine to recognize pure SQL functions as constant if they have constant arguments.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a120c9235f125e05be494038c16a9dd326fd79837698bef17e7879cd0cd75831
User & Date: drh 2024-03-18 18:00:17
References
2024-03-23
15:17
Fix an adverse interaction between CREATE TABLE AS and the new VALUES-as-coroutine optimization. dbsqlfuzz c2c5e7e08b7e489d270a26d895077a03f678c33b (check-in: 84b6fdea0b user: drh tags: trunk)
Context
2024-03-18
18:03
Remove unnecessary blank lines from build commands in the default Makefile.in. (check-in: 76fb3a908f user: drh tags: trunk)
18:00
Add the "VALUES-as-coroutine" optimization. Large VALUES clauses on an INSERT, for example, prepare and run in about half the time and with half the memory. This check-in also includes enhancements to the internal sqlite3ExprIsConstant() routine to recognize pure SQL functions as constant if they have constant arguments. (check-in: a120c9235f user: drh tags: trunk)
17:13
Fix harmless compiler (scan-build) warnings. (Closed-Leaf check-in: c86f9f2a15 user: drh tags: exp-values-clause2)
2024-03-15
17:57
New options for testrunner.tcl: --stop-on-error and --stop-on-coredump. (check-in: 82035b9cfd user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

214
215
216
217
218
219
220
221
222
223


224
225
226
227
228
229
230
Expr *sqlite3ExprSkipCollateAndLikely(Expr *pExpr){
  while( pExpr && ExprHasProperty(pExpr, EP_Skip|EP_Unlikely) ){
    if( ExprHasProperty(pExpr, EP_Unlikely) ){
      assert( ExprUseXList(pExpr) );
      assert( pExpr->x.pList->nExpr>0 );
      assert( pExpr->op==TK_FUNCTION );
      pExpr = pExpr->x.pList->a[0].pExpr;
    }else{
      assert( pExpr->op==TK_COLLATE );
      pExpr = pExpr->pLeft;


    }
  }  
  return pExpr;
}

/*
** Return the collation sequence for the expression pExpr. If







<
|

>
>







214
215
216
217
218
219
220

221
222
223
224
225
226
227
228
229
230
231
Expr *sqlite3ExprSkipCollateAndLikely(Expr *pExpr){
  while( pExpr && ExprHasProperty(pExpr, EP_Skip|EP_Unlikely) ){
    if( ExprHasProperty(pExpr, EP_Unlikely) ){
      assert( ExprUseXList(pExpr) );
      assert( pExpr->x.pList->nExpr>0 );
      assert( pExpr->op==TK_FUNCTION );
      pExpr = pExpr->x.pList->a[0].pExpr;

    }else if( pExpr->op==TK_COLLATE ){
      pExpr = pExpr->pLeft;
    }else{
      break;
    }
  }  
  return pExpr;
}

/*
** Return the collation sequence for the expression pExpr. If
1865
1866
1867
1868
1869
1870
1871

1872
1873
1874
1875
1876
1877
1878
    pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
    pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
    pNewItem->fg = pOldItem->fg;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->addrFillSub = pOldItem->addrFillSub;
    pNewItem->regReturn = pOldItem->regReturn;

    if( pNewItem->fg.isIndexedBy ){
      pNewItem->u1.zIndexedBy = sqlite3DbStrDup(db, pOldItem->u1.zIndexedBy);
    }
    pNewItem->u2 = pOldItem->u2;
    if( pNewItem->fg.isCte ){
      pNewItem->u2.pCteUse->nUse++;
    }







>







1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
    pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase);
    pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName);
    pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
    pNewItem->fg = pOldItem->fg;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->addrFillSub = pOldItem->addrFillSub;
    pNewItem->regReturn = pOldItem->regReturn;
    pNewItem->regResult = pOldItem->regResult;
    if( pNewItem->fg.isIndexedBy ){
      pNewItem->u1.zIndexedBy = sqlite3DbStrDup(db, pOldItem->u1.zIndexedBy);
    }
    pNewItem->u2 = pOldItem->u2;
    if( pNewItem->fg.isCte ){
      pNewItem->u2.pCteUse->nUse++;
    }
2341
2342
2343
2344
2345
2346
2347
















































2348
2349
2350
2351
2352
2353
2354
    }else if( ExprAlwaysTrue(pRight) || ExprAlwaysFalse(pLeft) ){
      pExpr = pExpr->op==TK_AND ? pLeft : pRight;
    }
  }
  return pExpr;
}


















































/*
** These routines are Walker callbacks used to check expressions to
** see if they are "constant" for some definition of constant.  The
** Walker.eCode value determines the type of "constant" we are looking
** for.
**







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







2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
    }else if( ExprAlwaysTrue(pRight) || ExprAlwaysFalse(pLeft) ){
      pExpr = pExpr->op==TK_AND ? pLeft : pRight;
    }
  }
  return pExpr;
}

/*
** pExpr is a TK_FUNCTION node.  Try to determine whether or not the
** function is a constant function.  A function is constant if all of
** the following are true:
**
**    (1)  It is a scalar function (not an aggregate or window function)
**    (2)  It has either the SQLITE_FUNC_CONSTANT or SQLITE_FUNC_SLOCHNG
**         property.
**    (3)  All of its arguments are constants
**
** This routine sets pWalker->eCode to 0 if pExpr is not a constant.
** It makes no changes to pWalker->eCode if pExpr is constant.  In
** every case, it returns WRC_Abort.
**
** Called as a service subroutine from exprNodeIsConstant().
*/
static SQLITE_NOINLINE int exprNodeIsConstantFunction(
  Walker *pWalker,
  Expr *pExpr
){
  int n;             /* Number of arguments */
  ExprList *pList;   /* List of arguments */
  FuncDef *pDef;     /* The function */
  sqlite3 *db;       /* The database */

  assert( pExpr->op==TK_FUNCTION );
  if( ExprHasProperty(pExpr, EP_TokenOnly)
   || (pList = pExpr->x.pList)==0
  ){;
    n = 0;
  }else{
    n = pList->nExpr;
    sqlite3WalkExprList(pWalker, pList);
    if( pWalker->eCode==0 ) return WRC_Abort;
  }
  db = pWalker->pParse->db;
  pDef = sqlite3FindFunction(db, pExpr->u.zToken, n, ENC(db), 0);
  if( pDef==0
   || pDef->xFinalize!=0
   || (pDef->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_SLOCHNG))==0
   || ExprHasProperty(pExpr, EP_WinFunc)
  ){
    pWalker->eCode = 0;
    return WRC_Abort;
  }
  return WRC_Continue;
}


/*
** These routines are Walker callbacks used to check expressions to
** see if they are "constant" for some definition of constant.  The
** Walker.eCode value determines the type of "constant" we are looking
** for.
**
2369
2370
2371
2372
2373
2374
2375

2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394


2395
2396
2397
2398
2399
2400
2401
** an error for new statements, but is silently converted
** to NULL for existing schemas.  This allows sqlite_schema tables that
** contain a bound parameter because they were generated by older versions
** of SQLite to be parsed by newer versions of SQLite without raising a
** malformed schema error.
*/
static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){


  /* If pWalker->eCode is 2 then any term of the expression that comes from
  ** the ON or USING clauses of an outer join disqualifies the expression
  ** from being considered constant. */
  if( pWalker->eCode==2 && ExprHasProperty(pExpr, EP_OuterON) ){
    pWalker->eCode = 0;
    return WRC_Abort;
  }

  switch( pExpr->op ){
    /* Consider functions to be constant if all their arguments are constant
    ** and either pWalker->eCode==4 or 5 or the function has the
    ** SQLITE_FUNC_CONST flag. */
    case TK_FUNCTION:
      if( (pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_ConstFunc))
       && !ExprHasProperty(pExpr, EP_WinFunc)
      ){
        if( pWalker->eCode==5 ) ExprSetProperty(pExpr, EP_FromDDL);
        return WRC_Continue;


      }else{
        pWalker->eCode = 0;
        return WRC_Abort;
      }
    case TK_ID:
      /* Convert "true" or "false" in a DEFAULT clause into the
      ** appropriate TK_TRUEFALSE operator */







>



















>
>







2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
** an error for new statements, but is silently converted
** to NULL for existing schemas.  This allows sqlite_schema tables that
** contain a bound parameter because they were generated by older versions
** of SQLite to be parsed by newer versions of SQLite without raising a
** malformed schema error.
*/
static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){
  assert( pWalker->eCode>0 );

  /* If pWalker->eCode is 2 then any term of the expression that comes from
  ** the ON or USING clauses of an outer join disqualifies the expression
  ** from being considered constant. */
  if( pWalker->eCode==2 && ExprHasProperty(pExpr, EP_OuterON) ){
    pWalker->eCode = 0;
    return WRC_Abort;
  }

  switch( pExpr->op ){
    /* Consider functions to be constant if all their arguments are constant
    ** and either pWalker->eCode==4 or 5 or the function has the
    ** SQLITE_FUNC_CONST flag. */
    case TK_FUNCTION:
      if( (pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_ConstFunc))
       && !ExprHasProperty(pExpr, EP_WinFunc)
      ){
        if( pWalker->eCode==5 ) ExprSetProperty(pExpr, EP_FromDDL);
        return WRC_Continue;
      }else if( pWalker->pParse ){
        return exprNodeIsConstantFunction(pWalker, pExpr);
      }else{
        pWalker->eCode = 0;
        return WRC_Abort;
      }
    case TK_ID:
      /* Convert "true" or "false" in a DEFAULT clause into the
      ** appropriate TK_TRUEFALSE operator */
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449

2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466






2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
      /* no break */ deliberate_fall_through
    default:
      testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail() disallows */
      testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail() disallows */
      return WRC_Continue;
  }
}
static int exprIsConst(Expr *p, int initFlag, int iCur){
  Walker w;
  w.eCode = initFlag;

  w.xExprCallback = exprNodeIsConstant;
  w.xSelectCallback = sqlite3SelectWalkFail;
#ifdef SQLITE_DEBUG
  w.xSelectCallback2 = sqlite3SelectWalkAssert2;
#endif
  w.u.iCur = iCur;
  sqlite3WalkExpr(&w, p);
  return w.eCode;
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
** and 0 if it involves variables or function calls.
**
** For the purposes of this function, a double-quoted string (ex: "abc")
** is considered a variable but a single-quoted string (ex: 'abc') is
** a constant.






*/
int sqlite3ExprIsConstant(Expr *p){
  return exprIsConst(p, 1, 0);
}

/*
** Walk an expression tree.  Return non-zero if
**
**   (1) the expression is constant, and
**   (2) the expression does originate in the ON or USING clause
**       of a LEFT JOIN, and
**   (3) the expression does not contain any EP_FixedCol TK_COLUMN
**       operands created by the constant propagation optimization.
**
** When this routine returns true, it indicates that the expression
** can be added to the pParse->pConstExpr list and evaluated once when
** the prepared statement starts up.  See sqlite3ExprCodeRunJustOnce().
*/
int sqlite3ExprIsConstantNotJoin(Expr *p){
  return exprIsConst(p, 2, 0);
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
** for any single row of the table with cursor iCur.  In other words, the
** expression must not refer to any non-deterministic function nor any
** table other than iCur.
*/
int sqlite3ExprIsTableConstant(Expr *p, int iCur){
  return exprIsConst(p, 3, iCur);
}

/*
** Check pExpr to see if it is an constraint on the single data source
** pSrc = &pSrcList->a[iSrc].  In other words, check to see if pExpr
** constrains pSrc but does not depend on any other tables or data
** sources anywhere else in the query.  Return true (non-zero) if pExpr







|


>

















>
>
>
>
>
>

|
|















|
|









|







2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
      /* no break */ deliberate_fall_through
    default:
      testcase( pExpr->op==TK_SELECT ); /* sqlite3SelectWalkFail() disallows */
      testcase( pExpr->op==TK_EXISTS ); /* sqlite3SelectWalkFail() disallows */
      return WRC_Continue;
  }
}
static int exprIsConst(Parse *pParse, Expr *p, int initFlag, int iCur){
  Walker w;
  w.eCode = initFlag;
  w.pParse = pParse;
  w.xExprCallback = exprNodeIsConstant;
  w.xSelectCallback = sqlite3SelectWalkFail;
#ifdef SQLITE_DEBUG
  w.xSelectCallback2 = sqlite3SelectWalkAssert2;
#endif
  w.u.iCur = iCur;
  sqlite3WalkExpr(&w, p);
  return w.eCode;
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
** and 0 if it involves variables or function calls.
**
** For the purposes of this function, a double-quoted string (ex: "abc")
** is considered a variable but a single-quoted string (ex: 'abc') is
** a constant.
**
** The pParse parameter may be NULL.  But if it is NULL, there is no way
** to determine if function calls are constant or not, and hence all
** function calls will be considered to be non-constant.  If pParse is
** not NULL, then a function call might be constant, depending on the
** function and on its parameters.
*/
int sqlite3ExprIsConstant(Parse *pParse, Expr *p){
  return exprIsConst(pParse, p, 1, 0);
}

/*
** Walk an expression tree.  Return non-zero if
**
**   (1) the expression is constant, and
**   (2) the expression does originate in the ON or USING clause
**       of a LEFT JOIN, and
**   (3) the expression does not contain any EP_FixedCol TK_COLUMN
**       operands created by the constant propagation optimization.
**
** When this routine returns true, it indicates that the expression
** can be added to the pParse->pConstExpr list and evaluated once when
** the prepared statement starts up.  See sqlite3ExprCodeRunJustOnce().
*/
static int sqlite3ExprIsConstantNotJoin(Parse *pParse, Expr *p){
  return exprIsConst(pParse, p, 2, 0);
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
** for any single row of the table with cursor iCur.  In other words, the
** expression must not refer to any non-deterministic function nor any
** table other than iCur.
*/
int sqlite3ExprIsTableConstant(Expr *p, int iCur){
  return exprIsConst(0, p, 3, iCur);
}

/*
** Check pExpr to see if it is an constraint on the single data source
** pSrc = &pSrcList->a[iSrc].  In other words, check to see if pExpr
** constrains pSrc but does not depend on any other tables or data
** sources anywhere else in the query.  Return true (non-zero) if pExpr
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
**
** For the purposes of this function, a double-quoted string (ex: "abc")
** is considered a variable but a single-quoted string (ex: 'abc') is
** a constant.
*/
int sqlite3ExprIsConstantOrFunction(Expr *p, u8 isInit){
  assert( isInit==0 || isInit==1 );
  return exprIsConst(p, 4+isInit, 0);
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/*
** Walk an expression tree.  Return 1 if the expression contains a
** subquery of some kind.  Return 0 if there are no subqueries.
*/







|







2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
**
** For the purposes of this function, a double-quoted string (ex: "abc")
** is considered a variable but a single-quoted string (ex: 'abc') is
** a constant.
*/
int sqlite3ExprIsConstantOrFunction(Expr *p, u8 isInit){
  assert( isInit==0 || isInit==1 );
  return exprIsConst(0, p, 4+isInit, 0);
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/*
** Walk an expression tree.  Return 1 if the expression contains a
** subquery of some kind.  Return 0 if there are no subqueries.
*/
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911


#ifndef SQLITE_OMIT_SUBQUERY
/*
** The argument is an IN operator with a list (not a subquery) on the
** right-hand side.  Return TRUE if that list is constant.
*/
static int sqlite3InRhsIsConstant(Expr *pIn){
  Expr *pLHS;
  int res;
  assert( !ExprHasProperty(pIn, EP_xIsSelect) );
  pLHS = pIn->pLeft;
  pIn->pLeft = 0;
  res = sqlite3ExprIsConstant(pIn);
  pIn->pLeft = pLHS;
  return res;
}
#endif

/*
** This function is used by the implementation of the IN (...) operator.







|





|







2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971


#ifndef SQLITE_OMIT_SUBQUERY
/*
** The argument is an IN operator with a list (not a subquery) on the
** right-hand side.  Return TRUE if that list is constant.
*/
static int sqlite3InRhsIsConstant(Parse *pParse, Expr *pIn){
  Expr *pLHS;
  int res;
  assert( !ExprHasProperty(pIn, EP_xIsSelect) );
  pLHS = pIn->pLeft;
  pIn->pLeft = 0;
  res = sqlite3ExprIsConstant(pParse, pIn);
  pIn->pLeft = pLHS;
  return res;
}
#endif

/*
** This function is used by the implementation of the IN (...) operator.
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
  ** and the RHS is not constant or has two or fewer terms,
  ** then it is not worth creating an ephemeral table to evaluate
  ** the IN operator so return IN_INDEX_NOOP.
  */
  if( eType==0
   && (inFlags & IN_INDEX_NOOP_OK)
   && ExprUseXList(pX)
   && (!sqlite3InRhsIsConstant(pX) || pX->x.pList->nExpr<=2)
  ){
    pParse->nTab--;  /* Back out the allocation of the unused cursor */
    iTab = -1;       /* Cursor is not allocated */
    eType = IN_INDEX_NOOP;
  }

  if( eType==0 ){







|







3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
  ** and the RHS is not constant or has two or fewer terms,
  ** then it is not worth creating an ephemeral table to evaluate
  ** the IN operator so return IN_INDEX_NOOP.
  */
  if( eType==0
   && (inFlags & IN_INDEX_NOOP_OK)
   && ExprUseXList(pX)
   && (!sqlite3InRhsIsConstant(pParse,pX) || pX->x.pList->nExpr<=2)
  ){
    pParse->nTab--;  /* Back out the allocation of the unused cursor */
    iTab = -1;       /* Cursor is not allocated */
    eType = IN_INDEX_NOOP;
  }

  if( eType==0 ){
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
      Expr *pE2 = pItem->pExpr;

      /* If the expression is not constant then we will need to
      ** disable the test that was generated above that makes sure
      ** this code only executes once.  Because for a non-constant
      ** expression we need to rerun this code each time.
      */
      if( addrOnce && !sqlite3ExprIsConstant(pE2) ){
        sqlite3VdbeChangeToNoop(v, addrOnce-1);
        sqlite3VdbeChangeToNoop(v, addrOnce);
        ExprClearProperty(pExpr, EP_Subrtn);
        addrOnce = 0;
      }

      /* Evaluate the expression and insert it into the temp table */







|







3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
      Expr *pE2 = pItem->pExpr;

      /* If the expression is not constant then we will need to
      ** disable the test that was generated above that makes sure
      ** this code only executes once.  Because for a non-constant
      ** expression we need to rerun this code each time.
      */
      if( addrOnce && !sqlite3ExprIsConstant(pParse, pE2) ){
        sqlite3VdbeChangeToNoop(v, addrOnce-1);
        sqlite3VdbeChangeToNoop(v, addrOnce);
        ExprClearProperty(pExpr, EP_Subrtn);
        addrOnce = 0;
      }

      /* Evaluate the expression and insert it into the temp table */
4786
4787
4788
4789
4790
4791
4792
4793


4794
4795
4796
4797
4798
4799
4800

#ifndef SQLITE_OMIT_WINDOWFUNC
      if( ExprHasProperty(pExpr, EP_WinFunc) ){
        return pExpr->y.pWin->regResult;
      }
#endif

      if( ConstFactorOk(pParse) && sqlite3ExprIsConstantNotJoin(pExpr) ){


        /* SQL functions can be expensive. So try to avoid running them
        ** multiple times if we know they always give the same result */
        return sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
      }
      assert( !ExprHasProperty(pExpr, EP_TokenOnly) );
      assert( ExprUseXList(pExpr) );
      pFarg = pExpr->x.pList;







|
>
>







4846
4847
4848
4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862

#ifndef SQLITE_OMIT_WINDOWFUNC
      if( ExprHasProperty(pExpr, EP_WinFunc) ){
        return pExpr->y.pWin->regResult;
      }
#endif

      if( ConstFactorOk(pParse)
       && sqlite3ExprIsConstantNotJoin(pParse,pExpr)
      ){
        /* SQL functions can be expensive. So try to avoid running them
        ** multiple times if we know they always give the same result */
        return sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
      }
      assert( !ExprHasProperty(pExpr, EP_TokenOnly) );
      assert( ExprUseXList(pExpr) );
      pFarg = pExpr->x.pList;
4817
4818
4819
4820
4821
4822
4823
4824
4825
4826
4827
4828
4829
4830
4831
        return exprCodeInlineFunction(pParse, pFarg,
             SQLITE_PTR_TO_INT(pDef->pUserData), target);
      }else if( pDef->funcFlags & (SQLITE_FUNC_DIRECT|SQLITE_FUNC_UNSAFE) ){
        sqlite3ExprFunctionUsable(pParse, pExpr, pDef);
      }

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){
          testcase( i==31 );
          constMask |= MASKBIT32(i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
        }
      }







|







4879
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890
4891
4892
4893
        return exprCodeInlineFunction(pParse, pFarg,
             SQLITE_PTR_TO_INT(pDef->pUserData), target);
      }else if( pDef->funcFlags & (SQLITE_FUNC_DIRECT|SQLITE_FUNC_UNSAFE) ){
        sqlite3ExprFunctionUsable(pParse, pExpr, pDef);
      }

      for(i=0; i<nFarg; i++){
        if( i<32 && sqlite3ExprIsConstant(pParse, pFarg->a[i].pExpr) ){
          testcase( i==31 );
          constMask |= MASKBIT32(i);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){
          pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr);
        }
      }
5284
5285
5286
5287
5288
5289
5290
5291
5292
5293
5294
5295
5296
5297
5298
*/
int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){
  int r2;
  pExpr = sqlite3ExprSkipCollateAndLikely(pExpr);
  if( ConstFactorOk(pParse)
   && ALWAYS(pExpr!=0)
   && pExpr->op!=TK_REGISTER
   && sqlite3ExprIsConstantNotJoin(pExpr)
  ){
    *pReg  = 0;
    r2 = sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
  }else{
    int r1 = sqlite3GetTempReg(pParse);
    r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1);
    if( r2==r1 ){







|







5346
5347
5348
5349
5350
5351
5352
5353
5354
5355
5356
5357
5358
5359
5360
*/
int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){
  int r2;
  pExpr = sqlite3ExprSkipCollateAndLikely(pExpr);
  if( ConstFactorOk(pParse)
   && ALWAYS(pExpr!=0)
   && pExpr->op!=TK_REGISTER
   && sqlite3ExprIsConstantNotJoin(pParse, pExpr)
  ){
    *pReg  = 0;
    r2 = sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
  }else{
    int r1 = sqlite3GetTempReg(pParse);
    r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1);
    if( r2==r1 ){
5348
5349
5350
5351
5352
5353
5354
5355
5356
5357
5358
5359
5360
5361
5362
/*
** Generate code that will evaluate expression pExpr and store the
** results in register target.  The results are guaranteed to appear
** in register target.  If the expression is constant, then this routine
** might choose to code the expression at initialization time.
*/
void sqlite3ExprCodeFactorable(Parse *pParse, Expr *pExpr, int target){
  if( pParse->okConstFactor && sqlite3ExprIsConstantNotJoin(pExpr) ){
    sqlite3ExprCodeRunJustOnce(pParse, pExpr, target);
  }else{
    sqlite3ExprCodeCopy(pParse, pExpr, target);
  }
}

/*







|







5410
5411
5412
5413
5414
5415
5416
5417
5418
5419
5420
5421
5422
5423
5424
/*
** Generate code that will evaluate expression pExpr and store the
** results in register target.  The results are guaranteed to appear
** in register target.  If the expression is constant, then this routine
** might choose to code the expression at initialization time.
*/
void sqlite3ExprCodeFactorable(Parse *pParse, Expr *pExpr, int target){
  if( pParse->okConstFactor && sqlite3ExprIsConstantNotJoin(pParse,pExpr) ){
    sqlite3ExprCodeRunJustOnce(pParse, pExpr, target);
  }else{
    sqlite3ExprCodeCopy(pParse, pExpr, target);
  }
}

/*
5407
5408
5409
5410
5411
5412
5413
5414
5415
5416
5417
5418
5419
5420
5421
      if( flags & SQLITE_ECEL_OMITREF ){
        i--;
        n--;
      }else{
        sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i);
      }
    }else if( (flags & SQLITE_ECEL_FACTOR)!=0
           && sqlite3ExprIsConstantNotJoin(pExpr)
    ){
      sqlite3ExprCodeRunJustOnce(pParse, pExpr, target+i);
    }else{
      int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i);
      if( inReg!=target+i ){
        VdbeOp *pOp;
        if( copyOp==OP_Copy







|







5469
5470
5471
5472
5473
5474
5475
5476
5477
5478
5479
5480
5481
5482
5483
      if( flags & SQLITE_ECEL_OMITREF ){
        i--;
        n--;
      }else{
        sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i);
      }
    }else if( (flags & SQLITE_ECEL_FACTOR)!=0
           && sqlite3ExprIsConstantNotJoin(pParse,pExpr)
    ){
      sqlite3ExprCodeRunJustOnce(pParse, pExpr, target+i);
    }else{
      int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i);
      if( inReg!=target+i ){
        VdbeOp *pOp;
        if( copyOp==OP_Copy

Changes to src/insert.c.

573
574
575
576
577
578
579



























































































































































































580
581
582
583
584
585
586
** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
** above are all no-ops
*/
# define autoIncBegin(A,B,C) (0)
# define autoIncStep(A,B,C)
#endif /* SQLITE_OMIT_AUTOINCREMENT */





























































































































































































/* Forward declaration */
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */







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







573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
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
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
** above are all no-ops
*/
# define autoIncBegin(A,B,C) (0)
# define autoIncStep(A,B,C)
#endif /* SQLITE_OMIT_AUTOINCREMENT */

/*
** If argument pVal is a Select object returned by an sqlite3MultiValues()
** that was able to use the co-routine optimization, finish coding the
** co-routine.
*/
void sqlite3MultiValuesEnd(Parse *pParse, Select *pVal){
  if( ALWAYS(pVal) && pVal->pSrc->nSrc>0 ){
    SrcItem *pItem = &pVal->pSrc->a[0];
    sqlite3VdbeEndCoroutine(pParse->pVdbe, pItem->regReturn);
    sqlite3VdbeJumpHere(pParse->pVdbe, pItem->addrFillSub - 1);
  }
}

/*
** Return true if all expressions in the expression-list passed as the
** only argument are constant.
*/
static int exprListIsConstant(Parse *pParse, ExprList *pRow){
  int ii;
  for(ii=0; ii<pRow->nExpr; ii++){
    if( 0==sqlite3ExprIsConstant(pParse, pRow->a[ii].pExpr) ) return 0;
  }
  return 1;
}

/*
** Return true if all expressions in the expression-list passed as the
** only argument are both constant and have no affinity.
*/
static int exprListIsNoAffinity(Parse *pParse, ExprList *pRow){
  int ii;
  if( exprListIsConstant(pParse,pRow)==0 ) return 0;
  for(ii=0; ii<pRow->nExpr; ii++){
    assert( pRow->a[ii].pExpr->affExpr==0 );
    if( 0!=sqlite3ExprAffinity(pRow->a[ii].pExpr) ) return 0;
  }
  return 1;

}

/*
** This function is called by the parser for the second and subsequent
** rows of a multi-row VALUES clause. Argument pLeft is the part of
** the VALUES clause already parsed, argument pRow is the vector of values
** for the new row. The Select object returned represents the complete
** VALUES clause, including the new row.
**
** There are two ways in which this may be achieved - by incremental 
** coding of a co-routine (the "co-routine" method) or by returning a
** Select object equivalent to the following (the "UNION ALL" method):
**
**        "pLeft UNION ALL SELECT pRow"
**
** If the VALUES clause contains a lot of rows, this compound Select
** object may consume a lot of memory.
**
** When the co-routine method is used, each row that will be returned
** by the VALUES clause is coded into part of a co-routine as it is 
** passed to this function. The returned Select object is equivalent to:
**
**     SELECT * FROM (
**       Select object to read co-routine
**     )
**
** The co-routine method is used in most cases. Exceptions are:
**
**    a) If the current statement has a WITH clause. This is to avoid
**       statements like:
**
**            WITH cte AS ( VALUES('x'), ('y') ... )
**            SELECT * FROM cte AS a, cte AS b;
**
**       This will not work, as the co-routine uses a hard-coded register
**       for its OP_Yield instructions, and so it is not possible for two
**       cursors to iterate through it concurrently.
**
**    b) The schema is currently being parsed (i.e. the VALUES clause is part 
**       of a schema item like a VIEW or TRIGGER). In this case there is no VM
**       being generated when parsing is taking place, and so generating 
**       a co-routine is not possible.
**
**    c) There are non-constant expressions in the VALUES clause (e.g.
**       the VALUES clause is part of a correlated sub-query).
**
**    d) One or more of the values in the first row of the VALUES clause
**       has an affinity (i.e. is a CAST expression). This causes problems
**       because the complex rules SQLite uses (see function 
**       sqlite3SubqueryColumnTypes() in select.c) to determine the effective
**       affinity of such a column for all rows require access to all values in
**       the column simultaneously. 
*/
Select *sqlite3MultiValues(Parse *pParse, Select *pLeft, ExprList *pRow){

  if( pParse->bHasWith                   /* condition (a) above */
   || pParse->db->init.busy              /* condition (b) above */
   || exprListIsConstant(pParse,pRow)==0 /* condition (c) above */
   || (pLeft->pSrc->nSrc==0 &&
       exprListIsNoAffinity(pParse,pLeft->pEList)==0) /* condition (d) above */
   || IN_SPECIAL_PARSE
  ){
    /* The co-routine method cannot be used. Fall back to UNION ALL. */
    Select *pSelect = 0;
    int f = SF_Values | SF_MultiValue;
    if( pLeft->pSrc->nSrc ){
      sqlite3MultiValuesEnd(pParse, pLeft);
      f = SF_Values;
    }else if( pLeft->pPrior ){
      /* In this case set the SF_MultiValue flag only if it was set on pLeft */
      f = (f & pLeft->selFlags);
    }
    pSelect = sqlite3SelectNew(pParse, pRow, 0, 0, 0, 0, 0, f, 0);
    pLeft->selFlags &= ~SF_MultiValue;
    if( pSelect ){
      pSelect->op = TK_ALL;
      pSelect->pPrior = pLeft;
      pLeft = pSelect;
    }
  }else{
    SrcItem *p = 0;               /* SrcItem that reads from co-routine */

    if( pLeft->pSrc->nSrc==0 ){
      /* Co-routine has not yet been started and the special Select object
      ** that accesses the co-routine has not yet been created. This block 
      ** does both those things. */
      Vdbe *v = sqlite3GetVdbe(pParse);
      Select *pRet = sqlite3SelectNew(pParse, 0, 0, 0, 0, 0, 0, 0, 0);

      /* Ensure the database schema has been read. This is to ensure we have
      ** the correct text encoding.  */
      if( (pParse->db->mDbFlags & DBFLAG_SchemaKnownOk)==0 ){
        sqlite3ReadSchema(pParse);
      }

      if( pRet ){
        SelectDest dest;
        pRet->pSrc->nSrc = 1;
        pRet->pPrior = pLeft->pPrior;
        pRet->op = pLeft->op;
        pLeft->pPrior = 0;
        pLeft->op = TK_SELECT;
        assert( pLeft->pNext==0 );
        assert( pRet->pNext==0 );
        p = &pRet->pSrc->a[0];
        p->pSelect = pLeft;
        p->fg.viaCoroutine = 1;
        p->addrFillSub = sqlite3VdbeCurrentAddr(v) + 1;
        p->regReturn = ++pParse->nMem;
        p->iCursor = -1;
        p->u1.nRow = 2;
        sqlite3VdbeAddOp3(v,OP_InitCoroutine,p->regReturn,0,p->addrFillSub);
        sqlite3SelectDestInit(&dest, SRT_Coroutine, p->regReturn);

        /* Allocate registers for the output of the co-routine. Do so so
        ** that there are two unused registers immediately before those
        ** used by the co-routine. This allows the code in sqlite3Insert()
        ** to use these registers directly, instead of copying the output
        ** of the co-routine to a separate array for processing.  */
        dest.iSdst = pParse->nMem + 3; 
        dest.nSdst = pLeft->pEList->nExpr;
        pParse->nMem += 2 + dest.nSdst;

        pLeft->selFlags |= SF_MultiValue;
        sqlite3Select(pParse, pLeft, &dest);
        p->regResult = dest.iSdst;
        assert( pParse->nErr || dest.iSdst>0 );
        pLeft = pRet;
      }
    }else{
      p = &pLeft->pSrc->a[0];
      assert( !p->fg.isTabFunc && !p->fg.isIndexedBy );
      p->u1.nRow++;
    }
  
    if( pParse->nErr==0 ){
      assert( p!=0 );
      if( p->pSelect->pEList->nExpr!=pRow->nExpr ){
        sqlite3SelectWrongNumTermsError(pParse, p->pSelect);
      }else{
        sqlite3ExprCodeExprList(pParse, pRow, p->regResult, 0, 0);
        sqlite3VdbeAddOp1(pParse->pVdbe, OP_Yield, p->regReturn);
      }
    }
    sqlite3ExprListDelete(pParse->db, pRow);
  }

  return pLeft;
}

/* Forward declaration */
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  int onError,          /* How to handle constraint errors */
909
910
911
912
913
914
915
916
917
918
919
















920
921
922
923
924
925
926
927
928
929
930
931
932
933
934

935
936
937
938
939
940
941
  ** is coming from a SELECT statement, then generate a co-routine that
  ** produces a single row of the SELECT on each invocation.  The
  ** co-routine is the common header to the 3rd and 4th templates.
  */
  if( pSelect ){
    /* Data is coming from a SELECT or from a multi-row VALUES clause.
    ** Generate a co-routine to run the SELECT. */
    int regYield;       /* Register holding co-routine entry-point */
    int addrTop;        /* Top of the co-routine */
    int rc;             /* Result code */

















    regYield = ++pParse->nMem;
    addrTop = sqlite3VdbeCurrentAddr(v) + 1;
    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
    sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
    dest.iSdst = bIdListInOrder ? regData : 0;
    dest.nSdst = pTab->nCol;
    rc = sqlite3Select(pParse, pSelect, &dest);
    regFromSelect = dest.iSdst;
    assert( db->pParse==pParse );
    if( rc || pParse->nErr ) goto insert_cleanup;
    assert( db->mallocFailed==0 );
    sqlite3VdbeEndCoroutine(v, regYield);
    sqlite3VdbeJumpHere(v, addrTop - 1);                       /* label B: */
    assert( pSelect->pEList );
    nColumn = pSelect->pEList->nExpr;


    /* Set useTempTable to TRUE if the result of the SELECT statement
    ** should be written into a temporary table (template 4).  Set to
    ** FALSE if each output row of the SELECT can be written directly into
    ** the destination table (template 3).
    **
    ** A temp table must be used if the table being updated is also one







<
<


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







1096
1097
1098
1099
1100
1101
1102


1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
  ** is coming from a SELECT statement, then generate a co-routine that
  ** produces a single row of the SELECT on each invocation.  The
  ** co-routine is the common header to the 3rd and 4th templates.
  */
  if( pSelect ){
    /* Data is coming from a SELECT or from a multi-row VALUES clause.
    ** Generate a co-routine to run the SELECT. */


    int rc;             /* Result code */

    if( pSelect->pSrc->nSrc==1 
     && pSelect->pSrc->a[0].fg.viaCoroutine 
     && pSelect->pPrior==0
    ){
      SrcItem *pItem = &pSelect->pSrc->a[0];
      dest.iSDParm = pItem->regReturn;
      regFromSelect = pItem->regResult;
      nColumn = pItem->pSelect->pEList->nExpr;
      ExplainQueryPlan((pParse, 0, "SCAN %S", pItem));
      if( bIdListInOrder && nColumn==pTab->nCol ){
        regData = regFromSelect;
        regRowid = regData - 1;
        regIns = regRowid - (IsVirtual(pTab) ? 1 : 0);
      }
    }else{
      int addrTop;        /* Top of the co-routine */
      int regYield = ++pParse->nMem;
      addrTop = sqlite3VdbeCurrentAddr(v) + 1;
      sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
      sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
      dest.iSdst = bIdListInOrder ? regData : 0;
      dest.nSdst = pTab->nCol;
      rc = sqlite3Select(pParse, pSelect, &dest);
      regFromSelect = dest.iSdst;
      assert( db->pParse==pParse );
      if( rc || pParse->nErr ) goto insert_cleanup;
      assert( db->mallocFailed==0 );
      sqlite3VdbeEndCoroutine(v, regYield);
      sqlite3VdbeJumpHere(v, addrTop - 1);                       /* label B: */
      assert( pSelect->pEList );
      nColumn = pSelect->pEList->nExpr;
    }

    /* Set useTempTable to TRUE if the result of the SELECT statement
    ** should be written into a temporary table (template 4).  Set to
    ** FALSE if each output row of the SELECT can be written directly into
    ** the destination table (template 3).
    **
    ** A temp table must be used if the table being updated is also one

Changes to src/parse.y.

561
562
563
564
565
566
567

568
569
570
571
572
573
574
  }
}

%ifndef SQLITE_OMIT_CTE
select(A) ::= WITH wqlist(W) selectnowith(X). {A = attachWithToSelect(pParse,X,W);}
select(A) ::= WITH RECURSIVE wqlist(W) selectnowith(X).
                                              {A = attachWithToSelect(pParse,X,W);}

%endif /* SQLITE_OMIT_CTE */
select(A) ::= selectnowith(A). {
  Select *p = A;
  if( p ){
    parserDoubleLinkSelect(pParse, p);
  }
}







>







561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
  }
}

%ifndef SQLITE_OMIT_CTE
select(A) ::= WITH wqlist(W) selectnowith(X). {A = attachWithToSelect(pParse,X,W);}
select(A) ::= WITH RECURSIVE wqlist(W) selectnowith(X).
                                              {A = attachWithToSelect(pParse,X,W);}

%endif /* SQLITE_OMIT_CTE */
select(A) ::= selectnowith(A). {
  Select *p = A;
  if( p ){
    parserDoubleLinkSelect(pParse, p);
  }
}
618
619
620
621
622
623
624
625
626
627

628
629
630
631








632
633
634
635
636
637
638
639
640
641
642


643
644
645
646
647
648
649
  }else{
    sqlite3WindowListDelete(pParse->db, R);
  }
}
%endif


oneselect(A) ::= values(A).

%type values {Select*}

%destructor values {sqlite3SelectDelete(pParse->db, $$);}
values(A) ::= VALUES LP nexprlist(X) RP. {
  A = sqlite3SelectNew(pParse,X,0,0,0,0,0,SF_Values,0);
}








values(A) ::= values(A) COMMA LP nexprlist(Y) RP. {
  Select *pRight, *pLeft = A;
  pRight = sqlite3SelectNew(pParse,Y,0,0,0,0,0,SF_Values|SF_MultiValue,0);
  if( ALWAYS(pLeft) ) pLeft->selFlags &= ~SF_MultiValue;
  if( pRight ){
    pRight->op = TK_ALL;
    pRight->pPrior = pLeft;
    A = pRight;
  }else{
    A = pLeft;
  }


}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {int}
distinct(A) ::= DISTINCT.   {A = SF_Distinct;}







|
|

>




>
>
>
>
>
>
>
>
|
<
<
|
<
<
<
<
<
<
|
>
>







619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642


643






644
645
646
647
648
649
650
651
652
653
  }else{
    sqlite3WindowListDelete(pParse->db, R);
  }
}
%endif


// Single row VALUES clause.
//
%type values {Select*}
oneselect(A) ::= values(A).
%destructor values {sqlite3SelectDelete(pParse->db, $$);}
values(A) ::= VALUES LP nexprlist(X) RP. {
  A = sqlite3SelectNew(pParse,X,0,0,0,0,0,SF_Values,0);
}

// Multiple row VALUES clause.
//
%type mvalues {Select*}
oneselect(A) ::= mvalues(A). {
  sqlite3MultiValuesEnd(pParse, A);
}
%destructor mvalues {sqlite3SelectDelete(pParse->db, $$);}
mvalues(A) ::= values(A) COMMA LP nexprlist(Y) RP. {


  A = sqlite3MultiValues(pParse, A, Y);






}
mvalues(A) ::= mvalues(A) COMMA LP nexprlist(Y) RP. {
  A = sqlite3MultiValues(pParse, A, Y);
}

// The "distinct" nonterminal is true (1) if the DISTINCT keyword is
// present and false (0) if it is not.
//
%type distinct {int}
distinct(A) ::= DISTINCT.   {A = SF_Distinct;}
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
      ** regardless of the value of expr1.
      */
      sqlite3ExprUnmapAndDelete(pParse, A);
      A = sqlite3Expr(pParse->db, TK_STRING, N ? "true" : "false");
      if( A ) sqlite3ExprIdToTrueFalse(A);
    }else{
      Expr *pRHS = Y->a[0].pExpr;
      if( Y->nExpr==1 && sqlite3ExprIsConstant(pRHS) && A->op!=TK_VECTOR ){
        Y->a[0].pExpr = 0;
        sqlite3ExprListDelete(pParse->db, Y);
        pRHS = sqlite3PExpr(pParse, TK_UPLUS, pRHS, 0);
        A = sqlite3PExpr(pParse, TK_EQ, A, pRHS);
      }else if( Y->nExpr==1 && pRHS->op==TK_SELECT ){
        A = sqlite3PExpr(pParse, TK_IN, A, 0);
        sqlite3PExprAddSelect(pParse, A, pRHS->x.pSelect);







|







1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
      ** regardless of the value of expr1.
      */
      sqlite3ExprUnmapAndDelete(pParse, A);
      A = sqlite3Expr(pParse->db, TK_STRING, N ? "true" : "false");
      if( A ) sqlite3ExprIdToTrueFalse(A);
    }else{
      Expr *pRHS = Y->a[0].pExpr;
      if( Y->nExpr==1 && sqlite3ExprIsConstant(pParse,pRHS) && A->op!=TK_VECTOR ){
        Y->a[0].pExpr = 0;
        sqlite3ExprListDelete(pParse->db, Y);
        pRHS = sqlite3PExpr(pParse, TK_UPLUS, pRHS, 0);
        A = sqlite3PExpr(pParse, TK_EQ, A, pRHS);
      }else if( Y->nExpr==1 && pRHS->op==TK_SELECT ){
        A = sqlite3PExpr(pParse, TK_IN, A, 0);
        sqlite3PExprAddSelect(pParse, A, pRHS->x.pSelect);
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766

1767
1768
1769
1770
1771
1772
1773
with ::= WITH wqlist(W).              { sqlite3WithPush(pParse, W, 1); }
with ::= WITH RECURSIVE wqlist(W).    { sqlite3WithPush(pParse, W, 1); }

%type wqas {u8}
wqas(A)   ::= AS.                  {A = M10d_Any;}
wqas(A)   ::= AS MATERIALIZED.     {A = M10d_Yes;}
wqas(A)   ::= AS NOT MATERIALIZED. {A = M10d_No;}
wqitem(A) ::= nm(X) eidlist_opt(Y) wqas(M) LP select(Z) RP. {
  A = sqlite3CteNew(pParse, &X, Y, Z, M); /*A-overwrites-X*/
}

wqlist(A) ::= wqitem(X). {
  A = sqlite3WithAdd(pParse, 0, X); /*A-overwrites-X*/
}
wqlist(A) ::= wqlist(A) COMMA wqitem(X). {
  A = sqlite3WithAdd(pParse, A, X);
}
%endif  SQLITE_OMIT_CTE







|


>







1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
with ::= WITH wqlist(W).              { sqlite3WithPush(pParse, W, 1); }
with ::= WITH RECURSIVE wqlist(W).    { sqlite3WithPush(pParse, W, 1); }

%type wqas {u8}
wqas(A)   ::= AS.                  {A = M10d_Any;}
wqas(A)   ::= AS MATERIALIZED.     {A = M10d_Yes;}
wqas(A)   ::= AS NOT MATERIALIZED. {A = M10d_No;}
wqitem(A) ::= withnm(X) eidlist_opt(Y) wqas(M) LP select(Z) RP. {
  A = sqlite3CteNew(pParse, &X, Y, Z, M); /*A-overwrites-X*/
}
withnm(A) ::= nm(A). {pParse->bHasWith = 1;}
wqlist(A) ::= wqitem(X). {
  A = sqlite3WithAdd(pParse, 0, X); /*A-overwrites-X*/
}
wqlist(A) ::= wqlist(A) COMMA wqitem(X). {
  A = sqlite3WithAdd(pParse, A, X);
}
%endif  SQLITE_OMIT_CTE

Changes to src/printf.c.

856
857
858
859
860
861
862




863
864
865
866
867
868
869
        }else if( pItem->zAlias ){
          sqlite3_str_appendall(pAccum, pItem->zAlias);
        }else{
          Select *pSel = pItem->pSelect;
          assert( pSel!=0 );
          if( pSel->selFlags & SF_NestedFrom ){
            sqlite3_str_appendf(pAccum, "(join-%u)", pSel->selId);




          }else{
            sqlite3_str_appendf(pAccum, "(subquery-%u)", pSel->selId);
          }
        }
        length = width = 0;
        break;
      }







>
>
>
>







856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
        }else if( pItem->zAlias ){
          sqlite3_str_appendall(pAccum, pItem->zAlias);
        }else{
          Select *pSel = pItem->pSelect;
          assert( pSel!=0 );
          if( pSel->selFlags & SF_NestedFrom ){
            sqlite3_str_appendf(pAccum, "(join-%u)", pSel->selId);
          }else if( pSel->selFlags & SF_MultiValue ){
            assert( !pItem->fg.isTabFunc && !pItem->fg.isIndexedBy );
            sqlite3_str_appendf(pAccum, "%u-ROW VALUES CLAUSE",
                                pItem->u1.nRow);
          }else{
            sqlite3_str_appendf(pAccum, "(subquery-%u)", pSel->selId);
          }
        }
        length = width = 0;
        break;
      }

Changes to src/select.c.

4772
4773
4774
4775
4776
4777
4778
4779
4780
4781
4782
4783
4784
4785
4786
  WhereConst *pConst,  /* The WhereConst into which we are inserting */
  Expr *pColumn,       /* The COLUMN part of the constraint */
  Expr *pValue,        /* The VALUE part of the constraint */
  Expr *pExpr          /* Overall expression: COLUMN=VALUE or VALUE=COLUMN */
){
  int i;
  assert( pColumn->op==TK_COLUMN );
  assert( sqlite3ExprIsConstant(pValue) );

  if( ExprHasProperty(pColumn, EP_FixedCol) ) return;
  if( sqlite3ExprAffinity(pValue)!=0 ) return;
  if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pConst->pParse,pExpr)) ){
    return;
  }








|







4772
4773
4774
4775
4776
4777
4778
4779
4780
4781
4782
4783
4784
4785
4786
  WhereConst *pConst,  /* The WhereConst into which we are inserting */
  Expr *pColumn,       /* The COLUMN part of the constraint */
  Expr *pValue,        /* The VALUE part of the constraint */
  Expr *pExpr          /* Overall expression: COLUMN=VALUE or VALUE=COLUMN */
){
  int i;
  assert( pColumn->op==TK_COLUMN );
  assert( sqlite3ExprIsConstant(pConst->pParse, pValue) );

  if( ExprHasProperty(pColumn, EP_FixedCol) ) return;
  if( sqlite3ExprAffinity(pValue)!=0 ) return;
  if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pConst->pParse,pExpr)) ){
    return;
  }

4830
4831
4832
4833
4834
4835
4836
4837
4838
4839
4840
4841
4842
4843
4844
4845
4846
4847
    return;
  }
  if( pExpr->op!=TK_EQ ) return;
  pRight = pExpr->pRight;
  pLeft = pExpr->pLeft;
  assert( pRight!=0 );
  assert( pLeft!=0 );
  if( pRight->op==TK_COLUMN && sqlite3ExprIsConstant(pLeft) ){
    constInsert(pConst,pRight,pLeft,pExpr);
  }
  if( pLeft->op==TK_COLUMN && sqlite3ExprIsConstant(pRight) ){
    constInsert(pConst,pLeft,pRight,pExpr);
  }
}

/*
** This is a helper function for Walker callback propagateConstantExprRewrite().
**







|


|







4830
4831
4832
4833
4834
4835
4836
4837
4838
4839
4840
4841
4842
4843
4844
4845
4846
4847
    return;
  }
  if( pExpr->op!=TK_EQ ) return;
  pRight = pExpr->pRight;
  pLeft = pExpr->pLeft;
  assert( pRight!=0 );
  assert( pLeft!=0 );
  if( pRight->op==TK_COLUMN && sqlite3ExprIsConstant(pConst->pParse, pLeft) ){
    constInsert(pConst,pRight,pLeft,pExpr);
  }
  if( pLeft->op==TK_COLUMN && sqlite3ExprIsConstant(pConst->pParse, pRight) ){
    constInsert(pConst,pLeft,pRight,pExpr);
  }
}

/*
** This is a helper function for Walker callback propagateConstantExprRewrite().
**
7632
7633
7634
7635
7636
7637
7638
7639
7640
7641
7642
7643
7644
7645
7646
      sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
    }

#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
    /* Generate code for all sub-queries in the FROM clause
    */
    pSub = pItem->pSelect;
    if( pSub==0 ) continue;

    /* The code for a subquery should only be generated once. */
    assert( pItem->addrFillSub==0 );

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







|







7632
7633
7634
7635
7636
7637
7638
7639
7640
7641
7642
7643
7644
7645
7646
      sqlite3AuthCheck(pParse, SQLITE_READ, pItem->zName, "", pItem->zDatabase);
    }

#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
    /* Generate code for all sub-queries in the FROM clause
    */
    pSub = pItem->pSelect;
    if( pSub==0 || pItem->addrFillSub!=0 ) continue;

    /* The code for a subquery should only be generated once. */
    assert( pItem->addrFillSub==0 );

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

Changes to src/sqliteInt.h.

3266
3267
3268
3269
3270
3271
3272
3273
3274


3275
3276
3277
3278
3279
3280
3281
3282
3283
** jointype expresses the join between the table and the previous table.
**
** In the colUsed field, the high-order bit (bit 63) is set if the table
** contains more than 63 columns and the 64-th or later column is used.
**
** Union member validity:
**
**    u1.zIndexedBy          fg.isIndexedBy && !fg.isTabFunc
**    u1.pFuncArg            fg.isTabFunc   && !fg.isIndexedBy


**    u2.pIBIndex            fg.isIndexedBy && !fg.isCte
**    u2.pCteUse             fg.isCte       && !fg.isIndexedBy
*/
struct SrcItem {
  Schema *pSchema;  /* Schema to which this item is fixed */
  char *zDatabase;  /* Name of database holding this table */
  char *zName;      /* Name of the table */
  char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
  Table *pTab;      /* An SQL table corresponding to zName */







|
|
>
>
|
|







3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
** jointype expresses the join between the table and the previous table.
**
** In the colUsed field, the high-order bit (bit 63) is set if the table
** contains more than 63 columns and the 64-th or later column is used.
**
** Union member validity:
**
**    u1.zIndexedBy      fg.isIndexedBy && !fg.isTabFunc
**    u1.pFuncArg        fg.isTabFunc   && !fg.isIndexedBy
**    u1.nRow            !fg.isTabFunc  && !fg.isIndexedBy
**
**    u2.pIBIndex        fg.isIndexedBy && !fg.isCte
**    u2.pCteUse         fg.isCte       && !fg.isIndexedBy
*/
struct SrcItem {
  Schema *pSchema;  /* Schema to which this item is fixed */
  char *zDatabase;  /* Name of database holding this table */
  char *zName;      /* Name of the table */
  char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
  Table *pTab;      /* An SQL table corresponding to zName */
3307
3308
3309
3310
3311
3312
3313

3314
3315
3316
3317
3318
3319
3320
    Expr *pOn;        /* fg.isUsing==0 =>  The ON clause of a join */
    IdList *pUsing;   /* fg.isUsing==1 =>  The USING clause of a join */
  } u3;
  Bitmask colUsed;  /* Bit N set if column N used. Details above for N>62 */
  union {
    char *zIndexedBy;    /* Identifier from "INDEXED BY <zIndex>" clause */
    ExprList *pFuncArg;  /* Arguments to table-valued-function */

  } u1;
  union {
    Index *pIBIndex;  /* Index structure corresponding to u1.zIndexedBy */
    CteUse *pCteUse;  /* CTE Usage info when fg.isCte is true */
  } u2;
};








>







3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
    Expr *pOn;        /* fg.isUsing==0 =>  The ON clause of a join */
    IdList *pUsing;   /* fg.isUsing==1 =>  The USING clause of a join */
  } u3;
  Bitmask colUsed;  /* Bit N set if column N used. Details above for N>62 */
  union {
    char *zIndexedBy;    /* Identifier from "INDEXED BY <zIndex>" clause */
    ExprList *pFuncArg;  /* Arguments to table-valued-function */
    u32 nRow;            /* Number of rows in a VALUES clause */
  } u1;
  union {
    Index *pIBIndex;  /* Index structure corresponding to u1.zIndexedBy */
    CteUse *pCteUse;  /* CTE Usage info when fg.isCte is true */
  } u2;
};

3808
3809
3810
3811
3812
3813
3814

3815
3816
3817
3818
3819
3820
3821
  u8 isMultiWrite;     /* True if statement may modify/insert multiple rows */
  u8 mayAbort;         /* True if statement may throw an ABORT exception */
  u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  u8 okConstFactor;    /* OK to factor out constants */
  u8 disableLookaside; /* Number of times lookaside has been disabled */
  u8 prepFlags;        /* SQLITE_PREPARE_* flags */
  u8 withinRJSubrtn;   /* Nesting level for RIGHT JOIN body subroutines */

#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  u8 earlyCleanup;     /* OOM inside sqlite3ParserAddCleanup() */
#endif
#ifdef SQLITE_DEBUG
  u8 ifNotExists;      /* Might be true if IF NOT EXISTS.  Assert()s only */
#endif
  int nRangeReg;       /* Size of the temporary register block */







>







3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
  u8 isMultiWrite;     /* True if statement may modify/insert multiple rows */
  u8 mayAbort;         /* True if statement may throw an ABORT exception */
  u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  u8 okConstFactor;    /* OK to factor out constants */
  u8 disableLookaside; /* Number of times lookaside has been disabled */
  u8 prepFlags;        /* SQLITE_PREPARE_* flags */
  u8 withinRJSubrtn;   /* Nesting level for RIGHT JOIN body subroutines */
  u8 bHasWith;         /* True if statement contains WITH */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  u8 earlyCleanup;     /* OOM inside sqlite3ParserAddCleanup() */
#endif
#ifdef SQLITE_DEBUG
  u8 ifNotExists;      /* Might be true if IF NOT EXISTS.  Assert()s only */
#endif
  int nRangeReg;       /* Size of the temporary register block */
4482
4483
4484
4485
4486
4487
4488



4489
4490
4491
4492
4493
4494
4495
  int regOne;             /* Register containing constant value 1 */
  int regStartRowid;
  int regEndRowid;
  u8 bExprArgs;           /* Defer evaluation of window function arguments
                          ** due to the SQLITE_SUBTYPE flag */
};




#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowUnlinkFromSelect(Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
void sqlite3WindowLink(Select *pSel, Window *pWin);







>
>
>







4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
  int regOne;             /* Register containing constant value 1 */
  int regStartRowid;
  int regEndRowid;
  u8 bExprArgs;           /* Defer evaluation of window function arguments
                          ** due to the SQLITE_SUBTYPE flag */
};

Select *sqlite3MultiValues(Parse *pParse, Select *pLeft, ExprList *pRow);
void sqlite3MultiValuesEnd(Parse *pParse, Select *pVal);

#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowUnlinkFromSelect(Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
void sqlite3WindowLink(Select *pSel, Window *pWin);
5050
5051
5052
5053
5054
5055
5056
5057
5058
5059
5060
5061
5062
5063
5064
5065
void sqlite3EndTransaction(Parse*,int);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
void sqlite3LeaveMutexAndCloseZombie(sqlite3*);
u32 sqlite3IsTrueOrFalse(const char*);
int sqlite3ExprIdToTrueFalse(Expr*);
int sqlite3ExprTruthValue(const Expr*);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);
int sqlite3ExprIsConstantOrFunction(Expr*, u8);
int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*);
int sqlite3ExprIsTableConstant(Expr*,int);
int sqlite3ExprIsSingleTableConstraint(Expr*,const SrcList*,int);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
int sqlite3ExprContainsSubquery(Expr*);
#endif







|
<







5057
5058
5059
5060
5061
5062
5063
5064

5065
5066
5067
5068
5069
5070
5071
void sqlite3EndTransaction(Parse*,int);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
void sqlite3LeaveMutexAndCloseZombie(sqlite3*);
u32 sqlite3IsTrueOrFalse(const char*);
int sqlite3ExprIdToTrueFalse(Expr*);
int sqlite3ExprTruthValue(const Expr*);
int sqlite3ExprIsConstant(Parse*,Expr*);

int sqlite3ExprIsConstantOrFunction(Expr*, u8);
int sqlite3ExprIsConstantOrGroupBy(Parse*, Expr*, ExprList*);
int sqlite3ExprIsTableConstant(Expr*,int);
int sqlite3ExprIsSingleTableConstraint(Expr*,const SrcList*,int);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
int sqlite3ExprContainsSubquery(Expr*);
#endif

Changes to src/where.c.

1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
  if( pOrderBy ){
    int n = pOrderBy->nExpr;
    for(i=0; i<n; i++){
      Expr *pExpr = pOrderBy->a[i].pExpr;
      Expr *pE2;

      /* Skip over constant terms in the ORDER BY clause */
      if( sqlite3ExprIsConstant(pExpr) ){
        continue;
      }

      /* Virtual tables are unable to deal with NULLS FIRST */
      if( pOrderBy->a[i].fg.sortFlags & KEYINFO_ORDER_BIGNULL ) break;

      /* First case - a direct column references without a COLLATE operator */







|







1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
  if( pOrderBy ){
    int n = pOrderBy->nExpr;
    for(i=0; i<n; i++){
      Expr *pExpr = pOrderBy->a[i].pExpr;
      Expr *pE2;

      /* Skip over constant terms in the ORDER BY clause */
      if( sqlite3ExprIsConstant(0, pExpr) ){
        continue;
      }

      /* Virtual tables are unable to deal with NULLS FIRST */
      if( pOrderBy->a[i].fg.sortFlags & KEYINFO_ORDER_BIGNULL ) break;

      /* First case - a direct column references without a COLLATE operator */
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451

    j++;
  }
  assert( j==nTerm );
  pIdxInfo->nConstraint = j;
  for(i=j=0; i<nOrderBy; i++){
    Expr *pExpr = pOrderBy->a[i].pExpr;
    if( sqlite3ExprIsConstant(pExpr) ) continue;
    assert( pExpr->op==TK_COLUMN
         || (pExpr->op==TK_COLLATE && pExpr->pLeft->op==TK_COLUMN
              && pExpr->iColumn==pExpr->pLeft->iColumn) );
    pIdxOrderBy[j].iColumn = pExpr->iColumn;
    pIdxOrderBy[j].desc = pOrderBy->a[i].fg.sortFlags & KEYINFO_ORDER_DESC;
    j++;
  }







|







1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451

    j++;
  }
  assert( j==nTerm );
  pIdxInfo->nConstraint = j;
  for(i=j=0; i<nOrderBy; i++){
    Expr *pExpr = pOrderBy->a[i].pExpr;
    if( sqlite3ExprIsConstant(0, pExpr) ) continue;
    assert( pExpr->op==TK_COLUMN
         || (pExpr->op==TK_COLLATE && pExpr->pLeft->op==TK_COLUMN
              && pExpr->iColumn==pExpr->pLeft->iColumn) );
    pIdxOrderBy[j].iColumn = pExpr->iColumn;
    pIdxOrderBy[j].desc = pOrderBy->a[i].fg.sortFlags & KEYINFO_ORDER_DESC;
    j++;
  }
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633

  if( (pPart->op==TK_EQ || pPart->op==TK_IS) ){
    Expr *pLeft = pPart->pLeft;
    Expr *pRight = pPart->pRight;
    u8 aff;

    if( pLeft->op!=TK_COLUMN ) return;
    if( !sqlite3ExprIsConstant(pRight) ) return;
    if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pParse, pPart)) ) return;
    if( pLeft->iColumn<0 ) return;
    aff = pIdx->pTable->aCol[pLeft->iColumn].affinity;
    if( aff>=SQLITE_AFF_TEXT ){
      if( pItem ){
        sqlite3 *db = pParse->db;
        IndexedExpr *p = (IndexedExpr*)sqlite3DbMallocRaw(db, sizeof(*p));







|







3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633

  if( (pPart->op==TK_EQ || pPart->op==TK_IS) ){
    Expr *pLeft = pPart->pLeft;
    Expr *pRight = pPart->pRight;
    u8 aff;

    if( pLeft->op!=TK_COLUMN ) return;
    if( !sqlite3ExprIsConstant(0, pRight) ) return;
    if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pParse, pPart)) ) return;
    if( pLeft->iColumn<0 ) return;
    aff = pIdx->pTable->aCol[pLeft->iColumn].affinity;
    if( aff>=SQLITE_AFF_TEXT ){
      if( pItem ){
        sqlite3 *db = pParse->db;
        IndexedExpr *p = (IndexedExpr*)sqlite3DbMallocRaw(db, sizeof(*p));
4993
4994
4995
4996
4997
4998
4999
5000
5001
5002
5003
5004
5005
5006
5007
      orderDistinctMask |= pLoop->maskSelf;
      for(i=0; i<nOrderBy; i++){
        Expr *p;
        Bitmask mTerm;
        if( MASKBIT(i) & obSat ) continue;
        p = pOrderBy->a[i].pExpr;
        mTerm = sqlite3WhereExprUsage(&pWInfo->sMaskSet,p);
        if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue;
        if( (mTerm&~orderDistinctMask)==0 ){
          obSat |= MASKBIT(i);
        }
      }
    }
  } /* End the loop over all WhereLoops from outer-most down to inner-most */
  if( obSat==obDone ) return (i8)nOrderBy;







|







4993
4994
4995
4996
4997
4998
4999
5000
5001
5002
5003
5004
5005
5006
5007
      orderDistinctMask |= pLoop->maskSelf;
      for(i=0; i<nOrderBy; i++){
        Expr *p;
        Bitmask mTerm;
        if( MASKBIT(i) & obSat ) continue;
        p = pOrderBy->a[i].pExpr;
        mTerm = sqlite3WhereExprUsage(&pWInfo->sMaskSet,p);
        if( mTerm==0 && !sqlite3ExprIsConstant(0,p) ) continue;
        if( (mTerm&~orderDistinctMask)==0 ){
          obSat |= MASKBIT(i);
        }
      }
    }
  } /* End the loop over all WhereLoops from outer-most down to inner-most */
  if( obSat==obDone ) return (i8)nOrderBy;
5862
5863
5864
5865
5866
5867
5868
5869
5870
5871
5872
5873
5874
5875
5876
      bMaybeNullRow = (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0;
    }else if( j>=0 && (pTab->aCol[j].colFlags & COLFLAG_VIRTUAL)!=0 ){
      pExpr = sqlite3ColumnExpr(pTab, &pTab->aCol[j]);
      bMaybeNullRow = 0;
    }else{
      continue;
    }
    if( sqlite3ExprIsConstant(pExpr) ) continue;
    if( pExpr->op==TK_FUNCTION ){
      /* Functions that might set a subtype should not be replaced by the
      ** value taken from an expression index since the index omits the
      ** subtype.  https://sqlite.org/forum/forumpost/68d284c86b082c3e */
      int n;
      FuncDef *pDef;
      sqlite3 *db = pParse->db;







|







5862
5863
5864
5865
5866
5867
5868
5869
5870
5871
5872
5873
5874
5875
5876
      bMaybeNullRow = (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0;
    }else if( j>=0 && (pTab->aCol[j].colFlags & COLFLAG_VIRTUAL)!=0 ){
      pExpr = sqlite3ColumnExpr(pTab, &pTab->aCol[j]);
      bMaybeNullRow = 0;
    }else{
      continue;
    }
    if( sqlite3ExprIsConstant(0,pExpr) ) continue;
    if( pExpr->op==TK_FUNCTION ){
      /* Functions that might set a subtype should not be replaced by the
      ** value taken from an expression index since the index omits the
      ** subtype.  https://sqlite.org/forum/forumpost/68d284c86b082c3e */
      int n;
      FuncDef *pDef;
      sqlite3 *db = pParse->db;
6140
6141
6142
6143
6144
6145
6146



6147

6148
6149
6150
6151
6152
6153
6154
  if( nTabList==0 ){
    if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
    if( (wctrlFlags & WHERE_WANT_DISTINCT)!=0
     && OptimizationEnabled(db, SQLITE_DistinctOpt)
    ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }



    ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW"));

  }else{
    /* Assign a bit from the bitmask to every term in the FROM clause.
    **
    ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
    **
    ** The rule of the previous sentence ensures that if X is the bitmask for
    ** a table T, then X-1 is the bitmask for all other tables to the left of T.







>
>
>
|
>







6140
6141
6142
6143
6144
6145
6146
6147
6148
6149
6150
6151
6152
6153
6154
6155
6156
6157
6158
  if( nTabList==0 ){
    if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
    if( (wctrlFlags & WHERE_WANT_DISTINCT)!=0
     && OptimizationEnabled(db, SQLITE_DistinctOpt)
    ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }
    if( ALWAYS(pWInfo->pSelect)
     && (pWInfo->pSelect->selFlags & SF_MultiValue)==0
    ){
      ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW"));
    }
  }else{
    /* Assign a bit from the bitmask to every term in the FROM clause.
    **
    ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
    **
    ** The rule of the previous sentence ensures that if X is the bitmask for
    ** a table T, then X-1 is the bitmask for all other tables to the left of T.
6892
6893
6894
6895
6896
6897
6898

6899
6900
6901
6902
6903
6904
6905

    /* For a co-routine, change all OP_Column references to the table of
    ** the co-routine into OP_Copy of result contained in a register.
    ** OP_Rowid becomes OP_Null.
    */
    if( pTabItem->fg.viaCoroutine ){
      testcase( pParse->db->mallocFailed );

      translateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur,
                            pTabItem->regResult, 0);
      continue;
    }

    /* If this scan uses an index, make VDBE code substitutions to read data
    ** from the index instead of from the table where possible.  In some cases







>







6896
6897
6898
6899
6900
6901
6902
6903
6904
6905
6906
6907
6908
6909
6910

    /* For a co-routine, change all OP_Column references to the table of
    ** the co-routine into OP_Copy of result contained in a register.
    ** OP_Rowid becomes OP_Null.
    */
    if( pTabItem->fg.viaCoroutine ){
      testcase( pParse->db->mallocFailed );
      assert( pTabItem->regResult>=0 );
      translateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur,
                            pTabItem->regResult, 0);
      continue;
    }

    /* If this scan uses an index, make VDBE code substitutions to read data
    ** from the index instead of from the table where possible.  In some cases

Changes to src/whereexpr.c.

985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
    iCur = pFrom->a[j].iCursor;
    for(pIdx=pFrom->a[j].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr==0 ) continue;
      for(i=0; i<pIdx->nKeyCol; i++){
        if( pIdx->aiColumn[i]!=XN_EXPR ) continue;
        assert( pIdx->bHasExpr );
        if( sqlite3ExprCompareSkip(pExpr,pIdx->aColExpr->a[i].pExpr,iCur)==0
         && !sqlite3ExprIsConstant(pIdx->aColExpr->a[i].pExpr)
        ){
          aiCurCol[0] = iCur;
          aiCurCol[1] = XN_EXPR;
          return 1;
        }
      }
    }







|







985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
    iCur = pFrom->a[j].iCursor;
    for(pIdx=pFrom->a[j].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr==0 ) continue;
      for(i=0; i<pIdx->nKeyCol; i++){
        if( pIdx->aiColumn[i]!=XN_EXPR ) continue;
        assert( pIdx->bHasExpr );
        if( sqlite3ExprCompareSkip(pExpr,pIdx->aColExpr->a[i].pExpr,iCur)==0
         && !sqlite3ExprIsConstant(0,pIdx->aColExpr->a[i].pExpr)
        ){
          aiCurCol[0] = iCur;
          aiCurCol[1] = XN_EXPR;
          return 1;
        }
      }
    }

Changes to src/window.c.

1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
** The argument expression is an PRECEDING or FOLLOWING offset.  The
** value should be a non-negative integer.  If the value is not a
** constant, change it to NULL.  The fact that it is then a non-negative
** integer will be caught later.  But it is important not to leave
** variable values in the expression tree.
*/
static Expr *sqlite3WindowOffsetExpr(Parse *pParse, Expr *pExpr){
  if( 0==sqlite3ExprIsConstant(pExpr) ){
    if( IN_RENAME_OBJECT ) sqlite3RenameExprUnmap(pParse, pExpr);
    sqlite3ExprDelete(pParse->db, pExpr);
    pExpr = sqlite3ExprAlloc(pParse->db, TK_NULL, 0, 0);
  }
  return pExpr;
}








|







1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
** The argument expression is an PRECEDING or FOLLOWING offset.  The
** value should be a non-negative integer.  If the value is not a
** constant, change it to NULL.  The fact that it is then a non-negative
** integer will be caught later.  But it is important not to leave
** variable values in the expression tree.
*/
static Expr *sqlite3WindowOffsetExpr(Parse *pParse, Expr *pExpr){
  if( 0==sqlite3ExprIsConstant(0,pExpr) ){
    if( IN_RENAME_OBJECT ) sqlite3RenameExprUnmap(pParse, pExpr);
    sqlite3ExprDelete(pParse->db, pExpr);
    pExpr = sqlite3ExprAlloc(pParse->db, TK_NULL, 0, 0);
  }
  return pExpr;
}

Changes to test/altertab3.test.

731
732
733
734
735
736
737



























738
739
  END}
  {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN
    SELECT z, y FROM (
      SELECT "t3".* FROM "t3"
    );
  END}
}




























finish_test







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


731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
  END}
  {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN
    SELECT z, y FROM (
      SELECT "t3".* FROM "t3"
    );
  END}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 30.0 {
  CREATE TABLE t1(a, b);
  CREATE VIEW v1 AS 
      SELECT ( VALUES(a), (b) ) FROM (
        SELECT a, b FROM t1
      )
  ;
}

do_execsql_test 30.1 {
  SELECT * FROM v1
}

do_execsql_test 30.1 {
  ALTER TABLE t1 RENAME TO t2;
}
do_execsql_test 30.2 {
  SELECT sql FROM sqlite_schema WHERE type='view'
} {
  {CREATE VIEW v1 AS 
      SELECT ( VALUES(a), (b) ) FROM (
        SELECT a, b FROM "t2"
      )}
}

finish_test

Changes to test/in4.test.

454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
  ANALYZE sqlite_schema;
  INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5 00 2003 10');
  ANALYZE sqlite_schema;
} {}
do_execsql_test 11.1 {
  SELECT * FROM t1
   WHERE b IN (345, (SELECT 1 FROM t1 
                      WHERE b IN (345 NOT GLOB 510)
                        AND c GLOB 'abc*xyz'))
     AND c BETWEEN 'abc' AND 'xyz';
} {xyz 1 abcdefxyz 99}
do_execsql_test 11.2 {
  EXPLAIN SELECT * FROM t1
   WHERE b IN (345, (SELECT 1 FROM t1 
                      WHERE b IN (345 NOT GLOB 510)
                        AND c GLOB 'abc*xyz'))
     AND c BETWEEN 'abc' AND 'xyz';
} {/ SeekScan /}

# 2021-06-25 ticket 6dcbfd11cf666e21
# Another problem with OP_SeekScan
#







|






|







454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
  ANALYZE sqlite_schema;
  INSERT INTO sqlite_stat1 VALUES('t1','t1abc','10000 5 00 2003 10');
  ANALYZE sqlite_schema;
} {}
do_execsql_test 11.1 {
  SELECT * FROM t1
   WHERE b IN (345, (SELECT 1 FROM t1 
                      WHERE b IN (coalesce(1,random()))
                        AND c GLOB 'abc*xyz'))
     AND c BETWEEN 'abc' AND 'xyz';
} {xyz 1 abcdefxyz 99}
do_execsql_test 11.2 {
  EXPLAIN SELECT * FROM t1
   WHERE b IN (345, (SELECT 1 FROM t1 
                      WHERE b IN (coalesce(1,random()))
                        AND c GLOB 'abc*xyz'))
     AND c BETWEEN 'abc' AND 'xyz';
} {/ SeekScan /}

# 2021-06-25 ticket 6dcbfd11cf666e21
# Another problem with OP_SeekScan
#

Changes to test/sqllimits1.test.

918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
  CREATE TABLE b1(x);
  INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11);
} {0 {}}

do_catchsql_test sqllimits1-18.2 {
  INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
    UNION VALUES(11);
} {1 {too many terms in compound SELECT}}

#-------------------------------------------------------------------------
#
reset_db
ifcapable utf16 {
  do_execsql_test 19.0 {
    PRAGMA encoding = 'utf16';







|







918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
  CREATE TABLE b1(x);
  INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11);
} {0 {}}

do_catchsql_test sqllimits1-18.2 {
  INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
    UNION VALUES(11);
} {0 {}}

#-------------------------------------------------------------------------
#
reset_db
ifcapable utf16 {
  do_execsql_test 19.0 {
    PRAGMA encoding = 'utf16';

Added test/values.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
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
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
# 2024 March 3
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

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


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, c);
}


explain_i {
  INSERT INTO x1(a, b, c) VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
}
do_execsql_test 1.1.1 {
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
}
do_execsql_test 1.1.2 {
  SELECT * FROM x1;
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}

do_execsql_test 1.2.0 {
  DELETE FROM x1
}
do_execsql_test 1.2.1 {
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 4, 4, 4;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 4

do_execsql_test 1.2.2 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5) 
  UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4  5 5 5  6 6 6}

do_execsql_test 1.2.3 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4)
  UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {1 1 1  2 2 2   3 3 3  4 4 4  6 6 6}

do_execsql_test 1.2.4 {
  DELETE FROM x1;
  INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3) UNION ALL SELECT 6, 6, 6;
  SELECT * FROM x1;
} {
 1 1 1
 2 2 2
 3 3 3
 6 6 6
}

set a 4
set b 5
set c 6
do_execsql_test 1.2.5 {
  DELETE FROM x1;
  INSERT INTO x1 
  VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), 
        (4, 4, $a), (5, 5, $b), (6, 6, $c)
}

do_execsql_test 1.2.6 {
  SELECT * FROM x1;
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
  5 5 5
  6 6 6
}

#-------------------------------------------------------------------------
# SQLITE_LIMIT_COMPOUND_SELECT set to 0.
#
reset_db

do_execsql_test 2.0 {
  CREATE TABLE x1(a, b, c);
}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 3

do_catchsql_test 2.1.1 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10, 10)
} {1 {all VALUES must have the same number of terms}}

do_catchsql_test 2.1.2 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
} {1 {all VALUES must have the same number of terms}}

sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0

do_execsql_test 2.2 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
} {}
do_execsql_test 2.3 {
  INSERT INTO x1 VALUES
      (1, 1, 1), 
      (2, 2, 2), 
      (3, 3, 3), 
      (4, 4, 4), 
      (5, 5, 5), 
      (6, 6, 6), 
      (7, 7, 7), 
      (8, 8, 8), 
      (9, 9, 9), 
      (10, 10, 10)
      UNION ALL 
      SELECT 5, 12, 12
      ORDER BY 1
} {}

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

do_execsql_test 3.0 {
  CREATE TABLE y1(x, y);
}

do_execsql_test 3.1.1 {
  DELETE FROM y1;
  INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 5);
}
do_execsql_test 3.1.2 {
  SELECT * FROM y1;
} {1 2  3 4  1 5}
do_execsql_test 3.2.1 {
  DELETE FROM y1;
  INSERT INTO y1 VALUES(1, 2), (3, 4), (row_number() OVER (), 6)
    , (row_number() OVER (), 7)
}
do_execsql_test 3.1.2 {
  SELECT * FROM y1;
} {1 2  3 4  1 6  1 7}

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

do_execsql_test 4.0 {
  CREATE TABLE x1(a PRIMARY KEY, b) WITHOUT ROWID;
}

foreach {tn iLimit} {1 0    2 3} { 
  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT $iLimit

  do_execsql_test 4.1.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, 1),
        (2, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
  }
  do_execsql_test 4.1.2 {
    SELECT * FROM x1
  } {1 1 2 a}

  do_execsql_test 4.2.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, 1),
        (2, 2),
        (3, 3),
        (4, 4),
        (5, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d')) ))
  }
  do_execsql_test 4.2.2 {
    SELECT * FROM x1
  } {1 1 2 2 3 3 4 4 5 a}

  do_execsql_test 4.3.1 {
    DELETE FROM x1;
    INSERT INTO x1 VALUES
        (1, (SELECT * FROM  (VALUES('a'), ('b'), ('c'), ('d'), ('e')) ))
  }
  do_execsql_test 4.3.2 {
    SELECT * FROM x1
  } {1 a}
}

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

do_execsql_test 5.0 {
  CREATE VIEW v1 AS VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);
}
do_execsql_test 5.1 {
  SELECT * FROM v1
} {1 2 3 4 5 6 7 8 9}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1), (2);
}

do_execsql_test 6.1 {
  SELECT ( VALUES( x ), ( x ) ) FROM t1;
} {1 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('x'), ('y');
}

do_execsql_test 6.1 {
  SELECT * FROM t1, (VALUES(1), (2))
} {x 1 x 2 y 1 y 2}

do_execsql_test 6.2 {
  VALUES(CAST(44 AS REAL)),(55);
} {44.0 55}

#------------------------------------------------------------------------
do_execsql_test 7.1 {
  WITH x1(a, b) AS (
    VALUES(1, 2), ('a', 'b')
  )
  SELECT * FROM x1 one, x1 two
} {
  1 2  1 2
  1 2  a b
  a b  1 2
  a b  a b
}

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

set VVV {
  ( VALUES('a', 'b'), ('c', 'd'), (123, NULL) )
}
set VVV2 {
  ( 
  SELECT 'a' AS column1, 'b' AS column2 
  UNION ALL SELECT 'c', 'd' UNION ALL SELECT 123, NULL
  )
}

do_execsql_test 8.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('d'), (NULL), (123)
}
foreach {tn q res} {
  1 "SELECT * FROM t1 LEFT JOIN VVV" {
    d a b   d c d   d 123 {}
    {} a b   {} c d   {} 123 {}
    123 a b   123 c d   123 123 {}
  }

  2 "SELECT * FROM t1 LEFT JOIN VVV ON (column1=x)" {
    d {} {}
    {} {} {}
    123 123 {}
  }

  3 "SELECT * FROM t1 RIGHT JOIN VVV" {
    d a b   d c d   d 123 {}
    {} a b   {} c d   {} 123 {}
    123 a b   123 c d   123 123 {}
  }

  4 "SELECT * FROM t1 RIGHT JOIN VVV ON (column1=x)" {
    123 123 {}
    {} a b
    {} c d
  }

  5 "SELECT * FROM t1 FULL OUTER JOIN VVV ON (column1=x)" {
    d {} {}
    {} {} {}
    123 123 {}
    {} a b
    {} c d
  }

  6 "SELECT count(*) FROM VVV" { 3 }

  7 "SELECT (SELECT column1 FROM VVV)" { a }

  8 "SELECT * FROM VVV UNION ALL SELECT * FROM VVV" {
    a b c d 123 {}
    a b c d 123 {}
  }

  9 "SELECT * FROM VVV INTERSECT SELECT * FROM VVV" {
    123 {} a b c d 
  }

  10 "SELECT * FROM VVV eXCEPT SELECT * FROM VVV" { }

  11 "SELECT * FROM VVV eXCEPT SELECT 'a', 'b'" { 123 {} c d }

} {
  set q1 [string map [list VVV $VVV] $q]
  set q2 [string map [list VVV $VVV2] $q]
  set q3 "WITH VVV AS $VVV $q"

  do_execsql_test 8.1.$tn.1 $q1 $res
  do_execsql_test 8.1.$tn.2 $q2 $res
  do_execsql_test 8.1.$tn.3 $q3 $res
}

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

do_execsql_test 9.1 {
  VALUES(456), (123), (NULL) UNION ALL SELECT 122 ORDER BY 1
} { {} 122 123 456 }

do_execsql_test 9.2 {
  VALUES (1, 2), (3, 4), (
    ( SELECT column1 FROM ( VALUES (5, 6), (7, 8) ) ),
    ( SELECT max(column2) FROM ( VALUES (5, 1), (7, 6) ) )
  )
} { 1 2 3 4 5 6 }

do_execsql_test 10.1 {
  CREATE TABLE a2(a, b, c DEFAULT 'xyz');
}
do_execsql_test 10.2 {
  INSERT INTO a2(a) VALUES(3),(4);
}

#-------------------------------------------------------------------------
reset_db
ifcapable fts5 {
  do_execsql_test 11.0 {
    CREATE VIRTUAL TABLE ft USING fts3(x);
  }
  do_execsql_test 11.1 {
    INSERT INTO ft VALUES('one'), ('two');
  }
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 12.0 {
  CREATE TABLE t1(a, b);
}
do_execsql_test 12.1 {
  INSERT INTO t1 SELECT 1, 2 UNION ALL VALUES(3, 4), (5, 6);
}
do_execsql_test 12.2 {
  SELECT * FROM t1
} {1 2 3 4 5 6}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('xyz');

  SELECT (
      VALUES( (max(substr('abc', 1, 1), x)) ),
      (123),
      (456)
      )
  FROM t1;
} {xyz}

do_catchsql_test 13.1 {
  VALUES(300), (zeroblob(300) OVER win);
} {1 {zeroblob() may not be used as a window function}}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 14.1 {
  PRAGMA encoding = utf16;
  CREATE TABLE t1(a, b);
} {}

db close
sqlite3 db test.db

do_execsql_test 14.2 {
  INSERT INTO t1 VALUES
    (17, 'craft'),
    (16, 'urtlek' IN(1,2,3));
}

#--------------------------------------------------------------------------
#
reset_db
do_eqp_test 15.1 {
  VALUES(1),(2),(3),(4),(5);
} {
  QUERY PLAN
  `--SCAN 5-ROW VALUES CLAUSE
}
do_execsql_test 15.2 {
  CREATE TABLE t1(a,b);
}
do_eqp_test 15.3 {
  INSERT INTO t1 VALUES
    (1,2),(3,4),(7,8);
} {
  QUERY PLAN
  `--SCAN 3-ROW VALUES CLAUSE
}
do_eqp_test 15.4 {
  INSERT INTO t1 VALUES
    (1,2),(3,4),(7,8),
    (5,row_number()OVER());
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN 3-ROW VALUES CLAUSE
     `--UNION ALL
        |--CO-ROUTINE (subquery-xxxxxx)
        |  `--SCAN CONSTANT ROW
        `--SCAN (subquery-xxxxxx)
}
do_eqp_test 15.5 {
  SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6)), (VALUES('a'),('b'),('c'));
} {
  QUERY PLAN
  |--SCAN 6-ROW VALUES CLAUSE
  `--SCAN 3-ROW VALUES CLAUSE
}
do_execsql_test 15.6 {
  CREATE TABLE t2(x,y);
}
do_eqp_test 15.7 {
  SELECT * FROM t2 UNION ALL VALUES(1,2),(3,4),(5,6),(7,8);
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN t2
     `--UNION ALL
        `--SCAN 4-ROW VALUES CLAUSE
}

#--------------------------------------------------------------------------
# The VALUES-as-coroutine optimization can be applied to later rows of
# a VALUES clause even if earlier rows do not qualify.
#
reset_db
do_execsql_test 16.1 {
  CREATE TABLE t1(a,b);
}
do_execsql_test 16.2 {
  BEGIN;
  INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
     (7,row_number()OVER()),
     (9,10), (11,12), (13,14), (15,16);
  SELECT * FROM t1 ORDER BY a, b;
  ROLLBACK;
} {1 2 3 4 5 6 7 1 9 10 11 12 13 14 15 16}
do_eqp_test 16.3 {
  INSERT INTO t1 VALUES(1,2),(3,4),(5,6),
     (7,row_number()OVER()),
     (9,10), (11,12), (13,14), (15,16);
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN 3-ROW VALUES CLAUSE
     |--UNION ALL
     |  |--CO-ROUTINE (subquery-xxxxxx)
     |  |  `--SCAN CONSTANT ROW
     |  `--SCAN (subquery-xxxxxx)
     `--UNION ALL
        `--SCAN 4-ROW VALUES CLAUSE
}
do_execsql_test 16.4 {
  BEGIN;
  INSERT INTO t1 VALUES
     (1,row_number()OVER()),
     (2,3), (4,5), (6,7);
  SELECT * FROM t1 ORDER BY a, b;
  ROLLBACK;
} {1 1 2 3 4 5 6 7}
do_eqp_test 16.5 {
  INSERT INTO t1 VALUES
     (1,row_number()OVER()),
     (2,3), (4,5), (6,7);
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  |--CO-ROUTINE (subquery-xxxxxx)
     |  |  `--SCAN CONSTANT ROW
     |  `--SCAN (subquery-xxxxxx)
     `--UNION ALL
        `--SCAN 3-ROW VALUES CLAUSE
}
do_execsql_test 16.6 {
  BEGIN;
  INSERT INTO t1 VALUES
     (1,2),(3,4),
     (5,row_number()OVER()),
     (7,8),(9,10),(11,12),
     (13,row_number()OVER()),
     (15,16),(17,18),(19,20),(21,22);
  SELECT * FROM t1 ORDER BY a, b;
  ROLLBACK;
} { 1 2 3 4 5 1 7 8 9 10 11 12 13 1 15 16 17 18 19 20 21 22}
do_eqp_test 16.7 {
  INSERT INTO t1 VALUES
     (1,2),(3,4),
     (5,row_number()OVER()),
     (7,8),(9,10),(11,12),
     (13,row_number()OVER()),
     (15,16),(17,18),(19,20),(21,22);
} {
  QUERY PLAN
  `--COMPOUND QUERY
     |--LEFT-MOST SUBQUERY
     |  `--SCAN 2-ROW VALUES CLAUSE
     |--UNION ALL
     |  |--CO-ROUTINE (subquery-xxxxxx)
     |  |  `--SCAN CONSTANT ROW
     |  `--SCAN (subquery-xxxxxx)
     |--UNION ALL
     |  `--SCAN 3-ROW VALUES CLAUSE
     |--UNION ALL
     |  |--CO-ROUTINE (subquery-xxxxxx)
     |  |  `--SCAN CONSTANT ROW
     |  `--SCAN (subquery-xxxxxx)
     `--UNION ALL
        `--SCAN 4-ROW VALUES CLAUSE
}

finish_test

Added test/valuesfault.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
# 2024 March 3
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

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


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, c);
}
faultsim_save_and_close

do_faultsim_test 1 -prep {
  faultsim_restore_and_reopen
  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 2
} -body {
  execsql {
    INSERT INTO x1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
  }
} -test {
  faultsim_test_result {0 {}} 
}


finish_test

Changes to test/whereL.test.

45
46
47
48
49
50
51



























52
53
54
55
56
57
58
  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--SCAN t3
}




























# Constant propagation in the face of collating sequences:
#
do_execsql_test 200 {
  CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
  CREATE INDEX c3x ON c3(x);
  INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');







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







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
  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--SCAN t3
}
do_eqp_test 121 {
  SELECT * FROM t1, t2, t3
   WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=abs(5)
  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--SCAN t3
}

# The sqlite3ExprIsConstant() routine does not believe that
# the expression "coalesce(5,random())" is constant.  So the
# optimization does not apply in this case.
# 
sqlite3_create_function db
do_eqp_test 122 {
  SELECT * FROM t1, t2, t3
   WHERE t1.a=t2.a AND t2.a=t3.j AND t3.j=coalesce(5,random())
  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SCAN t3
  |--SEARCH t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

# Constant propagation in the face of collating sequences:
#
do_execsql_test 200 {
  CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
  CREATE INDEX c3x ON c3(x);
  INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');