1# 2007 Febuary 24
2#
3# The author disclaims copyright to this source code.  In place of
4# 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 values
14# are pulled out of the database correctly.
15#
16# Long ago, the OP_Column opcode was sufficient to pull out the
17# value of a table column.  But then we added the ALTER TABLE ADD COLUMN
18# feature.  An added column might not actually exist in every row,
19# and so the OP_Column opcode has to contain a default value.  Later
20# still we added a feature whereby a REAL value with no fractional
21# part is stored in the database file as an integer to save space.
22# After extracting the value, we have to call OP_RealAffinity to
23# convert it back to a REAL.
24#
25# The sqlite3ExprCodeGetColumn() routine was added to take care of
26# all of the complications above.  The tests in this file attempt
27# to verify that sqlite3ExprCodeGetColumn() is used instead of a
28# raw OP_Column in all places where a table column is extracted from
29# 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.tcl
35
36ifcapable !altertable {
37  finish_test
38  return
39}
40
41# Create sample data.  Verify that the default value and type of an added
42# 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 accessed
70# 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 one
91# 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_test
109