[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/misc7.test

+# 2006 September 4
+#
+# 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.
+#
+# $Id: misc7.test,v 1.29 2009/07/16 18:21:18 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+if {[clang_sanitize_address]==0} {
+  do_test misc7-1-misuse {
+    c_misuse_test
+  } {}
+}
+
+do_test misc7-2 {
+  c_realloc_test
+} {}
+
+do_test misc7-3 {
+  c_collation_test
+} {}
+
+# Try to open a directory:
+#
+do_test misc7-4 {
+  delete_file mydir
+  file mkdir mydir
+  set rc [catch {
+    sqlite3 db2 ./mydir
+  } msg]
+  list $rc $msg
+} {1 {unable to open database file}}
+
+# Try to open a file with a directory where its journal file should be.
+#
+do_test misc7-5 {
+  delete_file mydir
+  file mkdir mydir-journal
+  sqlite3 db2 ./mydir
+  catchsql {
+    CREATE TABLE abc(a, b, c);
+  } db2
+} {1 {unable to open database file}}
+db2 close
+
+#--------------------------------------------------------------------
+# The following tests, misc7-6.* test the libraries behaviour when
+# it cannot open a file. To force this condition, we use up all the
+# file-descriptors before running sqlite. This probably only works
+# on unix.
+#
+
+proc use_up_files {} {
+  set ret [list]
+  catch {
+    while 1 { lappend ret [open test.db] }
+  }
+  return $ret
+}
+
+proc do_fileopen_test {prefix sql} {
+  set fd_list [use_up_files]
+  set ::go 1
+  set ::n 1
+  set ::sql $sql
+  while {$::go} {
+    catch {db close}
+    do_test ${prefix}.${::n} {
+      set rc [catch {
+        sqlite db test.db
+        db eval $::sql
+      } msg]
+      if {$rc == 0} {set ::go 0}
+  
+      expr {$rc == 0 || ($rc == 1 && [string first unable $msg]==0)}
+    } 1
+  
+    close [lindex $fd_list 0]
+    set fd_list [lrange $fd_list 1 end]
+    incr ::n
+  }
+  foreach fd $fd_list {
+    close $fd
+  }
+  db close
+}
+
+execsql { CREATE TABLE abc(a PRIMARY KEY, b, c); }
+db close
+
+if {$tcl_platform(platform)!="windows"} {
+  do_fileopen_test misc7-6.1 {
+    BEGIN;
+    INSERT INTO abc VALUES(1, 2, 3);
+    INSERT INTO abc VALUES(2, 3, 4);
+    INSERT INTO abc SELECT a+2, b, c FROM abc;
+    COMMIT;
+  }
+  
+  do_fileopen_test misc7-6.2 {
+    PRAGMA temp.cache_size = 1000;
+  }
+}
+
+#
+# End of tests for out-of-file-descriptors condition.
+#--------------------------------------------------------------------
+
+sqlite3 db test.db
+execsql {
+  DELETE FROM abc;
+  INSERT INTO abc VALUES(1, 2, 3);
+  INSERT INTO abc VALUES(2, 3, 4);
+  INSERT INTO abc SELECT a+2, b, c FROM abc;
+}
+  
+
+#--------------------------------------------------------------------
+# Test that the sqlite3_busy_timeout call seems to delay approximately
+# the right amount of time.
+#
+do_test misc7-7.0 {
+  sqlite3 db2 test.db
+  sqlite3_busy_timeout [sqlite3_connection_pointer db] 2000
+  execsql {
+    BEGIN EXCLUSIVE;
+  } db2
+
+  # Now db2 has an exclusive lock on the database file, and db has
+  # a busy-timeout of 2000 milliseconds. So check that trying to
+  # access the database using connection db delays for at least 1500 ms.
+  #
+  set tm [time {
+    set result [catchsql {
+        SELECT * FROM sqlite_master;
+      } db]
+  }]
+  set delay [lindex $tm 0]  ;# In microseconds
+  lappend result [expr {$delay>1500000 && $delay<4000000}]
+} {1 {database is locked} 1}
+db2 close
+
+#--------------------------------------------------------------------
+# Test that nothing goes horribly wrong when attaching a database
+# after the omit_readlock pragma has been exercised.
+#
+# Note:  The PRAGMA omit_readlock was an early hack to disable the
+# fcntl() calls for read-only databases so that read-only databases could
+# be read on broken NFS systems.  That pragma has now been removed.
+# (Use the unix-none VFS as a replacement, if needed.)  But these tests
+# do not really depend on omit_readlock, so we left them in place.
+#
+do_test misc7-7.1 {
+  forcedelete test2.db
+  forcedelete test2.db-journal
+  execsql {
+    PRAGMA omit_readlock = 1;
+    ATTACH 'test2.db' AS aux;
+    CREATE TABLE aux.hello(world);
+    SELECT name FROM aux.sqlite_master;
+  }
+} {hello}
+do_test misc7-7.2 {
+  execsql {
+    DETACH aux;
+  }
+} {}
+do_test misc7-7.3 {
+  db close
+  sqlite3 db test.db -readonly 1
+  execsql {
+    PRAGMA omit_readlock = 1;
+    ATTACH 'test2.db' AS aux;
+    SELECT name FROM aux.sqlite_master;
+    SELECT name FROM aux.sqlite_master;
+  }
+} {hello hello}
+do_test misc7-7.3 {
+  db close
+  sqlite3 db test.db
+  set ::DB [sqlite3_connection_pointer db]
+  list
+} {}
+
+# Test the UTF-16 version of the "out of memory" message (used when
+# malloc fails during sqlite3_open() ).
+#
+ifcapable utf16 {
+  do_test misc7-8 {
+    encoding convertfrom unicode [sqlite3_errmsg16 0x00000000]
+  } {out of memory}
+}
+
+do_test misc7-9 {
+  execsql {
+    SELECT * 
+    FROM (SELECT name+1 AS one FROM sqlite_master LIMIT 1 OFFSET 1) 
+    WHERE one LIKE 'hello%';
+  }
+} {}
+
+#--------------------------------------------------------------------
+# Improve coverage for vtab code.
+#
+ifcapable vtab {
+  # Run some debug code to improve reported coverage
+  #
+
+  # set sqlite_where_trace 1
+  do_test misc7-10 {
+    register_echo_module [sqlite3_connection_pointer db]
+    execsql {
+      CREATE VIRTUAL TABLE t1 USING echo(abc);
+      SELECT a FROM t1 WHERE a = 1 ORDER BY b;
+    }
+  } {1}
+  set sqlite_where_trace 0
+
+  # Specify an ORDER BY clause that cannot be indexed.
+  do_test misc7-11 {
+    execsql {
+      SELECT t1.a, t2.a FROM t1, t1 AS t2 ORDER BY 2 LIMIT 1;
+    }
+  } {1 1}
+
+  # The whole point of this is to test an error code other than
+  # SQLITE_NOMEM from the vtab xBestIndex callback.
+  #
+  do_ioerr_test misc7-12 -tclprep {
+    sqlite3 db2 test.db
+    register_echo_module [sqlite3_connection_pointer db2]
+    db2 eval {
+      CREATE TABLE abc(a PRIMARY KEY, b, c);
+      INSERT INTO abc VALUES(1, 2, 3);
+      CREATE VIRTUAL TABLE t1 USING echo(abc);
+    }
+    db2 close
+  } -tclbody {
+    register_echo_module [sqlite3_connection_pointer db]
+    execsql {SELECT * FROM t1 WHERE a = 1;}
+  } 
+
+  # The case where the virtual table module returns a very large number
+  # as the cost of a scan (greater than SQLITE_BIG_DOUBLE in the code).
+  #
+  do_test misc7-13 {
+    sqlite3 db test.db
+    register_echo_module [sqlite3_connection_pointer db]
+    set ::echo_module_cost 2.0e+99
+    execsql {SELECT * FROM t1 WHERE a = 1;}
+  } {1 2 3}
+  unset ::echo_module_cost
+}
+
+db close
+forcedelete test.db
+forcedelete test.db-journal
+sqlite3 db test.db
+
+ifcapable explain {
+  do_execsql_test misc7-14.1 {
+    CREATE TABLE abc(a PRIMARY KEY, b, c);
+    EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE rowid = 1;
+  } {
+    0 0 0 {SEARCH TABLE abc AS t2 USING INTEGER PRIMARY KEY (rowid=?)}
+  }
+  do_execsql_test misc7-14.2 {
+    EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 WHERE a = 1;
+  } {0 0 0 
+     {SEARCH TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1 (a=?)}
+  }
+  do_execsql_test misc7-14.3 {
+    EXPLAIN QUERY PLAN SELECT * FROM abc AS t2 ORDER BY a;
+  } {0 0 0 
+     {SCAN TABLE abc AS t2 USING INDEX sqlite_autoindex_abc_1}
+  }
+}
+
+db close
+forcedelete test.db
+forcedelete test.db-journal
+sqlite3 db test.db
+
+#--------------------------------------------------------------------
+# This is all to force the pager_remove_from_stmt_list() function
+# (inside pager.c) to remove a pager from the middle of the
+# statement-list.
+#
+do_test misc7-15.1 {
+  execsql {
+    PRAGMA cache_size = 10;
+    BEGIN;
+    CREATE TABLE abc(a PRIMARY KEY, b, c);
+    INSERT INTO abc 
+    VALUES(randstr(100,100), randstr(100,100), randstr(100,100));
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+    COMMIT;
+  }
+  expr {[file size test.db]>10240}
+} {1}
+do_test misc7-15.2 {
+  execsql {
+    DELETE FROM abc WHERE rowid > 12;
+    INSERT INTO abc SELECT 
+            randstr(100,100), randstr(100,100), randstr(100,100) FROM abc;
+  }
+} {}
+
+db close
+forcedelete test.db
+forcedelete test.db-journal
+sqlite3 db test.db
+
+do_ioerr_test misc7-16 -sqlprep {
+   PRAGMA cache_size = 10;
+   PRAGMA default_cache_size = 10;
+   CREATE TABLE t3(a, b, UNIQUE(a, b));
+   INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
+   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
+   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
+   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
+   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
+   INSERT INTO t3 SELECT randstr(100, 100), randstr(100, 100) FROM t3;
+   UPDATE t3 
+   SET b = 'hello world'
+   WHERE rowid >= (SELECT max(rowid)-1 FROM t3);
+} -tclbody {
+  set rc [catch {db eval {
+    BEGIN;
+      PRAGMA cache_size = 10;
+      INSERT INTO t3 VALUES( randstr(100, 100), randstr(100, 100) );
+      UPDATE t3 SET a = b;
+    COMMIT;
+  }} msg]
+
+  if {!$rc || ($rc && [string first "UNIQUE" $msg]==0)} {
+    set msg
+  } else {
+    error $msg
+  }
+}
+
+sqlite3 db test.db
+
+do_test misc7-16.X {
+  execsql {
+    SELECT count(*) FROM t3;
+  }
+} {32}
+
+#----------------------------------------------------------------------
+# Test the situation where a hot-journal is discovered but write-access
+# to it is denied. This should return SQLITE_BUSY.
+#
+# These tests do not work on windows due to restrictions in the
+# windows file system.
+#
+if {$tcl_platform(platform)!="windows"} {
+
+  # Some network filesystems (ex: AFP) do not support setting read-only
+  # permissions.  Only run these tests if full unix permission setting
+  # capabilities are supported.
+  #
+  file attributes test.db -permissions rw-r--r--
+  if {[file attributes test.db -permissions]==0644} {
+
+    do_test misc7-17.1 {
+      execsql {
+        BEGIN;
+        DELETE FROM t3 WHERE (oid%3)==0;
+      }
+      forcecopy test.db bak.db
+      forcecopy test.db-journal bak.db-journal
+      execsql {
+        COMMIT;
+      }
+    
+      db close
+      forcecopy bak.db test.db
+      forcecopy bak.db-journal test.db-journal
+      sqlite3 db test.db
+    
+      catch {file attributes test.db-journal -permissions r--------}
+      catch {file attributes test.db-journal -readonly 1}
+      catchsql {
+        SELECT count(*) FROM t3;
+      }
+    } {1 {unable to open database file}}
+    do_test misc7-17.2 {
+      # Note that the -readonly flag must be cleared before the -permissions
+      # are set. Otherwise, when using tcl 8.5 on mac, the fact that the 
+      # -readonly flag is set causes the attempt to set the permissions
+      # to fail.
+      catch {file attributes test.db-journal -readonly 0}
+      catch {file attributes test.db-journal -permissions rw-------}
+      catchsql {
+        SELECT count(*) FROM t3;
+      }
+    } {0 32}
+    
+    # sqlite3_test_control_pending_page [expr ($::sqlite_pending_byte / 1024) + 1]
+    set ::pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
+    sqlite3_test_control_pending_byte $::sqlite_pending_byte 
+    do_test misc7-17.3 {
+      db eval {
+        pragma writable_schema = true;
+        UPDATE sqlite_master 
+          SET rootpage = $pending_byte_page
+          WHERE type = 'table' AND name = 't3';
+      }
+      execsql {
+        SELECT rootpage FROM sqlite_master WHERE type = 'table' AND name = 't3';
+      }
+    } $::pending_byte_page
+    
+    do_test misc7-17.4 {
+      db close
+      sqlite3 db test.db
+      catchsql {
+        SELECT count(*) FROM t3;
+      } 
+    } {1 {database disk image is malformed}}
+  }
+}
+
+# Ticket #2470
+#
+do_test misc7-18.1 {
+  execsql {
+    CREATE TABLE table_1 (col_10);
+    CREATE TABLE table_2 (
+      col_1, col_2, col_3, col_4, col_5,
+      col_6, col_7, col_8, col_9, col_10
+    );
+    SELECT a.col_10
+    FROM
+      (SELECT table_1.col_10 AS col_10 FROM table_1) a,
+      (SELECT table_1.col_10, table_2.col_9 AS qcol_9
+         FROM table_1, table_2
+        GROUP BY table_1.col_10, qcol_9);
+  }
+} {}
+
+# Testing boundary conditions on sqlite3_status()
+#
+do_test misc7-19.1 {
+  sqlite3_status -1 0
+} {21 0 0}
+do_test misc7-19.2 {
+  sqlite3_status 1000 0
+} {21 0 0}
+
+
+# sqlite3_global_recover() is a no-op.  But we might as well test it
+# if only to get the test coverage.
+#
+do_test misc7-20.1 {
+  sqlite3_global_recover
+} {SQLITE_OK}
+
+# Try to open a really long file name.
+#
+do_test misc7-21.1 {
+  set zFile [file join [get_pwd] "[string repeat abcde 104].db"]
+  set rc [catch {sqlite3 db2 $zFile} msg]
+  list $rc $msg
+} {1 {unable to open database file}}
+
+# Try to do hot-journal rollback with a read-only connection. The 
+# error code should be SQLITE_READONLY_ROLLBACK.
+#
+do_test misc7-22.1 {
+  db close
+  forcedelete test.db copy.db-journal
+  sqlite3 db test.db
+  execsql {
+    CREATE TABLE t1(a, b);
+    INSERT INTO t1 VALUES(1, 2);
+    INSERT INTO t1 VALUES(3, 4);
+  }
+  db close
+  sqlite3 db test.db -readonly 1
+  catchsql {
+    INSERT INTO t1 VALUES(5, 6);
+  }
+} {1 {attempt to write a readonly database}}
+do_test misc7-22.2 { execsql { SELECT * FROM t1 } } {1 2 3 4}
+do_test misc7-22.3 { 
+  set fd [open test.db-journal w]
+  puts $fd [string repeat abc 1000]
+  close $fd
+  catchsql { SELECT * FROM t1 }
+} {1 {attempt to write a readonly database}}
+do_test misc7-22.4 { 
+  sqlite3_extended_errcode db
+} SQLITE_READONLY_ROLLBACK
+
+db close
+forcedelete test.db
+
+finish_test