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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20set ::testprefix orderby9 21 22 23do_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. 34proc 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} 40do_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 50do_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 56do_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 62finish_test 63