AryaWu/sqlite

1# 2007 Febuary 242#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file implements tests to verify that table column values14# are pulled out of the database correctly.15#16# Long ago, the OP_Column opcode was sufficient to pull out the17# value of a table column.  But then we added the ALTER TABLE ADD COLUMN18# feature.  An added column might not actually exist in every row,19# and so the OP_Column opcode has to contain a default value.  Later20# still we added a feature whereby a REAL value with no fractional21# part is stored in the database file as an integer to save space.22# After extracting the value, we have to call OP_RealAffinity to23# convert it back to a REAL.24#25# The sqlite3ExprCodeGetColumn() routine was added to take care of26# all of the complications above.  The tests in this file attempt27# to verify that sqlite3ExprCodeGetColumn() is used instead of a28# raw OP_Column in all places where a table column is extracted from29# the database.30#31# $Id: tkt2251.test,v 1.2 2007/09/12 17:01:45 danielk1977 Exp $32 33set testdir [file dirname $argv0]34source $testdir/tester.tcl35 36ifcapable !altertable {37  finish_test38  return39}40 41# Create sample data.  Verify that the default value and type of an added42# column is correct for aggregates.43do_test tkt2251-1.1 {44  execsql {45    CREATE TABLE t1(a INTEGER);46    INSERT INTO t1 VALUES(1);47    INSERT INTO t1 VALUES(1);48    INSERT INTO t1 VALUES(2);49    INSERT INTO t1 VALUES(9);50    INSERT INTO t1 VALUES(9);51    INSERT INTO t1 VALUES(9);52    INSERT INTO t1 VALUES(3);53    INSERT INTO t1 VALUES(2);54    ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0;55    SELECT avg(b), typeof(avg(b)) FROM t1;56  }57} {4.0 real}58do_test tkt2251-1.2 {59  execsql {60    SELECT sum(b), typeof(sum(b)) FROM t1;61  }62} {32.0 real}63do_test tkt2251-1.3 {64  execsql {65    SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a;66  }67} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real}68 69# Make sure that the REAL value comes out when values are accessed70# by index.71#72do_test tkt2251-2.1 {73  execsql {74    SELECT b, typeof(b) FROM t1 WHERE a=3;75  }76} {4.0 real}77do_test tkt2251-2.2 {78  execsql {79    CREATE INDEX t1i1 ON t1(a,b);80    SELECT b, typeof(b) FROM t1 WHERE a=3;81  }82} {4.0 real}83do_test tkt2251-2.3 {84  execsql {85    REINDEX;86    SELECT b, typeof(b) FROM t1 WHERE a=3;87  }88} {4.0 real}89 90# Make sure the correct REAL value is used when copying from one91# table to another.92#93do_test tkt2251-3.1 {94  execsql {95    CREATE TABLE t2(x,y);96    INSERT INTO t2 SELECT * FROM t1;97    SELECT y, typeof(y) FROM t2 WHERE x=3;98  }99} {4.0 real}100do_test tkt2251-3.2 {101  execsql {102    CREATE TABLE t3 AS SELECT * FROM t1;103    SELECT b, typeof(b) FROM t3 WHERE a=3;104  }105} {4.0 real}106 107 108finish_test109