/ Check-in [e11bbf17]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Make sure that integer values are converted to real when pulled from a REAL table column by GROUP BY. Ticket #2251. Also make sure default values are correctly expanded. There may be other places in the code where this issue comes up - we need to look further. (CVS 3659)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e11bbf174c5a2fa75e3d1dd450c8b2a18f40e4da
User & Date: drh 2007-02-24 11:52:53
Context
2007-02-24
13:23
Make sure the INSERT xfer optimization does not trigger if the CHECK constraints on the two tables are not identical. Ticket #2252. (CVS 3660) check-in: 6fc18275 user: drh tags: trunk
11:52
Make sure that integer values are converted to real when pulled from a REAL table column by GROUP BY. Ticket #2251. Also make sure default values are correctly expanded. There may be other places in the code where this issue comes up - we need to look further. (CVS 3659) check-in: e11bbf17 user: drh tags: trunk
2007-02-23
23:13
Disable the OR optimization if it would conflict with column affinity coercions. Ticket #2249. Additional cleanup and testing of the OR optimization. (CVS 3658) check-in: 908daaa9 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1514
1515
1516
1517
1518
1519
1520

























1521
1522
1523
1524
1525
1526
1527
....
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575

1576
1577
1578
1579
1580
1581
1582
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.277 2007/02/23 03:00:45 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
  }else if( sqlite3FitsIn64Bits(z) ){
    sqlite3VdbeOp3(v, OP_Int64, 0, 0, z, n);
  }else{
    sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
  }
}


























/*
** Generate code into the current Vdbe to evaluate the given
** expression and leave the result on the top of stack.
**
** This code depends on the fact that certain token values (ex: TK_EQ)
** are the same as opcode values (ex: OP_Eq) that implement the corresponding
** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
................................................................................
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */
        assert( pParse->ckOffset>0 );
        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
      }else if( pExpr->iColumn>=0 ){
        Table *pTab = pExpr->pTab;
        int iCol = pExpr->iColumn;
        int op = (pTab && IsVirtual(pTab)) ? OP_VColumn : OP_Column;
        sqlite3VdbeAddOp(v, op, pExpr->iTable, iCol);
        sqlite3ColumnDefault(v, pTab, iCol);
#ifndef SQLITE_OMIT_FLOATING_POINT
        if( pTab && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){
          sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
        }
#endif
      }else{
        Table *pTab = pExpr->pTab;
        int op = (pTab && IsVirtual(pTab)) ? OP_VRowid : OP_Rowid;
        sqlite3VdbeAddOp(v, op, pExpr->iTable, 0);

      }
      break;
    }
    case TK_INTEGER: {
      codeInteger(v, (char*)pExpr->token.z, pExpr->token.n);
      break;
    }







|







 







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







 







<
<
<
<
<
<
<
<
<
<
<

<
<
<
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
....
1579
1580
1581
1582
1583
1584
1585











1586



1587
1588
1589
1590
1591
1592
1593
1594
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.278 2007/02/24 11:52:53 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
  }else if( sqlite3FitsIn64Bits(z) ){
    sqlite3VdbeOp3(v, OP_Int64, 0, 0, z, n);
  }else{
    sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
  }
}


/*
** Generate code that will extract the iColumn-th column from
** table pTab and push that column value on the stack.  There
** is an open cursor to pTab in iTable.  If iColumn<0 then
** code is generated that extracts the rowid.
*/
void sqlite3ExprCodeGetColumn(Vdbe *v, Table *pTab, int iColumn, int iTable){
  if( iColumn<0 ){
    int op = (pTab && IsVirtual(pTab)) ? OP_VRowid : OP_Rowid;
    sqlite3VdbeAddOp(v, op, iTable, 0);
  }else if( pTab==0 ){
    sqlite3VdbeAddOp(v, OP_Column, iTable, iColumn);
  }else{
    int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
    sqlite3VdbeAddOp(v, op, iTable, iColumn);
    sqlite3ColumnDefault(v, pTab, iColumn);
#ifndef SQLITE_OMIT_FLOATING_POINT
    if( pTab->aCol[iColumn].affinity==SQLITE_AFF_REAL ){
      sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
    }
#endif
  }
}

/*
** Generate code into the current Vdbe to evaluate the given
** expression and leave the result on the top of stack.
**
** This code depends on the fact that certain token values (ex: TK_EQ)
** are the same as opcode values (ex: OP_Eq) that implement the corresponding
** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
................................................................................
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */
        assert( pParse->ckOffset>0 );
        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);











      }else{



        sqlite3ExprCodeGetColumn(v, pExpr->pTab, pExpr->iColumn, pExpr->iTable);
      }
      break;
    }
    case TK_INTEGER: {
      codeInteger(v, (char*)pExpr->token.z, pExpr->token.n);
      break;
    }

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.327 2007/02/14 09:19:36 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
        groupBySort = 1;
        sqlite3ExprCodeExprList(pParse, pGroupBy);
        sqlite3VdbeAddOp(v, OP_Sequence, sAggInfo.sortingIdx, 0);
        j = pGroupBy->nExpr+1;
        for(i=0; i<sAggInfo.nColumn; i++){
          struct AggInfo_col *pCol = &sAggInfo.aCol[i];
          if( pCol->iSorterColumn<j ) continue;
          if( pCol->iColumn<0 ){
            sqlite3VdbeAddOp(v, OP_Rowid, pCol->iTable, 0);
          }else{
            Table *pTab = pCol->pTab;
            int op = (pTab && IsVirtual(pTab)) ? OP_VColumn : OP_Column;
            sqlite3VdbeAddOp(v, op, pCol->iTable, pCol->iColumn);
          }
          j++;
        }
        sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
        sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
        sqlite3WhereEnd(pWInfo);
        sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "# GROUP BY sort"));







