# 2001 September 15

#

# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

#

# 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.  The

# focus of this file is testing the SELECT statement.

#

# $Id: select2.test,v 1.27 2008/07/12 14:52:20 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Create a table with some data

#

execsql {CREATE TABLE tbl1(f1 int, f2 int)}

execsql {BEGIN}

for {set i 0} {$i<=30} {incr i} {

  execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"

}

execsql {COMMIT}

# Do a second query inside a first.

#

do_test select2-1.1 {

  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}

  set r {}

  catch {unset data}

  db eval $sql data {

    set f1 $data(f1)

    lappend r $f1:

    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"

    db eval $sql2 d2 {

      lappend r $d2(f2)

    }

  }

  set r

} {0: 0 7 8 9 1: 0 1 8 9 2: 0 1 2 9 3: 0 1 2 3 4: 2 3 4 5: 3 4 5 6: 4 5 6 7: 5 6 7 8: 6 7 8}

do_test select2-1.2 {

  set sql {SELECT DISTINCT f1 FROM tbl1 WHERE f1>3 AND f1<5}

  set r {}

  db eval $sql data {

    set f1 $data(f1)

    lappend r $f1:

    set sql2 "SELECT f2 FROM tbl1 WHERE f1=$f1 ORDER BY f2"

    db eval $sql2 d2 {

      lappend r $d2(f2)

    }

  }

  set r

} {4: 2 3 4}

unset data

# Create a largish table. Do this twice, once using the TCL cache and once

# without.  Compare the performance to make sure things go faster with the

# cache turned on.

#

ifcapable tclvar {

  do_test select2-2.0.1 {

    set t1 [time {

      execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}

      for {set i 1} {$i<=30000} {incr i} {

        set i2 [expr {$i*2}]

        set i3 [expr {$i*3}]

        db eval {INSERT INTO tbl2 VALUES($i,$i2,$i3)}

      }

      execsql {COMMIT}

    }]

    list

  } {}

  puts "time with cache: $::t1"

}

catch {execsql {DROP TABLE tbl2}}

do_test select2-2.0.2 {

  set t2 [time {

    execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}

    for {set i 1} {$i<=30000} {incr i} {

      set i2 [expr {$i*2}]

      set i3 [expr {$i*3}]

      execsql "INSERT INTO tbl2 VALUES($i,$i2,$i3)"

    }

    execsql {COMMIT}

  }]

  list

} {}

puts "time without cache: $t2"

#Symbian OS: This is a tclsqlite3.exe test. We do not use the TCL cache.

if {$::tcl_platform(platform)!="symbian"} {

  ifcapable tclvar {

    do_test select2-2.0.3 {

      expr {[lindex $t1 0]<[lindex $t2 0]}

    } 1

  }

}

do_test select2-2.1 {

  execsql {SELECT count(*) FROM tbl2}

} {30000}

do_test select2-2.2 {

  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}

} {29500}

do_test select2-3.1 {

  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}

} {500}

do_test select2-3.2a {

  execsql {CREATE INDEX idx1 ON tbl2(f2)}

} {}

do_test select2-3.2b {

  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}

} {500}

do_test select2-3.2c {

  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}

} {500}

do_test select2-3.2d {

  set sqlite_search_count 0

  execsql {SELECT * FROM tbl2 WHERE 1000=f2}

  set sqlite_search_count

} {3}

do_test select2-3.2e {

  set sqlite_search_count 0

  execsql {SELECT * FROM tbl2 WHERE f2=1000}

  set sqlite_search_count

} {3}

# Make sure queries run faster with an index than without

#

do_test select2-3.3 {

  execsql {DROP INDEX idx1}

  set sqlite_search_count 0

  execsql {SELECT f1 FROM tbl2 WHERE f2==2000}

  set sqlite_search_count

} {29999}

# Make sure we can optimize functions in the WHERE clause that

# use fields from two or more different table.  (Bug #6)

#

do_test select2-4.1 {

  execsql {

    CREATE TABLE aa(a);

    CREATE TABLE bb(b);

    INSERT INTO aa VALUES(1);

    INSERT INTO aa VALUES(3);

    INSERT INTO bb VALUES(2);

    INSERT INTO bb VALUES(4);

    SELECT * FROM aa, bb WHERE max(a,b)>2;

  }

} {1 4 3 2 3 4}

do_test select2-4.2 {

  execsql {

    INSERT INTO bb VALUES(0);

    SELECT * FROM aa, bb WHERE b;

  }

} {1 2 1 4 3 2 3 4}

do_test select2-4.3 {

  execsql {

    SELECT * FROM aa, bb WHERE NOT b;

  }

} {1 0 3 0}

do_test select2-4.4 {

  execsql {

    SELECT * FROM aa, bb WHERE min(a,b);

  }

} {1 2 1 4 3 2 3 4}

do_test select2-4.5 {

  execsql {

    SELECT * FROM aa, bb WHERE NOT min(a,b);

  }

} {1 0 3 0}

do_test select2-4.6 {

  execsql {

    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 1 END;

  }

} {1 2 3 4}

do_test select2-4.7 {

  execsql {

    SELECT * FROM aa, bb WHERE CASE WHEN a=b-1 THEN 0 ELSE 1 END;

  }

} {1 4 1 0 3 2 3 0}

finish_test