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

     1  # 2014-04-25
     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 ORDER BY optimizations on joins
    13  # that involve virtual tables.
    14  #
    15  
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  set ::testprefix orderby7
    20  
    21  ifcapable !fts3 {
    22    finish_test
    23    return
    24  }
    25  
    26  do_execsql_test 1.0 {
    27    CREATE VIRTUAL TABLE fts USING fts3(content TEXT);
    28    INSERT INTO fts(rowid,content)
    29       VALUES(1,'this is a test of the fts3 virtual'),
    30             (2,'table used as part of a join together'),
    31             (3,'with the DISTINCT keyword.  There was'),
    32             (4,'a bug at one time (2013-06 through 2014-04)'),
    33             (5,'that prevented this from working correctly.'),
    34             (11,'a row that occurs twice'),
    35             (12,'a row that occurs twice');
    36   
    37    CREATE TABLE t1(x TEXT PRIMARY KEY, y);
    38    INSERT OR IGNORE INTO t1 SELECT content, rowid+100 FROM fts;
    39  } {}
    40  do_execsql_test 1.1 {
    41    SELECT DISTINCT fts.rowid, t1.y
    42      FROM fts, t1
    43     WHERE fts MATCH 'that twice'
    44       AND content=x
    45     ORDER BY y;
    46  } {11 111 12 111}
    47  do_execsql_test 1.2 {
    48    SELECT DISTINCT fts.rowid, t1.x
    49      FROM fts, t1
    50     WHERE fts MATCH 'that twice'
    51       AND content=x
    52     ORDER BY 1;
    53  } {11 {a row that occurs twice} 12 {a row that occurs twice}}
    54  do_execsql_test 1.3 {
    55    SELECT DISTINCT t1.x
    56      FROM fts, t1
    57     WHERE fts MATCH 'that twice'
    58       AND content=x
    59     ORDER BY 1;
    60  } {{a row that occurs twice}}
    61  do_execsql_test 1.4 {
    62    SELECT t1.x
    63      FROM fts, t1
    64     WHERE fts MATCH 'that twice'
    65       AND content=x
    66     ORDER BY 1;
    67  } {{a row that occurs twice} {a row that occurs twice}}
    68  do_execsql_test 1.5 {
    69    SELECT DISTINCT t1.x
    70      FROM fts, t1
    71     WHERE fts MATCH 'that twice'
    72       AND content=x;
    73  } {{a row that occurs twice}}
    74  do_execsql_test 1.6 {
    75    SELECT t1.x
    76      FROM fts, t1
    77     WHERE fts MATCH 'that twice'
    78       AND content=x;
    79  } {{a row that occurs twice} {a row that occurs twice}}
    80  
    81  do_execsql_test 2.1 {
    82    SELECT DISTINCT t1.x
    83      FROM fts, t1
    84     WHERE fts.rowid=11
    85       AND content=x
    86     ORDER BY fts.rowid;
    87  } {{a row that occurs twice}}
    88  do_execsql_test 2.2 {
    89    SELECT DISTINCT t1.*
    90      FROM fts, t1
    91     WHERE fts.rowid=11
    92       AND content=x
    93     ORDER BY fts.rowid;
    94  } {{a row that occurs twice} 111}
    95  do_execsql_test 2.3 {
    96    SELECT DISTINCT t1.*
    97      FROM fts, t1
    98     WHERE fts.rowid=11
    99       AND content=x
   100     ORDER BY t1.y
   101  } {{a row that occurs twice} 111}
   102  
   103  
   104  
   105  
   106  finish_test