misc1.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

  }
  catchsql "SELECT count(*) FROM manycol $::where"
} {0 9}
do_test misc1-10.2 {
  catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
} {0 9}
do_test misc1-10.3 {
  regsub "x0>=0" $::where "x0=0" ::where
  catchsql "DELETE FROM manycol $::where"
} {0 {}}
do_test misc1-10.4 {
  execsql {SELECT count(*) FROM manycol}
} {8}
do_test misc1-10.5 {
  catchsql "DELETE FROM manycol $::where AND rowid>0"
} {0 {}}
do_test misc1-10.6 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {101}
do_test misc1-10.7 {
  regsub "x0=0" $::where "x0=100" ::where
  catchsql "UPDATE manycol SET x1=x1+1 $::where"
} {0 {}}
do_test misc1-10.8 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {102}
do_test misc1-10.9 {
  catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
} {0 {}}
do_test misc1-10.10 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {103}

# Make sure the initialization works even if a database is opened while
# another process has the database locked.
#
# Update for v3: The BEGIN doesn't lock the database so the schema is read
# and the SELECT returns successfully.
do_test misc1-11.1 {
  execsql {BEGIN}
  execsql {UPDATE t1 SET a=0 WHERE 0}
  sqlite3 db2 test.db
  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
  lappend rc $msg
# v2 result: {1 {database is locked}}
} {0 3}
do_test misc1-11.2 {
  execsql {COMMIT}
  set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
  db2 close
  lappend rc $msg
} {0 3}

# Make sure string comparisons really do compare strings in format4+.
# Similar tests in the format3.test file show that for format3 and earlier
# all comparisions where numeric if either operand looked like a number.
#
do_test misc1-12.1 {
  execsql {SELECT '0'=='0.0'}
} {0}
do_test misc1-12.2 {
  execsql {SELECT '0'==0.0}
} {0}
do_test misc1-12.3 {
  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
} {0}
do_test misc1-12.4 {
  execsql {
    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
    INSERT INTO t6 VALUES('0','0.0');
    SELECT * FROM t6;
  }
} {0 0.0}
ifcapable conflict {
  do_test misc1-12.5 {
    execsql {
      INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
      SELECT * FROM t6;
    }
  } {0 0.0}
  do_test misc1-12.6 {
    execsql {
      INSERT OR IGNORE INTO t6 VALUES('y',0);
      SELECT * FROM t6;
    }
  } {0 0.0 y 0}
}
do_test misc1-12.7 {
  execsql {
    CREATE TABLE t7(x INTEGER, y TEXT, z);
    INSERT INTO t7 VALUES(0,0,1);
    INSERT INTO t7 VALUES(0.0,0,2);
    INSERT INTO t7 VALUES(0,0.0,3);
    INSERT INTO t7 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t7 ORDER BY z;
  }
} {0 0 0 0.0}
do_test misc1-12.8 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1;
  }
} {1 4 4}
do_test misc1-12.9 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
  }
} {1 2 2 3 4 2}

# This used to be an error.  But we changed the code so that arbitrary
# identifiers can be used as a collating sequence.  Collation is by text
# if the identifier contains "text", "blob", or "clob" and is numeric
# otherwise.
#
# Update: In v3, it is an error again.
#
#do_test misc1-12.10 {
#  catchsql {
#    SELECT * FROM t6 ORDER BY a COLLATE unknown;
#  }
#} {0 {0 0 y 0}}
do_test misc1-12.11 {
  execsql {
    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,0,1);
    INSERT INTO t8 VALUES(0.0,0,2);
    INSERT INTO t8 VALUES(0,0.0,3);
    INSERT INTO t8 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t8 ORDER BY z;
  }
} {0 0 0.0 0}
do_test misc1-12.12 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
  }
} {1 3 2 2 4 2}
do_test misc1-12.13 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
  }
} {1 4 4}

