Return to alias.test CVS log

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

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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