/ Check-in [728ad39e]
Login

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

Overview
Comment:Apply real affinity to generated columns of type REAL that are extract from an index. Ticket [e0a8120553f4b082]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 728ad39e3bd07a2503a95c68ed5bbc3f28fd97551d59f12a5fab25dc68227e99
User & Date: drh 2019-12-20 20:45:02
References
2019-12-20
20:56 Ticket [e0a81205] Incorrect result for BETWEEN and generated column status still Open with 8 other changes (artifact: 924f8096 user: drh)
Context
2019-12-20
22:46
Do not try to access a generated column through an index if the collating sequence for the generated column is non-standard. Part 2 of ticket [e0a8120553f4b082] (check-in: 056bb8dc user: drh tags: trunk)
20:45
Apply real affinity to generated columns of type REAL that are extract from an index. Ticket [e0a8120553f4b082] (check-in: 728ad39e user: drh tags: trunk)
20:08
Debugging enhancment: Show the Expr.y.pTab pointer on TK_COLUMN nodes of an expression tree in the treeview. (check-in: 64154ac4 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

3605
3606
3607
3608
3609
3610
3611

3612
3613
3614
3615
3616
3617
3618
3619
3620

3621
3622
3623
3624
3625
3626
3627
....
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690




3691
3692
3693
3694
3695
3696
3697
                              pCol->iSorterColumn, target);
        return target;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      int iTab = pExpr->iTable;

      if( ExprHasProperty(pExpr, EP_FixedCol) ){
        /* 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";
................................................................................
          }
        }else{
          /* Coding an expression that is part of an index where column names
          ** in the index refer to the table to which the index belongs */
          iTab = pParse->iSelfTab - 1;
        }
      }
      return sqlite3ExprCodeGetColumn(pParse, pExpr->y.pTab,
                               pExpr->iColumn, iTab, target,
                               pExpr->op2);




    }
    case TK_INTEGER: {
      codeInteger(pParse, pExpr, 0, target);
      return target;
    }
    case TK_TRUEFALSE: {
      sqlite3VdbeAddOp2(v, OP_Integer, sqlite3ExprTruthValue(pExpr), target);







>







<

>







 







|


>
>
>
>







3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619

3620
3621
3622
3623
3624
3625
3626
3627
3628
....
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
                              pCol->iSorterColumn, target);
        return target;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      int iTab = pExpr->iTable;
      int iReg;
      if( ExprHasProperty(pExpr, EP_FixedCol) ){
        /* 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 aff;
        iReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft,target);
        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";
................................................................................
          }
        }else{
          /* Coding an expression that is part of an index where column names
          ** in the index refer to the table to which the index belongs */
          iTab = pParse->iSelfTab - 1;
        }
      }
      iReg = sqlite3ExprCodeGetColumn(pParse, pExpr->y.pTab,
                               pExpr->iColumn, iTab, target,
                               pExpr->op2);
      if( pExpr->y.pTab==0 && pExpr->affExpr==SQLITE_AFF_REAL ){
        sqlite3VdbeAddOp1(v, OP_RealAffinity, iReg);
      }
      return iReg;
    }
    case TK_INTEGER: {
      codeInteger(pParse, pExpr, 0, target);
      return target;
    }
    case TK_TRUEFALSE: {
      sqlite3VdbeAddOp2(v, OP_Integer, sqlite3ExprTruthValue(pExpr), target);

Changes to test/gencol1.test.

437
438
439
440
441
442
443


















444
445
446
do_execsql_test gencol1-16.30 {
  INSERT INTO t1(c1) VALUES(1),(NULL);
  SELECT * FROM t1;
} {1 0 {} 1}
do_execsql_test gencol1-16.40 {
  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1 ON c0=c1;
} {0 {} {}}




















finish_test







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



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
do_execsql_test gencol1-16.30 {
  INSERT INTO t1(c1) VALUES(1),(NULL);
  SELECT * FROM t1;
} {1 0 {} 1}
do_execsql_test gencol1-16.40 {
  SELECT c0, c1, c2 FROM t0 LEFT JOIN t1 ON c0=c1;
} {0 {} {}}

# 2019-12-20 ticket e0a8120553f4b082
# Generated columns with REAL affinity need to have an OP_RealAffinity
# opcode applied, even when the column value is extracted from an index.
#
reset_db
do_execsql_test gencol1-17.10 {
  CREATE TABLE t0(c0 REAL AS(1) UNIQUE, c1 INT);
  INSERT INTO t0 VALUES('');
  SELECT quote(c0), quote(c1) from t0;
} {1.0 ''}
do_execsql_test gencol1-17.20 {
  SELECT *, (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0) FROM t0;
} {1.0 {} 0}
do_execsql_test gencol1-17.30 {
  SELECT * FROM t0 WHERE (1 BETWEEN CAST(t0.c0 AS TEXT) AND t0.c0);
} {}



finish_test