|







 







|
<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3140
3141
3142
3143
3144
3145
3146
3147






3148
3149
3150
3151
3152
3153
3154
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.328 2007/02/24 11:52:54 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
        groupBySort = 1;
        sqlite3ExprCodeExprList(pParse, pGroupBy);
        sqlite3VdbeAddOp(v, OP_Sequence, sAggInfo.sortingIdx, 0);
        j = pGroupBy->nExpr+1;
        for(i=0; i<sAggInfo.nColumn; i++){
          struct AggInfo_col *pCol = &sAggInfo.aCol[i];
          if( pCol->iSorterColumn<j ) continue;
          sqlite3ExprCodeGetColumn(v, pCol->pTab, pCol->iColumn, pCol->iTable);






          j++;
        }
        sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
        sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
        sqlite3WhereEnd(pWInfo);
        sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
        VdbeComment((v, "# GROUP BY sort"));

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1647
1648
1649
1650
1651
1652
1653

1654
1655
1656
1657
1658
1659
1660
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.537 2007/02/14 09:19:36 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Extra interface definitions for those who need them
*/
................................................................................
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**);
void sqlite3WhereEnd(WhereInfo*);

void sqlite3ExprCode(Parse*, Expr*);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
void sqlite3NextedParse(Parse*, const char*, ...);
Table *sqlite3FindTable(sqlite3*,const char*, const char*);







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.538 2007/02/24 11:52:55 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Extra interface definitions for those who need them
*/
................................................................................
Table *sqlite3SrcListLookup(Parse*, SrcList*);
int sqlite3IsReadOnly(Parse*, Table*, int);
void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**);
void sqlite3WhereEnd(WhereInfo*);
void sqlite3ExprCodeGetColumn(Vdbe*, Table*, int, int);
void sqlite3ExprCode(Parse*, Expr*);
void sqlite3ExprCodeAndCache(Parse*, Expr*);
int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
void sqlite3NextedParse(Parse*, const char*, ...);
Table *sqlite3FindTable(sqlite3*,const char*, const char*);

Changes to test/alter.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
625
626
627
628
629
630
631



















632
633
634
#    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.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.17 2006/02/09 02:56:03 drh Exp $
#

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

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
................................................................................
  execsql {
    CREATE TABLE t1(a TEXT COLLATE BINARY);
    ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
    INSERT INTO t1 VALUES(1,'2');
    SELECT typeof(a), a, typeof(b), b FROM t1;
  }
} {text 1 integer 2}





















finish_test







|







 







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



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.18 2007/02/24 11:52:55 drh Exp $
#

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

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
................................................................................
  execsql {
    CREATE TABLE t1(a TEXT COLLATE BINARY);
    ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
    INSERT INTO t1 VALUES(1,'2');
    SELECT typeof(a), a, typeof(b), b FROM t1;
  }
} {text 1 integer 2}

# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
# a default value that the default value is used by aggregate functions.
#
do_test alter-8.1 {
  execsql {
    CREATE TABLE t2(a INTEGER);
    INSERT INTO t2 VALUES(1);
    INSERT INTO t2 VALUES(1);
    INSERT INTO t2 VALUES(2);
    ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
    SELECT sum(b) FROM t2;
  }
} {27}
do_test alter-8.2 {
  execsql {
    SELECT a, sum(b) FROM t2 GROUP BY a;
  }
} {1 18 2 9}


finish_test

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
231
232
233
234
235
236
237





238


















239
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.19 2006/04/11 14:16:22 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
} {}
do_test select3-7.2 {
  execsql {
    SELECT a, sum(b) FROM t2 WHERE b=5;
  }
} {{} {}}

























finish_test







|







 







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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.20 2007/02/24 11:52:55 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
} {}
do_test select3-7.2 {
  execsql {
    SELECT a, sum(b) FROM t2 WHERE b=5;
  }
} {{} {}}

# If a table column is of type REAL but we are storing integer values
# in it, the values are stored as integers to take up less space.  The
# values are converted by to REAL as they are read out of the table.
# Make sure the GROUP BY clause does this conversion correctly.
# Ticket #2251.
#
do_test select3-8.1 {
  execsql {
    CREATE TABLE A (
      A1 DOUBLE,
      A2 VARCHAR COLLATE NOCASE,
      A3 DOUBLE
    );
    INSERT INTO A VALUES(39136,'ABC',1201900000);
    INSERT INTO A VALUES(39136,'ABC',1207000000);
    SELECT typeof(sum(a3)) FROM a;
  }
} {real}
do_test select3-8.2 {
  execsql {
    SELECT typeof(sum(a3)) FROM a GROUP BY a1;
  }
} {real}

finish_test