github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/index2.test (about)

     1  # 2005-01-11
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  # This file implements regression tests for SQLite library.  The
    12  # focus of this file is testing the CREATE INDEX statement.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  
    18  # Create a table with a large number of columns
    19  #
    20  do_test index2-1.1 {
    21    set sql {CREATE TABLE t1(}
    22    for {set i 1} {$i<1000} {incr i} {
    23      append sql "c$i,"
    24    }
    25    append sql "c1000);"
    26    execsql $sql
    27  } {}
    28  do_test index2-1.2 {
    29    set sql {INSERT INTO t1 VALUES(}
    30    for {set i 1} {$i<1000} {incr i} {
    31      append sql $i,
    32    }
    33    append sql {1000);}
    34    execsql $sql
    35  } {}
    36  do_test index2-1.3 {
    37    execsql {SELECT c123 FROM t1}
    38  } 123
    39  do_test index2-1.4 {
    40    execsql BEGIN
    41    for {set j 1} {$j<=100} {incr j} {
    42      set sql {INSERT INTO t1 VALUES(}
    43      for {set i 1} {$i<1000} {incr i} {
    44        append sql [expr {$j*10000+$i}],
    45      }
    46      append sql "[expr {$j*10000+1000}]);"
    47      execsql $sql
    48    }
    49    execsql COMMIT
    50    execsql {SELECT count(*) FROM t1}
    51  } 101
    52  do_test index2-1.5 {
    53    execsql {SELECT round(sum(c1000)) FROM t1}
    54  } {50601000.0}
    55  
    56  # Create indices with many columns
    57  #
    58  do_test index2-2.1 {
    59    set sql "CREATE INDEX t1i1 ON t1("
    60    for {set i 1} {$i<1000} {incr i} {
    61      append sql c$i,
    62    }
    63    append sql c1000)
    64    execsql $sql
    65  } {}
    66  do_test index2-2.2 {
    67    ifcapable explain {
    68      execsql {EXPLAIN SELECT c9 FROM t1 ORDER BY c1, c2, c3, c4, c5}
    69    }
    70    execsql {SELECT c9 FROM t1 ORDER BY c1, c2, c3, c4, c5, c6 LIMIT 5}
    71  } {9 10009 20009 30009 40009}
    72  
    73  finish_test