AryaWu/sqlite

1# 2008 August 282#3# The author disclaims copyright to this source code.  In place of4# 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#12# This file implements regression tests for SQLite library.  The13# focus of this script is correct code generation of aliased result-set14# values.  See ticket #3343.15#16# $Id: alias.test,v 1.3 2009/04/23 13:22:44 drh Exp $17#18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# Aliases are currently evaluated twice.  We might try to change this22# in the future.  But not now.23return24 25# A procedure to return a sequence of increasing integers.26#27namespace eval ::seq {28  variable counter 029  proc value {args} {30    variable counter31    incr counter32    return $counter33  }34  proc reset {} {35    variable counter36    set counter 037  }38}39 40 41do_test alias-1.1 {42  db function sequence ::seq::value43  db eval {44    CREATE TABLE t1(x);45    INSERT INTO t1 VALUES(9);46    INSERT INTO t1 VALUES(8);47    INSERT INTO t1 VALUES(7);48    SELECT x, sequence() FROM t1;49  }50} {9 1 8 2 7 3}51do_test alias-1.2 {52  ::seq::reset53  db eval {54    SELECT x, sequence() AS y FROM t1 WHERE y>055  }56} {9 1 8 2 7 3}57do_test alias-1.3 {58  ::seq::reset59  db eval {60    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<9961  }62} {9 1 8 2 7 3}63do_test alias-1.4 {64  ::seq::reset65  db eval {66    SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=5567  }68} {9 1 8 2 7 3}69do_test alias-1.5 {70  ::seq::reset71  db eval {72    SELECT x, sequence() AS y FROM t173     WHERE y>0 AND y<99 AND y!=55 AND y NOT IN (56,57,58)74       AND y NOT LIKE 'abc%' AND y%10==275  }76} {8 2}77do_test alias-1.6 {78  ::seq::reset79  db eval {80    SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 9981  }82} {9 1 8 2 7 3}83#do_test alias-1.7 {84#  ::seq::reset85#  db eval {86#    SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)87#  }88#} {7 3}89do_test alias-1.8 {90  ::seq::reset91  db eval {92    SELECT x, 1-sequence() AS y FROM t1 ORDER BY y93  }94} {7 -2 8 -1 9 0}95do_test alias-1.9 {96  ::seq::reset97  db eval {98    SELECT x, sequence() AS y FROM t1 ORDER BY -y99  }100} {7 3 8 2 9 1}101do_test alias-1.10 {102  ::seq::reset103  db eval {104    SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y105  }106} {8 2 9 1 7 3}107 108unset -nocomplain random_int_list109set random_int_list [db eval {110   SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r111}]112do_test alias-1.11 {113  lsort -integer $::random_int_list114} $random_int_list115 116 117do_test alias-2.1 {118  db eval {119    SELECT 4 UNION SELECT 1 ORDER BY 1120  }121} {1 4}122do_test alias-2.2 {123  db eval {124    SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1125  }126} {1 4 9}127 128if 0 {129  # Aliases in the GROUP BY clause cause the expression to be evaluated130  # twice in the current implementation.  This might change in the future.131  #132  do_test alias-3.1 {133    ::seq::reset134    db eval {135      SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y136    }137  } {1 1 2 1 3 1}138}139 140finish_test141