SQLite

#-------------------------------------------------------------------------
# Test that it is not possible to use ALTER TABLE on any system table.
#
set system_table_list {1 sqlite_master}
catchsql ANALYZE
ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
ifcapable stat2   { lappend system_table_list 3 sqlite_stat2 }
ifcapable stat3   { lappend system_table_list 4 sqlite_stat3 }

foreach {tn tbl} $system_table_list {
  do_test alter-15.$tn.1 {
    catchsql "ALTER TABLE $tbl RENAME TO xyz"
  } [list 1 "table $tbl may not be altered"]

  do_test alter-15.$tn.2 {

# implements tests for range and LIKE constraints that use bound variables
# instead of literal constant arguments.
#

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

ifcapable !stat3 {
  finish_test
  return
}

#----------------------------------------------------------------------
# Test Organization:
#

} {}

do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
do_eqp_test analyze3-1.1.3 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}

do_test analyze3-1.1.4 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.1.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.1.6 {
  set l [expr int(200)]
  set u [expr int(300)]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.1.7 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
} {2000 0 499500}
do_test analyze3-1.1.8 {
  set l [string range "0" 0 end]
  set u [string range "1100" 0 end]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {2000 0 499500}
do_test analyze3-1.1.9 {
  set l [expr int(0)]
  set u [expr int(1100)]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {2000 0 499500}


# The following tests are similar to the block above. The difference is
# that the indexed column has TEXT affinity in this case. In the tests
# above the affinity is INTEGER.
#
do_test analyze3-1.2.1 {
  execsql {
    BEGIN;
      CREATE TABLE t2(x TEXT, y);
      INSERT INTO t2 SELECT * FROM t1;
      CREATE INDEX i2 ON t2(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.2.2 {
  SELECT sum(y) FROM t2 WHERE x>1 AND x<2
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
do_eqp_test analyze3-1.2.3 {
  SELECT sum(y) FROM t2 WHERE x>0 AND x<99
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~982 rows)}}
do_test analyze3-1.2.4 {
  sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
} {161 0 4760}
do_test analyze3-1.2.5 {
  set l [string range "12" 0 end]
  set u [string range "20" 0 end]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {161 0 text text 4760}
do_test analyze3-1.2.6 {
  set l [expr int(12)]
  set u [expr int(20)]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {161 0 integer integer 4760}
do_test analyze3-1.2.7 {
  sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
} {1981 0 490555}
do_test analyze3-1.2.8 {
  set l [string range "0" 0 end]
  set u [string range "99" 0 end]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {1981 0 text text 490555}
do_test analyze3-1.2.9 {
  set l [expr int(0)]
  set u [expr int(99)]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {1981 0 integer integer 490555}

# Same tests a third time. This time, column x has INTEGER affinity and
# is not the leftmost column of the table. This triggered a bug causing
# SQLite to use sub-optimal query plans in 3.6.18 and earlier.
#
do_test analyze3-1.3.1 {
  execsql {
    BEGIN;
      CREATE TABLE t3(y TEXT, x INTEGER);
      INSERT INTO t3 SELECT y, x FROM t1;
      CREATE INDEX i3 ON t3(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.3.2 {
  SELECT sum(y) FROM t3 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
do_eqp_test analyze3-1.3.3 {
  SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}

do_test analyze3-1.3.4 {
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.3.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.3.6 {
  set l [expr int(200)]
  set u [expr int(300)]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.3.7 {
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
} {2000 0 499500}
do_test analyze3-1.3.8 {
  set l [string range "0" 0 end]
  set u [string range "1100" 0 end]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {2000 0 499500}
do_test analyze3-1.3.9 {
  set l [expr int(0)]
  set u [expr int(1100)]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {2000 0 499500}

#-------------------------------------------------------------------------
# Test that the values of bound SQL variables may be used for the LIKE
# optimization.
#
drop_all_tables
do_test analyze3-2.1 {

# in this file a corner-case query planner optimization involving the
# join order of two tables of different sizes.
#

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

ifcapable !stat3 {
  finish_test
  return
}

set testprefix analyze6

proc eqp {sql {db db}} {

# This file implements tests to verify that ticket [cbd054fa6b] has been
# fixed.  
#

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

ifcapable !stat3 {
  finish_test
  return
}

do_test tkt-cbd05-1.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL);

  db eval {
    ANALYZE;
  }
} {}
do_test tkt-cbd05-1.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

do_test tkt-cbd05-2.1 {
  db eval {

  db eval {
    ANALYZE;
  }
} {}
do_test tkt-cbd05-2.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

finish_test