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

     1  # 2015-08-26
     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.
    12  # 
    13  # This file seeks to verify that expressions (and especially functions)
    14  # that are in both the ORDER BY clause and the result set are only
    15  # evaluated once.
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  set ::testprefix orderby9
    21  
    22  
    23  do_execsql_test setup {
    24    -- create a table with many entries
    25    CREATE TABLE t1(x);
    26    WITH RECURSIVE
    27       c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
    28    INSERT INTO t1 SELECT x FROM c;
    29  }
    30  
    31  # Some versions of TCL are unable to [lsort -int] for
    32  # 64-bit integers.  So we write our own comparison
    33  # routine.
    34  proc bigintcompare {a b} {
    35    set x [expr {$a-$b}]
    36    if {$x<0} {return -1}
    37    if {$x>0} {return +1}
    38    return 0
    39  }
    40  do_test 1.0 {
    41    set l1 {}
    42    # If random() is only evaluated once and then reused for each row, then
    43    # the output should appear in sorted order.  If random() is evaluated 
    44    # separately for the result set and the ORDER BY clause, then the output
    45    # order will be random.
    46    db eval {SELECT random() AS y FROM t1 ORDER BY 1;} {lappend l1 $y}
    47    expr {$l1==[lsort -command bigintcompare $l1]}
    48  } {1}
    49  
    50  do_test 1.1 {
    51    set l1 {}
    52    db eval {SELECT random() AS y FROM t1 ORDER BY random();} {lappend l1 $y}
    53    expr {$l1==[lsort -command bigintcompare $l1]}
    54  } {1}
    55  
    56  do_test 1.2 {
    57    set l1 {}
    58    db eval {SELECT random() AS y FROM t1 ORDER BY +random();} {lappend l1 $y}
    59    expr {$l1==[lsort -command bigintcompare $l1]}
    60  } {0}
    61  
    62  finish_test