# 2015-08-26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file seeks to verify that expressions (and especially functions) # that are in both the ORDER BY clause and the result set are only # evaluated once. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix orderby9 do_execsql_test setup { -- create a table with many entries CREATE TABLE t1(x); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) INSERT INTO t1 SELECT x FROM c; } # Some versions of TCL are unable to [lsort -int] for # 64-bit integers. So we write our own comparison # routine. proc bigintcompare {a b} { set x [expr {$a-$b}] if {$x<0} {return -1} if {$x>0} {return +1} return 0 } do_test 1.0 { set l1 {} # If random() is only evaluated once and then reused for each row, then # the output should appear in sorted order. If random() is evaluated # separately for the result set and the ORDER BY clause, then the output # order will be random. db eval {SELECT random() AS y FROM t1 ORDER BY 1;} {lappend l1 $y} expr {$l1==[lsort -command bigintcompare $l1]} } {1} do_test 1.1 { set l1 {} db eval {SELECT random() AS y FROM t1 ORDER BY random();} {lappend l1 $y} expr {$l1==[lsort -command bigintcompare $l1]} } {1} do_test 1.2 { set l1 {} db eval {SELECT random() AS y FROM t1 ORDER BY +random();} {lappend l1 $y} expr {$l1==[lsort -command bigintcompare $l1]} } {0} finish_test