Index: src/insert.c
==================================================================
--- src/insert.c
+++ src/insert.c
@@ -10,11 +10,11 @@
 **
 *************************************************************************
 ** This file contains C code routines that are called by the parser
 ** to handle INSERT statements in SQLite.
 **
-** $Id: insert.c,v 1.175 2007/02/24 13:53:05 drh Exp $
+** $Id: insert.c,v 1.176 2007/02/24 15:18:50 drh Exp $
 */
 #include "sqliteInt.h"
 
 /*
 ** Set P3 of the most recently inserted opcode to a column affinity
@@ -1460,11 +1460,11 @@
     }
     if( pSrcIdx==0 ){
       return 0;    /* pDestIdx has no corresponding index in pSrc */
     }
   }
-  if( !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
+  if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
     return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
   }
 
   /* If we get this far, it means either:
   **

Index: test/insert4.test
==================================================================
--- test/insert4.test
+++ test/insert4.test
@@ -9,11 +9,11 @@
 #
 #***********************************************************************
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing the INSERT transfer optimization.
 #
-# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $
+# $Id: insert4.test,v 1.3 2007/02/24 15:18:50 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
 # The sqlite3_xferopt_count variable is incremented whenever the 
@@ -23,19 +23,28 @@
 # set to N.
 #
 proc xferopt_test {testname N} {
   do_test $testname {set ::sqlite3_xferopt_count} $N
 }
+
+# Create tables used for testing.
+#
+execsql {
+  CREATE TABLE t1(a int, b int, check(b>a));
+  CREATE TABLE t2(x int, y int);
+  CREATE VIEW v2 AS SELECT y, x FROM t2;
+  CREATE TABLE t3(a int, b int);
+}
 
 # Ticket #2252.  Make sure the an INSERT from identical tables
 # does not violate constraints.
 #
 do_test insert4-1.1 {
   set sqlite3_xferopt_count 0
   execsql {
-    CREATE TABLE t1(a int, b int, check(b>a));
-    CREATE TABLE t2(x int, y int);
+    DELETE FROM t1;
+    DELETE FROM t2;
     INSERT INTO t2 VALUES(9,1);
   }
   catchsql {
     INSERT INTO t1 SELECT * FROM t2;
   }
@@ -51,30 +60,31 @@
 # when it is not a valid optimization.
 #
 # The SELECT must be against a real table.
 do_test insert4-2.1.1 {
   execsql {
+    DELETE FROM t1;
     INSERT INTO t1 SELECT 4, 8;
     SELECT * FROM t1;
   }
 } {4 8}
 xferopt_test insert4-2.1.2  0
 do_test insert4-2.2.1 {
   catchsql {
     DELETE FROM t1;
-    CREATE VIEW v1 AS SELECT y, x FROM t2;
-    INSERT INTO t1 SELECT * FROM v1;
+    INSERT INTO t1 SELECT * FROM v2;
     SELECT * FROM t1;
   }
 } {0 {1 9}}
 xferopt_test insert4-2.2.2 0
 
 # Do not run the transfer optimization if there is a LIMIT clause
 #
 do_test insert4-2.3.1 {
   execsql {
-    CREATE TABLE t3(a int, b int);
+    DELETE FROM t2;
+    INSERT INTO t2 VALUES(9,1);
     INSERT INTO t2 SELECT y, x FROM t2;
     INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
     SELECT * FROM t3;
   }
 } {9 1}
@@ -103,22 +113,118 @@
     DELETE FROM t1;
     INSERT INTO t1 SELECT DISTINCT * FROM t2;
   }
 } {1 {constraint failed}}
 xferopt_test insert4-2.4.4 0
+
+# The following procedure constructs two tables then tries to transfer
+# data from one table to the other.  Checks are made to make sure the
+# transfer is successful and that the transfer optimization was used or
+# not, as appropriate.
+#
+#     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA 
+#
+# The TESTID argument is the symbolic name for this test.  The XFER-USED
+# argument is true if the transfer optimization should be employed and
+# false if not.  INIT-DATA is a single row of data that is to be 
+# transfered.  DEST-SCHEMA and SRC-SCHEMA are table declarations for
+# the destination and source tables.
+#
+proc xfer_check {testid xferused initdata destschema srcschema} {
+  execsql "CREATE TABLE dest($destschema)"
+  execsql "CREATE TABLE src($srcschema)"
+  execsql "INSERT INTO src VALUES([join $initdata ,])"
+  set ::sqlite3_xferopt_count 0
+  do_test $testid.1 {
+    execsql {
+      INSERT INTO dest SELECT * FROM src;
+      SELECT * FROM dest;
+    }
+  } $initdata
+  do_test $testid.2 {
+    set ::sqlite3_xferopt_count
+  } $xferused
+  execsql {
+    DROP TABLE dest;
+    DROP TABLE src;
+  }
+}
+
 
 # Do run the transfer optimization if tables have identical
 # CHECK constraints.
 #
-do_test insert4-3.1.1 {
-  set sqlite3_xferopt_count 0
-  execsql {
-    DELETE FROM t1;
-    INSERT INTO t1 VALUES(1,9);
-    CREATE TABLE t4(m int, n int, CHECK(n>m));
-    INSERT INTO t4 SELECT * FROM t1;
-    SELECT * FROM t4;
-  }
-} {1 9}
-xferopt_test insert4-3.1.2 1
+xfer_check insert4-3.1 1 {1 9} \
+    {a int, b int CHECK(b>a)} \
+    {x int, y int CHECK(y>x)}
+xfer_check insert4-3.2 1 {1 9} \
+    {a int, b int CHECK(b>a)} \
+    {x int CHECK(y>x), y int}
+
+# Do run the transfer optimization if the destination table lacks
+# any CHECK constraints regardless of whether or not there are CHECK
+# constraints on the source table.
+#
+xfer_check insert4-3.3 1 {1 9} \
+    {a int, b int} \
+    {x int, y int CHECK(y>x)}
+
+# Do run the transfer optimization if the destination table omits
+# NOT NULL constraints that the source table has.
+#
+xfer_check insert4-3.4 0 {1 9} \
+    {a int, b int CHECK(b>a)} \
+    {x int, y int}
+
+# Do not run the optimization if the destination has NOT NULL
+# constraints that the source table lacks.
+#
+xfer_check insert4-3.5 0 {1 9} \
+    {a int, b int NOT NULL} \
+    {x int, y int}
+xfer_check insert4-3.6 0 {1 9} \
+    {a int, b int NOT NULL} \
+    {x int NOT NULL, y int}
+xfer_check insert4-3.7 0 {1 9} \
+    {a int NOT NULL, b int NOT NULL} \
+    {x int NOT NULL, y int}
+xfer_check insert4-3.8 0 {1 9} \
+    {a int NOT NULL, b int} \
+    {x int, y int}
+
+
+# Do run the transfer optimization if the destination table and
+# source table have the same NOT NULL constraints or if the 
+# source table has extra NOT NULL constraints.
+#
+xfer_check insert4-3.9 1 {1 9} \
+    {a int, b int} \
+    {x int NOT NULL, y int}
+xfer_check insert4-3.10 1 {1 9} \
+    {a int, b int} \
+    {x int NOT NULL, y int NOT NULL}
+xfer_check insert4-3.11 1 {1 9} \
+    {a int NOT NULL, b int} \
+    {x int NOT NULL, y int NOT NULL}
+xfer_check insert4-3.12 1 {1 9} \
+    {a int, b int NOT NULL} \
+    {x int NOT NULL, y int NOT NULL}
+
+# Do not run the optimization if any corresponding table
+# columns have different affinities.
+#
+xfer_check insert4-3.20 0 {1 9} \
+    {a text, b int} \
+    {x int, b int}
+xfer_check insert4-3.21 0 {1 9} \
+    {a int, b int} \
+    {x text, b int}
+
+# "int" and "integer" are equivalent so the optimization should
+# run here.
+#
+xfer_check insert4-3.22 1 {1 9} \
+    {a int, b int} \
+    {x integer, b int}
+
 
 finish_test

ADDED   test/tkt2251.test
Index: test/tkt2251.test
==================================================================
--- /dev/null
+++ test/tkt2251.test
@@ -0,0 +1,103 @@
+# 2007 Febuary 24
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests to verify that table column values
+# are pulled out of the database correctly.
+#
+# Long ago, the OP_Column opcode was sufficient to pull out the
+# value of a table column.  But then we added the ALTER TABLE ADD COLUMN
+# feature.  An added column might not actually exist in every row,
+# and so the OP_Column opcode has to contain a default value.  Later
+# still we added a feature whereby a REAL value with no fractional
+# part is stored in the database file as an integer to save space.
+# After extracting the value, we have to call OP_RealAffinity to
+# convert it back to a REAL.
+#
+# The sqlite3ExprCodeGetColumn() routine was added to take care of
+# all of the complications above.  The tests in this file attempt
+# to verify that sqlite3ExprCodeGetColumn() is used instead of a
+# raw OP_Column in all places where a table column is extracted from
+# the database.
+#
+# $Id: tkt2251.test,v 1.1 2007/02/24 15:18:51 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Create sample data.  Verify that the default value and type of an added
+# column is correct for aggregates.
+do_test tkt2251-1.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER);
+    INSERT INTO t1 VALUES(1);
+    INSERT INTO t1 VALUES(1);
+    INSERT INTO t1 VALUES(2);
+    INSERT INTO t1 VALUES(9);
+    INSERT INTO t1 VALUES(9);
+    INSERT INTO t1 VALUES(9);
+    INSERT INTO t1 VALUES(3);
+    INSERT INTO t1 VALUES(2);
+    ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0;
+    SELECT avg(b), typeof(avg(b)) FROM t1;
+  }
+} {4.0 real}
+do_test tkt2251-1.2 {
+  execsql {
+    SELECT sum(b), typeof(sum(b)) FROM t1;
+  }
+} {32.0 real}
+do_test tkt2251-1.3 {
+  execsql {
+    SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a;
+  }
+} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real}
+
+# Make sure that the REAL value comes out when values are accessed
+# by index.
+#
+do_test tkt2251-2.1 {
+  execsql {
+    SELECT b, typeof(b) FROM t1 WHERE a=3;
+  }
+} {4.0 real}
+do_test tkt2251-2.2 {
+  execsql {
+    CREATE INDEX t1i1 ON t1(a,b);
+    SELECT b, typeof(b) FROM t1 WHERE a=3;
+  }
+} {4.0 real}
+do_test tkt2251-2.3 {
+  execsql {
+    REINDEX;
+    SELECT b, typeof(b) FROM t1 WHERE a=3;
+  }
+} {4.0 real}
+
+# Make sure the correct REAL value is used when copying from one
+# table to another.
+#
+do_test tkt2251-3.1 {
+  execsql {
+    CREATE TABLE t2(x,y);
+    INSERT INTO t2 SELECT * FROM t1;
+    SELECT y, typeof(y) FROM t2 WHERE x=3;
+  }
+} {4.0 real}
+do_test tkt2251-3.2 {
+  execsql {
+    CREATE TABLE t3 AS SELECT * FROM t1;
+    SELECT b, typeof(b) FROM t3 WHERE a=3;
+  }
+} {4.0 real}
+
+
+finish_test