/ Check-in [e11bbf17]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.277 2007/02/23 03:00:45 drh Exp $
           15  +** $Id: expr.c,v 1.278 2007/02/24 11:52:53 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
  1514   1514     }else if( sqlite3FitsIn64Bits(z) ){
  1515   1515       sqlite3VdbeOp3(v, OP_Int64, 0, 0, z, n);
  1516   1516     }else{
  1517   1517       sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
  1518   1518     }
  1519   1519   }
  1520   1520   
         1521  +
         1522  +/*
         1523  +** Generate code that will extract the iColumn-th column from
         1524  +** table pTab and push that column value on the stack.  There
         1525  +** is an open cursor to pTab in iTable.  If iColumn<0 then
         1526  +** code is generated that extracts the rowid.
         1527  +*/
         1528  +void sqlite3ExprCodeGetColumn(Vdbe *v, Table *pTab, int iColumn, int iTable){
         1529  +  if( iColumn<0 ){
         1530  +    int op = (pTab && IsVirtual(pTab)) ? OP_VRowid : OP_Rowid;
         1531  +    sqlite3VdbeAddOp(v, op, iTable, 0);
         1532  +  }else if( pTab==0 ){
         1533  +    sqlite3VdbeAddOp(v, OP_Column, iTable, iColumn);
         1534  +  }else{
         1535  +    int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
         1536  +    sqlite3VdbeAddOp(v, op, iTable, iColumn);
         1537  +    sqlite3ColumnDefault(v, pTab, iColumn);
         1538  +#ifndef SQLITE_OMIT_FLOATING_POINT
         1539  +    if( pTab->aCol[iColumn].affinity==SQLITE_AFF_REAL ){
         1540  +      sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
         1541  +    }
         1542  +#endif
         1543  +  }
         1544  +}
         1545  +
  1521   1546   /*
  1522   1547   ** Generate code into the current Vdbe to evaluate the given
  1523   1548   ** expression and leave the result on the top of stack.
  1524   1549   **
  1525   1550   ** This code depends on the fact that certain token values (ex: TK_EQ)
  1526   1551   ** are the same as opcode values (ex: OP_Eq) that implement the corresponding
  1527   1552   ** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
................................................................................
  1554   1579         /* Otherwise, fall thru into the TK_COLUMN case */
  1555   1580       }
  1556   1581       case TK_COLUMN: {
  1557   1582         if( pExpr->iTable<0 ){
  1558   1583           /* This only happens when coding check constraints */
  1559   1584           assert( pParse->ckOffset>0 );
  1560   1585           sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
  1561         -      }else if( pExpr->iColumn>=0 ){
  1562         -        Table *pTab = pExpr->pTab;
  1563         -        int iCol = pExpr->iColumn;
  1564         -        int op = (pTab && IsVirtual(pTab)) ? OP_VColumn : OP_Column;
  1565         -        sqlite3VdbeAddOp(v, op, pExpr->iTable, iCol);
  1566         -        sqlite3ColumnDefault(v, pTab, iCol);
  1567         -#ifndef SQLITE_OMIT_FLOATING_POINT
  1568         -        if( pTab && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){
  1569         -          sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
  1570         -        }
  1571         -#endif
  1572   1586         }else{
  1573         -        Table *pTab = pExpr->pTab;
  1574         -        int op = (pTab && IsVirtual(pTab)) ? OP_VRowid : OP_Rowid;
  1575         -        sqlite3VdbeAddOp(v, op, pExpr->iTable, 0);
         1587  +        sqlite3ExprCodeGetColumn(v, pExpr->pTab, pExpr->iColumn, pExpr->iTable);
  1576   1588         }
  1577   1589         break;
  1578   1590       }
  1579   1591       case TK_INTEGER: {
  1580   1592         codeInteger(v, (char*)pExpr->token.z, pExpr->token.n);
  1581   1593         break;
  1582   1594       }

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.327 2007/02/14 09:19:36 danielk1977 Exp $
           15  +** $Id: select.c,v 1.328 2007/02/24 11:52:54 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Delete all the content of a Select structure but do not deallocate
    22     22   ** the select structure itself.
