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

     1  # 2014-04-21
     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  #
    12  # Test that ticket [b75a9ca6b0] has been fixed.
    13  #
    14  # Ticket [b75a9ca6b0] concerns queries that have both a GROUP BY
    15  # and an ORDER BY.  This code verifies that SQLite is able to
    16  # optimize out the ORDER BY in some circumstances, but retains the
    17  # ORDER BY when necessary.
    18  #
    19  
    20  set testdir [file dirname $argv0]
    21  source $testdir/tester.tcl
    22  set testprefix tkt-b75a9ca6b0
    23  
    24  do_execsql_test 1 {
    25    CREATE TABLE t1 (x, y);
    26    INSERT INTO t1 VALUES (1, 3); 
    27    INSERT INTO t1 VALUES (2, 2);
    28    INSERT INTO t1 VALUES (3, 1);
    29  }
    30  
    31  do_execsql_test 1.1 {
    32    CREATE INDEX i1 ON t1(x, y);
    33  } 
    34  
    35  set idxscan {SCAN t1 USING COVERING INDEX i1}
    36  set tblscan {SCAN t1}
    37  set grpsort {USE TEMP B-TREE FOR GROUP BY}
    38  set sort    {USE TEMP B-TREE FOR ORDER BY}
    39  
    40  foreach {tn q res eqp} [subst -nocommands {
    41    1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y"
    42    {1 3  2 2  3 1} {$idxscan}
    43  
    44    2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x"
    45    {1 3  2 2  3 1} {$idxscan*$sort}
    46  
    47    3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x"
    48    {3 1  2 2  1 3} {$idxscan*$sort}
    49    
    50    4 "SELECT * FROM t1 GROUP BY x ORDER BY x"
    51    {1 3  2 2  3 1} {$idxscan}
    52  
    53    5 "SELECT * FROM t1 GROUP BY y ORDER BY y"
    54    {3 1  2 2  1 3} {$tblscan*$grpsort}
    55  
    56    6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
    57    {1 3  2 2  3 1} {$tblscan*$grpsort*$sort}
    58  
    59    7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
    60    {1 3  2 2  3 1} {$idxscan*$sort}
    61  
    62    8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
    63    {3 1  2 2  1 3} {$idxscan}
    64  
    65    9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
    66    {1 3  2 2  3 1} {$idxscan}
    67  
    68    10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
    69    {1 3  2 2  3 1} {$idxscan*$sort}
    70  
    71  }] {
    72    do_execsql_test 1.$tn.1 $q $res
    73    do_eqp_test     1.$tn.2 $q $eqp
    74  }
    75  
    76  
    77  finish_test