Return to alias.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/alias.test, revision 1.1

1.1     ! misho       1: # 2008 August 28
        !             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: #
        !            12: # This file implements regression tests for SQLite library.  The
        !            13: # focus of this script is correct code generation of aliased result-set
        !            14: # values.  See ticket #3343.
        !            15: #
        !            16: # $Id: alias.test,v 1.3 2009/04/23 13:22:44 drh Exp $
        !            17: #
        !            18: set testdir [file dirname $argv0]
        !            19: source $testdir/tester.tcl
        !            20: 
        !            21: # Aliases are currently evaluated twice.  We might try to change this
        !            22: # in the future.  But not now.
        !            23: return
        !            24: 
        !            25: # A procedure to return a sequence of increasing integers.
        !            26: #
        !            27: namespace eval ::seq {
        !            28:   variable counter 0
        !            29:   proc value {args} {
        !            30:     variable counter
        !            31:     incr counter
        !            32:     return $counter
        !            33:   }
        !            34:   proc reset {} {
        !            35:     variable counter
        !            36:     set counter 0
        !            37:   }
        !            38: }
        !            39: 
        !            40: 
        !            41: do_test alias-1.1 {
        !            42:   db function sequence ::seq::value
        !            43:   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}
        !            51: do_test alias-1.2 {
        !            52:   ::seq::reset
        !            53:   db eval {
        !            54:     SELECT x, sequence() AS y FROM t1 WHERE y>0
        !            55:   }
        !            56: } {9 1 8 2 7 3}
        !            57: do_test alias-1.3 {
        !            58:   ::seq::reset
        !            59:   db eval {
        !            60:     SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99
        !            61:   }
        !            62: } {9 1 8 2 7 3}
        !            63: do_test alias-1.4 {
        !            64:   ::seq::reset
        !            65:   db eval {
        !            66:     SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55
        !            67:   }
        !            68: } {9 1 8 2 7 3}
        !            69: do_test alias-1.5 {
        !            70:   ::seq::reset
        !            71:   db eval {
        !            72:     SELECT x, sequence() AS y FROM t1
        !            73:      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==2
        !            75:   }
        !            76: } {8 2}
        !            77: do_test alias-1.6 {
        !            78:   ::seq::reset
        !            79:   db eval {
        !            80:     SELECT x, sequence() AS y FROM t1 WHERE y BETWEEN 0 AND 99
        !            81:   }
        !            82: } {9 1 8 2 7 3}
        !            83: #do_test alias-1.7 {
        !            84: #  ::seq::reset
        !            85: #  db eval {
        !            86: #    SELECT x, sequence() AS y FROM t1 WHERE y IN (55,66,3)
        !            87: #  }
        !            88: #} {7 3}
        !            89: do_test alias-1.8 {
        !            90:   ::seq::reset
        !            91:   db eval {
        !            92:     SELECT x, 1-sequence() AS y FROM t1 ORDER BY y
        !            93:   }
        !            94: } {7 -2 8 -1 9 0}
        !            95: do_test alias-1.9 {
        !            96:   ::seq::reset
        !            97:   db eval {
        !            98:     SELECT x, sequence() AS y FROM t1 ORDER BY -y
        !            99:   }
        !           100: } {7 3 8 2 9 1}
        !           101: do_test alias-1.10 {
        !           102:   ::seq::reset
        !           103:   db eval {
        !           104:     SELECT x, sequence() AS y FROM t1 ORDER BY x%2, y
        !           105:   }
        !           106: } {8 2 9 1 7 3}
        !           107: 
        !           108: unset -nocomplain random_int_list
        !           109: set random_int_list [db eval {
        !           110:    SELECT random()&2147483647 AS r FROM t1, t1, t1, t1 ORDER BY r
        !           111: }]
        !           112: do_test alias-1.11 {
        !           113:   lsort -integer $::random_int_list
        !           114: } $random_int_list
        !           115: 
        !           116: 
        !           117: do_test alias-2.1 {
        !           118:   db eval {
        !           119:     SELECT 4 UNION SELECT 1 ORDER BY 1
        !           120:   }
        !           121: } {1 4}
        !           122: do_test alias-2.2 {
        !           123:   db eval {
        !           124:     SELECT 4 UNION SELECT 1 UNION SELECT 9 ORDER BY 1
        !           125:   }
        !           126: } {1 4 9}
        !           127: 
        !           128: if 0 {
        !           129:   # Aliases in the GROUP BY clause cause the expression to be evaluated
        !           130:   # twice in the current implementation.  This might change in the future.
        !           131:   #
        !           132:   do_test alias-3.1 {
        !           133:     ::seq::reset
        !           134:     db eval {
        !           135:       SELECT sequence(*) AS y, count(*) AS z FROM t1 GROUP BY y ORDER BY z, y
        !           136:     }
        !           137:   } {1 1 2 1 3 1}
        !           138: }
        !           139: 
        !           140: finish_test