gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt-ba7cbfaedc.test (about)

     1  # 2014-10-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  #
    12  # Test that ticket [ba7cbfaedc] has been fixed.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix tkt-ba7cbfaedc
    18  
    19  do_execsql_test 1 {
    20    CREATE TABLE t1 (x, y);
    21    INSERT INTO t1 VALUES (3, 'a');
    22    INSERT INTO t1 VALUES (1, 'a'); 
    23    INSERT INTO t1 VALUES (2, 'b');
    24    INSERT INTO t1 VALUES (2, 'a');
    25    INSERT INTO t1 VALUES (3, 'b');
    26    INSERT INTO t1 VALUES (1, 'b'); 
    27  }
    28  
    29  do_execsql_test 1.1 {
    30    CREATE INDEX i1 ON t1(x, y);
    31  }
    32  
    33  foreach {n idx} {
    34    1 { CREATE INDEX i1 ON t1(x, y) }
    35    2 { CREATE INDEX i1 ON t1(x DESC, y) }
    36    3 { CREATE INDEX i1 ON t1(x, y DESC) }
    37    4 { CREATE INDEX i1 ON t1(x DESC, y DESC) }
    38  } {
    39    catchsql { DROP INDEX i1 }
    40    execsql $idx
    41    foreach {tn q res} {
    42      1 "GROUP BY x, y ORDER BY x, y"            {1 a 1 b   2 a 2 b   3 a 3 b}
    43      2 "GROUP BY x, y ORDER BY x DESC, y"       {3 a 3 b   2 a 2 b   1 a 1 b}
    44      3 "GROUP BY x, y ORDER BY x, y DESC"       {1 b 1 a   2 b 2 a   3 b 3 a}
    45      4 "GROUP BY x, y ORDER BY x DESC, y DESC"  {3 b 3 a   2 b 2 a   1 b 1 a}
    46    } {
    47      do_execsql_test 1.$n.$tn "SELECT * FROM t1 $q" $res
    48    }
    49  }
    50  
    51  do_execsql_test 2.0 {
    52    drop table if exists t1;
    53    create table t1(id int);
    54    insert into t1(id) values(1),(2),(3),(4),(5);
    55    create index t1_idx_id on t1(id asc);
    56    select * from t1 group by id order by id;
    57    select * from t1 group by id order by id asc;
    58    select * from t1 group by id order by id desc;
    59  } {
    60    1 2 3 4 5   1 2 3 4 5   5 4 3 2 1
    61  }
    62  
    63  finish_test