sl@0: # 2008 August 28
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: #
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script is correct code generation of aliased result-set
sl@0: # values.  See ticket #3343.
sl@0: #
sl@0: # $Id: alias.test,v 1.1 2008/08/29 02:14:03 drh Exp $
sl@0: #
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # A procedure to return a sequence of increasing integers.
sl@0: #
sl@0: namespace eval ::seq {
sl@0:   variable counter 0
sl@0:   proc value {args} {
sl@0:     variable counter
sl@0:     incr counter
sl@0:     return $counter
sl@0:   }
sl@0:   proc reset {} {
sl@0:     variable counter
sl@0:     set counter 0
sl@0:   }
sl@0: }
sl@0: 
sl@0: 
sl@0: do_test alias-1.1 {
sl@0:   db function sequence ::seq::value
sl@0:   db eval {
sl@0:     CREATE TABLE t1(x);
sl@0:     INSERT INTO t1 VALUES(9);
sl@0:     INSERT INTO t1 VALUES(8);
sl@0:     INSERT INTO t1 VALUES(7);
sl@0:     SELECT x, sequence() FROM t1;
sl@0:   }
sl@0: } {9 1 8 2 7 3}
sl@0: do_test alias-1.2 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0: --pragma vdbe_listing=on; pragma vdbe_trace=on;
sl@0:     SELECT x, sequence() AS y FROM t1 WHERE y>0
sl@0:   }
sl@0: } {9 1 8 2 7 3}
sl@0: do_test alias-1.3 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
sl@0:   }
sl@0: } {9 1 8 2 7 3}
sl@0: do_test alias-1.4 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
sl@0:   }
sl@0: } {9 1 8 2 7 3}
sl@0: do_test alias-1.5 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1
sl@0:      WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
sl@0:        AND y NOT LIKE 'abc%' AND y%10==2
sl@0:   }
sl@0: } {8 2}
sl@0: do_test alias-1.6 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
sl@0:   }
sl@0: } {9 1 8 2 7 3}
sl@0: do_test alias-1.7 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
sl@0:   }
sl@0: } {7 3}
sl@0: do_test alias-1.8 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
sl@0:   }
sl@0: } {7 -2 8 -1 9 0}
sl@0: do_test alias-1.9 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 ORDER BY -y
sl@0:   }
sl@0: } {7 3 8 2 9 1}
sl@0: do_test alias-1.10 {
sl@0:   ::seq::reset
sl@0:   db eval {
sl@0:     SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
sl@0:   }
sl@0: } {8 2 9 1 7 3}
sl@0: 
sl@0: unset -nocomplain random_int_list
sl@0: set random_int_list [db eval {
sl@0:    SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
sl@0: }]
sl@0: do_test alias-1.11 {
sl@0:   lsort -integer $::random_int_list
sl@0: } $random_int_list
sl@0: 
sl@0: 
sl@0: do_test alias-2.1 {
sl@0:   db eval {
sl@0:     SELECT 4 UNION SELECT 1 ORDER BY 1
sl@0:   }
sl@0: } {1 4}
sl@0: do_test alias-2.2 {
sl@0:   db eval {
sl@0:     SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
sl@0:   }
sl@0: } {1 4 9}
sl@0: 
sl@0: if 0 {
sl@0:   # Aliases in the GROUP BY clause cause the expression to be evaluated
sl@0:   # twice in the current implementation.  This might change in the future.
sl@0:   #
sl@0:   do_test alias-3.1 {
sl@0:     ::seq::reset
sl@0:     db eval {
sl@0:       SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
sl@0:     }
sl@0:   } {1 1 2 1 3 1}
sl@0: }
sl@0: 
sl@0: finish_test