gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt2251.test (about)

     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  
    33  set testdir [file dirname $argv0]
    34  source $testdir/tester.tcl
    35  
    36  ifcapable !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.
    43  do_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}
    58  do_test tkt2251-1.2 {
    59    execsql {
    60      SELECT sum(b), typeof(sum(b)) FROM t1;
    61    }
    62  } {32.0 real}
    63  do_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  #
    72  do_test tkt2251-2.1 {
    73    execsql {
    74      SELECT b, typeof(b) FROM t1 WHERE a=3;
    75    }
    76  } {4.0 real}
    77  do_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}
    83  do_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  #
    93  do_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}
   100  do_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  
   108  finish_test