SQLite

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.7 2004/02/09 14:35:28 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#

  }
} {64}
do_test misc3-4.3 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
  }
} {64}

# Ticket #601:  Putting a left join inside "SELECT * FROM (<join-here>)"
# gives different results that if the outer "SELECT * FROM ..." is omitted.
#
do_test misc4-5.1 {
  execsql {
    CREATE TABLE x1 (b, c);
    INSERT INTO x1 VALUES('dog',3);
    INSERT INTO x1 VALUES('cat',1);
    INSERT INTO x1 VALUES('dog',4);
    CREATE TABLE x2 (c, e);
    INSERT INTO x2 VALUES(1,'one');
    INSERT INTO x2 VALUES(2,'two');
    INSERT INTO x2 VALUES(3,'three');
    INSERT INTO x2 VALUES(4,'four');
    SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
       (SELECT b, max(c) AS c FROM x1 GROUP BY b)
       USING(c);
  }
} {1 one cat 2 two {} 3 three {} 4 four dog}
do_test misc4-5.2 {
  execsql {
    SELECT * FROM (
      SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
         (SELECT b, max(c) AS c FROM x1 GROUP BY b)
         USING(c)
    );
  }
} {1 one cat 2 two {} 3 three {} 4 four dog}


finish_test