1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/tkt2251.test	Fri Jun 15 03:10:57 2012 +0200
     1.3 @@ -0,0 +1,108 @@
     1.4 +# 2007 Febuary 24
     1.5 +#
     1.6 +# The author disclaims copyright to this source code.  In place of
     1.7 +# a legal notice, here is a blessing:
     1.8 +#
     1.9 +#    May you do good and not evil.
    1.10 +#    May you find forgiveness for yourself and forgive others.
    1.11 +#    May you share freely, never taking more than you give.
    1.12 +#
    1.13 +#***********************************************************************
    1.14 +# This file implements regression tests for SQLite library.
    1.15 +#
    1.16 +# This file implements tests to verify that table column values
    1.17 +# are pulled out of the database correctly.
    1.18 +#
    1.19 +# Long ago, the OP_Column opcode was sufficient to pull out the
    1.20 +# value of a table column.  But then we added the ALTER TABLE ADD COLUMN
    1.21 +# feature.  An added column might not actually exist in every row,
    1.22 +# and so the OP_Column opcode has to contain a default value.  Later
    1.23 +# still we added a feature whereby a REAL value with no fractional
    1.24 +# part is stored in the database file as an integer to save space.
    1.25 +# After extracting the value, we have to call OP_RealAffinity to
    1.26 +# convert it back to a REAL.
    1.27 +#
    1.28 +# The sqlite3ExprCodeGetColumn() routine was added to take care of
    1.29 +# all of the complications above.  The tests in this file attempt
    1.30 +# to verify that sqlite3ExprCodeGetColumn() is used instead of a
    1.31 +# raw OP_Column in all places where a table column is extracted from
    1.32 +# the database.
    1.33 +#
    1.34 +# $Id: tkt2251.test,v 1.2 2007/09/12 17:01:45 danielk1977 Exp $
    1.35 +
    1.36 +set testdir [file dirname $argv0]
    1.37 +source $testdir/tester.tcl
    1.38 +
    1.39 +ifcapable !altertable {
    1.40 +  finish_test
    1.41 +  return
    1.42 +}
    1.43 +
    1.44 +# Create sample data.  Verify that the default value and type of an added
    1.45 +# column is correct for aggregates.
    1.46 +do_test tkt2251-1.1 {
    1.47 +  execsql {
    1.48 +    CREATE TABLE t1(a INTEGER);
    1.49 +    INSERT INTO t1 VALUES(1);
    1.50 +    INSERT INTO t1 VALUES(1);
    1.51 +    INSERT INTO t1 VALUES(2);
    1.52 +    INSERT INTO t1 VALUES(9);
    1.53 +    INSERT INTO t1 VALUES(9);
    1.54 +    INSERT INTO t1 VALUES(9);
    1.55 +    INSERT INTO t1 VALUES(3);
    1.56 +    INSERT INTO t1 VALUES(2);
    1.57 +    ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0;
    1.58 +    SELECT avg(b), typeof(avg(b)) FROM t1;
    1.59 +  }
    1.60 +} {4.0 real}
    1.61 +do_test tkt2251-1.2 {
    1.62 +  execsql {
    1.63 +    SELECT sum(b), typeof(sum(b)) FROM t1;
    1.64 +  }
    1.65 +} {32.0 real}
    1.66 +do_test tkt2251-1.3 {
    1.67 +  execsql {
    1.68 +    SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a;
    1.69 +  }
    1.70 +} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real}
    1.71 +
    1.72 +# Make sure that the REAL value comes out when values are accessed
    1.73 +# by index.
    1.74 +#
    1.75 +do_test tkt2251-2.1 {
    1.76 +  execsql {
    1.77 +    SELECT b, typeof(b) FROM t1 WHERE a=3;
    1.78 +  }
    1.79 +} {4.0 real}
    1.80 +do_test tkt2251-2.2 {
    1.81 +  execsql {
    1.82 +    CREATE INDEX t1i1 ON t1(a,b);
    1.83 +    SELECT b, typeof(b) FROM t1 WHERE a=3;
    1.84 +  }
    1.85 +} {4.0 real}
    1.86 +do_test tkt2251-2.3 {
    1.87 +  execsql {
    1.88 +    REINDEX;
    1.89 +    SELECT b, typeof(b) FROM t1 WHERE a=3;
    1.90 +  }
    1.91 +} {4.0 real}
    1.92 +
    1.93 +# Make sure the correct REAL value is used when copying from one
    1.94 +# table to another.
    1.95 +#
    1.96 +do_test tkt2251-3.1 {
    1.97 +  execsql {
    1.98 +    CREATE TABLE t2(x,y);
    1.99 +    INSERT INTO t2 SELECT * FROM t1;
   1.100 +    SELECT y, typeof(y) FROM t2 WHERE x=3;
   1.101 +  }
   1.102 +} {4.0 real}
   1.103 +do_test tkt2251-3.2 {
   1.104 +  execsql {
   1.105 +    CREATE TABLE t3 AS SELECT * FROM t1;
   1.106 +    SELECT b, typeof(b) FROM t3 WHERE a=3;
   1.107 +  }
   1.108 +} {4.0 real}
   1.109 +
   1.110 +
   1.111 +finish_test