# 2008 August 28
#
# 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. The
# focus of this script is correct code generation of aliased result-set
# values. See ticket #3343.
#
# $Id: alias.test,v 1.3 2009/04/23 13:22:44 drh Exp $
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Aliases are currently evaluated twice. We might try to change this
# in the future. But not now.
return
# A procedure to return a sequence of increasing integers.
#
namespace eval ::seq {
variable counter 0
proc value {args} {
variable counter
incr counter
return $counter
}
proc reset {} {
variable counter
set counter 0
}
}
do_test alias-1.1 {
db function sequence ::seq::value
db eval {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(9);
INSERT INTO t1 VALUES(8);
INSERT INTO t1 VALUES(7);
SELECT x, sequence() FROM t1;
}
} {9 1 8 2 7 3}
do_test alias-1.2 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 WHERE y>0
}
} {9 1 8 2 7 3}
do_test alias-1.3 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
}
} {9 1 8 2 7 3}
do_test alias-1.4 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
}
} {9 1 8 2 7 3}
do_test alias-1.5 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1
WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)
AND y NOT LIKE 'abc%' AND y%10==2
}
} {8 2}
do_test alias-1.6 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
}
} {9 1 8 2 7 3}
#do_test alias-1.7 {
# ::seq::reset
# db eval {
# SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
# }
#} {7 3}
do_test alias-1.8 {
::seq::reset
db eval {
SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
}
} {7 -2 8 -1 9 0}
do_test alias-1.9 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 ORDER BY -y
}
} {7 3 8 2 9 1}
do_test alias-1.10 {
::seq::reset
db eval {
SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
}
} {8 2 9 1 7 3}
unset -nocomplain random_int_list
set random_int_list [db eval {
SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
}]
do_test alias-1.11 {
lsort -integer $::random_int_list
} $random_int_list
do_test alias-2.1 {
db eval {
SELECT 4 UNION SELECT 1 ORDER BY 1
}
} {1 4}
do_test alias-2.2 {
db eval {
SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
}
} {1 4 9}
if 0 {
# Aliases in the GROUP BY clause cause the expression to be evaluated
# twice in the current implementation. This might change in the future.
#
do_test alias-3.1 {
::seq::reset
db eval {
SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
}
} {1 1 2 1 3 1}
}
finish_test