gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/orderby2.test (about)

     1  # 2012 Sept 27
     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 that the optimizations that disable
    13  # ORDER BY clauses when the natural order of a query is correct.
    14  #
    15  
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  set ::testprefix orderby2
    20  
    21  # Generate test data for a join.  Verify that the join gets the
    22  # correct answer.
    23  #
    24  do_test 1.0 {
    25    db eval {
    26      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    27      INSERT INTO t1 VALUES(1,11), (2,22);
    28      CREATE TABLE t2(d, e, UNIQUE(d,e));
    29      INSERT INTO t2 VALUES(10, 'ten'), (11,'eleven'), (12,'twelve'),
    30                           (11, 'oneteen');
    31    }
    32  } {}
    33  
    34  do_test 1.1a {
    35    db eval {
    36      SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e;
    37    }
    38  } {eleven oneteen}
    39  do_test 1.1b {
    40    db eval {
    41      EXPLAIN QUERY PLAN
    42      SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e;
    43    }
    44  } {~/ORDER BY/}
    45  
    46  do_test 1.2a {
    47    db eval {
    48      SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e;
    49    }
    50  } {eleven oneteen}
    51  do_test 1.2b {
    52    db eval {
    53      EXPLAIN QUERY PLAN
    54      SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e;
    55    }
    56  } {~/ORDER BY/}
    57  
    58  do_test 1.3a {
    59    db eval {
    60      SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;
    61    }
    62  } {ten 11 eleven 11 oneteen 11 twelve 11}
    63  do_test 1.3b {
    64    db eval {
    65      EXPLAIN QUERY PLAN
    66      SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e;
    67    }
    68  } {~/ORDER BY/}
    69  
    70  # The following tests derived from TH3 test module cov1/where34.test
    71  #
    72  do_test 2.0 {
    73    db eval {
    74      CREATE TABLE t31(a,b); CREATE INDEX t31ab ON t31(a,b);
    75      CREATE TABLE t32(c,d); CREATE INDEX t32cd ON t32(c,d);
    76      CREATE TABLE t33(e,f); CREATE INDEX t33ef ON t33(e,f);
    77      CREATE TABLE t34(g,h); CREATE INDEX t34gh ON t34(g,h);
    78      
    79      INSERT INTO t31 VALUES(1,4), (2,3), (1,3);
    80      INSERT INTO t32 VALUES(4,5), (3,6), (3,7), (4,8);
    81      INSERT INTO t33 VALUES(5,9), (7,10), (6,11), (8,12), (8,13), (7,14);
    82      INSERT INTO t34 VALUES(11,20), (10,21), (12,22), (9,23), (13,24),
    83                            (14,25), (12,26);
    84      SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
    85       WHERE c=b AND e=d AND g=f
    86       ORDER BY a ASC, c ASC, e DESC, g ASC;
    87    }
    88  } {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}
    89  do_test 2.1 {
    90    db eval {
    91      SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
    92       WHERE c=b AND e=d AND g=f
    93       ORDER BY +a ASC, +c ASC, +e DESC, +g ASC;
    94    }
    95  } {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7,14 2,3,6,11}
    96  do_test 2.2 {
    97    db eval {
    98      SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
    99       WHERE c=b AND e=d AND g=f
   100       ORDER BY a ASC, c ASC, e ASC, g ASC;
   101    }
   102  } {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7,10 2,3,7,14}
   103  do_test 2.3 {
   104    optimization_control db cover-idx-scan off
   105    db cache flush
   106    db eval {
   107      SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34
   108       WHERE c=b AND e=d AND g=f
   109       ORDER BY a ASC, c ASC, e ASC, g ASC;
   110    }
   111  } {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7,10 2,3,7,14}  
   112  optimization_control db all on
   113  db cache flush
   114  
   115  
   116  
   117  finish_test