................................................................................
  3140   3140           groupBySort = 1;
  3141   3141           sqlite3ExprCodeExprList(pParse, pGroupBy);
  3142   3142           sqlite3VdbeAddOp(v, OP_Sequence, sAggInfo.sortingIdx, 0);
  3143   3143           j = pGroupBy->nExpr+1;
  3144   3144           for(i=0; i<sAggInfo.nColumn; i++){
  3145   3145             struct AggInfo_col *pCol = &sAggInfo.aCol[i];
  3146   3146             if( pCol->iSorterColumn<j ) continue;
  3147         -          if( pCol->iColumn<0 ){
  3148         -            sqlite3VdbeAddOp(v, OP_Rowid, pCol->iTable, 0);
  3149         -          }else{
  3150         -            Table *pTab = pCol->pTab;
  3151         -            int op = (pTab && IsVirtual(pTab)) ? OP_VColumn : OP_Column;
  3152         -            sqlite3VdbeAddOp(v, op, pCol->iTable, pCol->iColumn);
  3153         -          }
         3147  +          sqlite3ExprCodeGetColumn(v, pCol->pTab, pCol->iColumn, pCol->iTable);
  3154   3148             j++;
  3155   3149           }
  3156   3150           sqlite3VdbeAddOp(v, OP_MakeRecord, j, 0);
  3157   3151           sqlite3VdbeAddOp(v, OP_IdxInsert, sAggInfo.sortingIdx, 0);
  3158   3152           sqlite3WhereEnd(pWInfo);
  3159   3153           sqlite3VdbeAddOp(v, OP_Sort, sAggInfo.sortingIdx, addrEnd);
  3160   3154           VdbeComment((v, "# GROUP BY sort"));

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.537 2007/02/14 09:19:36 danielk1977 Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.538 2007/02/24 11:52:55 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Extra interface definitions for those who need them
    21     21   */
................................................................................
  1647   1647   Table *sqlite3SrcListLookup(Parse*, SrcList*);
  1648   1648   int sqlite3IsReadOnly(Parse*, Table*, int);
  1649   1649   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  1650   1650   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  1651   1651   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  1652   1652   WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**);
  1653   1653   void sqlite3WhereEnd(WhereInfo*);
         1654  +void sqlite3ExprCodeGetColumn(Vdbe*, Table*, int, int);
  1654   1655   void sqlite3ExprCode(Parse*, Expr*);
  1655   1656   void sqlite3ExprCodeAndCache(Parse*, Expr*);
  1656   1657   int sqlite3ExprCodeExprList(Parse*, ExprList*);
  1657   1658   void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
  1658   1659   void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
  1659   1660   void sqlite3NextedParse(Parse*, const char*, ...);
  1660   1661   Table *sqlite3FindTable(sqlite3*,const char*, const char*);

Changes to test/alter.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #*************************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is testing the ALTER TABLE statement.
    13     13   #
    14         -# $Id: alter.test,v 1.17 2006/02/09 02:56:03 drh Exp $
           14  +# $Id: alter.test,v 1.18 2007/02/24 11:52:55 drh Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
    21     21   ifcapable !altertable {
................................................................................
   625    625     execsql {
   626    626       CREATE TABLE t1(a TEXT COLLATE BINARY);
   627    627       ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE;
   628    628       INSERT INTO t1 VALUES(1,'2');
   629    629       SELECT typeof(a), a, typeof(b), b FROM t1;
   630    630     }
   631    631   } {text 1 integer 2}
          632  +
          633  +# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has
          634  +# a default value that the default value is used by aggregate functions.
          635  +#
          636  +do_test alter-8.1 {
          637  +  execsql {
          638  +    CREATE TABLE t2(a INTEGER);
          639  +    INSERT INTO t2 VALUES(1);
          640  +    INSERT INTO t2 VALUES(1);
          641  +    INSERT INTO t2 VALUES(2);
          642  +    ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9;
          643  +    SELECT sum(b) FROM t2;
          644  +  }
          645  +} {27}
          646  +do_test alter-8.2 {
          647  +  execsql {
          648  +    SELECT a, sum(b) FROM t2 GROUP BY a;
          649  +  }
          650  +} {1 18 2 9}
   632    651   
   633    652   
   634    653   finish_test

Changes to test/select3.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing aggregate functions and the
    13     13   # GROUP BY and HAVING clauses of SELECT statements.
    14     14   #
    15         -# $Id: select3.test,v 1.19 2006/04/11 14:16:22 drh Exp $
           15  +# $Id: select3.test,v 1.20 2007/02/24 11:52:55 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Build some test data
    21     21   #
    22     22   do_test select3-1.0 {
................................................................................
   231    231   } {}
   232    232   do_test select3-7.2 {
   233    233     execsql {
   234    234       SELECT a, sum(b) FROM t2 WHERE b=5;
   235    235     }
   236    236   } {{} {}}
   237    237   
          238  +# If a table column is of type REAL but we are storing integer values
          239  +# in it, the values are stored as integers to take up less space.  The
          240  +# values are converted by to REAL as they are read out of the table.
          241  +# Make sure the GROUP BY clause does this conversion correctly.
          242  +# Ticket #2251.
          243  +#
          244  +do_test select3-8.1 {
          245  +  execsql {
          246  +    CREATE TABLE A (
          247  +      A1 DOUBLE,
          248  +      A2 VARCHAR COLLATE NOCASE,
          249  +      A3 DOUBLE
          250  +    );
          251  +    INSERT INTO A VALUES(39136,'ABC',1201900000);
          252  +    INSERT INTO A VALUES(39136,'ABC',1207000000);
          253  +    SELECT typeof(sum(a3)) FROM a;
          254  +  }
          255  +} {real}
          256  +do_test select3-8.2 {
          257  +  execsql {
          258  +    SELECT typeof(sum(a3)) FROM a GROUP BY a1;
          259  +  }
          260  +} {real}
   238    261   
   239    262   finish_test