# There was a problem with realloc() in the OP_MemStore operation of
# the VDBE.  A buffer was being reallocated but some pointers into 
# the old copy of the buffer were not being moved over to the new copy.
# The following code tests for the problem.
#
ifcapable subquery {
  do_test misc1-13.1 {
     execsql {
       CREATE TABLE t9(x,y);
       INSERT INTO t9 VALUES('one',1);
       INSERT INTO t9 VALUES('two',2);
       INSERT INTO t9 VALUES('three',3);
       INSERT INTO t9 VALUES('four',4);
       INSERT INTO t9 VALUES('five',5);
       INSERT INTO t9 VALUES('six',6);
       INSERT INTO t9 VALUES('seven',7);
       INSERT INTO t9 VALUES('eight',8);
       INSERT INTO t9 VALUES('nine',9);
       INSERT INTO t9 VALUES('ten',10);
       INSERT INTO t9 VALUES('eleven',11);
       SELECT y FROM t9
       WHERE x=(SELECT x FROM t9 WHERE y=1)
          OR x=(SELECT x FROM t9 WHERE y=2)
          OR x=(SELECT x FROM t9 WHERE y=3)
          OR x=(SELECT x FROM t9 WHERE y=4)
          OR x=(SELECT x FROM t9 WHERE y=5)
          OR x=(SELECT x FROM t9 WHERE y=6)
          OR x=(SELECT x FROM t9 WHERE y=7)
          OR x=(SELECT x FROM t9 WHERE y=8)
          OR x=(SELECT x FROM t9 WHERE y=9)
          OR x=(SELECT x FROM t9 WHERE y=10)
          OR x=(SELECT x FROM t9 WHERE y=11)
          OR x=(SELECT x FROM t9 WHERE y=12)
          OR x=(SELECT x FROM t9 WHERE y=13)
          OR x=(SELECT x FROM t9 WHERE y=14)
       ;
     }
  } {1 2 3 4 5 6 7 8 9 10 11}
}

# Make sure a database connection still works after changing the
# working directory.
#
do_test misc1-14.1 {
  file mkdir tempdir
  cd tempdir
  execsql {BEGIN}
  file exists ./test.db-journal
} {0}
do_test misc1-14.2 {
  execsql {UPDATE t1 SET a=0 WHERE 0}
  file exists ../test.db-journal
} {1}
do_test misc1-14.3 {
  cd ..
  file delete tempdir
  execsql {COMMIT}
  file exists ./test.db-journal
} {0}

# A failed create table should not leave the table in the internal
# data structures.  Ticket #238.
#
do_test misc1-15.1.1 {
  catchsql {
    CREATE TABLE t10 AS SELECT c1;
  }
} {1 {no such column: c1}}
do_test misc1-15.1.2 {
  catchsql {
    CREATE TABLE t10 AS SELECT t9.c1;
  }
} {1 {no such column: t9.c1}}
do_test misc1-15.1.3 {
  catchsql {
    CREATE TABLE t10 AS SELECT main.t9.c1;
  }
} {1 {no such column: main.t9.c1}}
do_test misc1-15.2 {
  catchsql {
    CREATE TABLE t10 AS SELECT 1;
  }
  # The bug in ticket #238 causes the statement above to fail with
  # the error "table t10 alread exists"
} {0 {}}

# Test for memory leaks when a CREATE TABLE containing a primary key
# fails.  Ticket #249.
#
do_test misc1-16.1 {
  catchsql {SELECT name FROM sqlite_master LIMIT 1}
  catchsql {
    CREATE TABLE test(a integer, primary key(a));
  }
} {0 {}}
do_test misc1-16.2 {
  catchsql {
    CREATE TABLE test(a integer, primary key(a));
  }
} {1 {table test already exists}}
do_test misc1-16.3 {
  catchsql {
    CREATE TABLE test2(a text primary key, b text, primary key(a,b));
  }
} {1 {table "test2" has more than one primary key}}
do_test misc1-16.4 {
  execsql {
    INSERT INTO test VALUES(1);
    SELECT rowid, a FROM test;
  }
} {1 1}
do_test misc1-16.5 {
  execsql {
    INSERT INTO test VALUES(5);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5}
do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}

ifcapable trigger&&tempdb {
# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
      INSERT INTO RealTable(TestString) 
         SELECT new.TestString FROM TempTable LIMIT 1;
    END;
    INSERT INTO TempTable(TestString) VALUES ('1');
    INSERT INTO TempTable(TestString) VALUES ('2');
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}
}

do_test misc1-18.1 {
  set n [sqlite3_sleep 100]
  expr {$n>=100}
} {1}

finish_test