/ Check-in [8b12e95f]
Login

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

Overview
Comment:Fix a problem that comes up when using generated columns that evaluate to a constant in an index and then making use of that index in a join.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 8b12e95fec7ce6e0de82a04ca3dfcf1a8e62e233b7382aa28a8a9be6e862b1af
User & Date: drh 2019-11-21 18:28:44
Context
2019-11-21
19:37
Whenever a generated column is used, assume that all columns are used. check-in: 6601da58 user: drh tags: trunk
18:28
Fix a problem that comes up when using generated columns that evaluate to a constant in an index and then making use of that index in a join. check-in: 8b12e95f user: drh tags: trunk
17:14
Fix a recently introduced memory leak in the test code in test_vfs.c. check-in: 2d53a30c user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

3601
3602
3603
3604
3605
3606
3607


3608



3609
3610
3611
3612
3613
3614
3615
        /* This COLUMN expression is really a constant due to WHERE clause
        ** constraints, and that constant is coded by the pExpr->pLeft
        ** expresssion.  However, make sure the constant has the correct
        ** datatype by applying the Affinity of the table column to the
        ** constant.
        */
        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);


        int aff = sqlite3TableColumnAffinity(pExpr->y.pTab, pExpr->iColumn);



        if( aff>SQLITE_AFF_BLOB ){
          static const char zAff[] = "B\000C\000D\000E";
          assert( SQLITE_AFF_BLOB=='A' );
          assert( SQLITE_AFF_TEXT=='B' );
          if( iReg!=target ){
            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
            iReg = target;







>
>
|
>
>
>







3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
        /* This COLUMN expression is really a constant due to WHERE clause
        ** constraints, and that constant is coded by the pExpr->pLeft
        ** expresssion.  However, make sure the constant has the correct
        ** datatype by applying the Affinity of the table column to the
        ** constant.
        */
        int iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
        int aff;
        if( pExpr->y.pTab ){
          aff = sqlite3TableColumnAffinity(pExpr->y.pTab, pExpr->iColumn);
        }else{
          aff = pExpr->affExpr;
        }
        if( aff>SQLITE_AFF_BLOB ){
          static const char zAff[] = "B\000C\000D\000E";
          assert( SQLITE_AFF_BLOB=='A' );
          assert( SQLITE_AFF_TEXT=='B' );
          if( iReg!=target ){
            sqlite3VdbeAddOp2(v, OP_SCopy, iReg, target);
            iReg = target;

Changes to src/wherecode.c.

1137
1138
1139
1140
1141
1142
1143


1144
1145
1146
1147
1148
1149
1150
/* A walker node callback that translates a column reference to a table
** into a corresponding column reference of an index.
*/
static int whereIndexExprTransColumn(Walker *p, Expr *pExpr){
  if( pExpr->op==TK_COLUMN ){
    IdxExprTrans *pX = p->u.pIdxTrans;
    if( pExpr->iTable==pX->iTabCur && pExpr->iColumn==pX->iTabCol ){


      pExpr->iTable = pX->iIdxCur;
      pExpr->iColumn = pX->iIdxCol;
      pExpr->y.pTab = 0;
    }
  }
  return WRC_Continue;
}







>
>







1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
/* A walker node callback that translates a column reference to a table
** into a corresponding column reference of an index.
*/
static int whereIndexExprTransColumn(Walker *p, Expr *pExpr){
  if( pExpr->op==TK_COLUMN ){
    IdxExprTrans *pX = p->u.pIdxTrans;
    if( pExpr->iTable==pX->iTabCur && pExpr->iColumn==pX->iTabCol ){
      assert( pExpr->y.pTab!=0 );
      pExpr->affExpr = sqlite3TableColumnAffinity(pExpr->y.pTab,pExpr->iColumn);
      pExpr->iTable = pX->iIdxCur;
      pExpr->iColumn = pX->iIdxCol;
      pExpr->y.pTab = 0;
    }
  }
  return WRC_Continue;
}

Changes to test/gencol1.test.

238
239
240
241
242
243
244












245
246
247
  CREATE TABLE t0(
    c0,
    c1 AS(c0 + c2),
    c2 AS(c1) CHECK(c2)
  );
  UPDATE t0 SET c0 = NULL;
} {1 {generated column loop on "c2"}}














finish_test







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



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
  CREATE TABLE t0(
    c0,
    c1 AS(c0 + c2),
    c2 AS(c1) CHECK(c2)
  );
  UPDATE t0 SET c0 = NULL;
} {1 {generated column loop on "c2"}}

# 2019-11-21 Problems in the new generated column logic
# reported by Yongheng Chen and Rui Zhong
do_execsql_test gencol1-9.10 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(aa , bb AS (17) UNIQUE);
  INSERT INTO t1 VALUES(17);
  CREATE TABLE t2(cc);
  INSERT INTO t2 VALUES(41);
  SELECT * FROM t2 JOIN t1 WHERE t1.bb=t1.aa AND t1.bb=17;
} {41 17 17}


finish_test