# 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.1 2008/08/29 02:14:03 drh Exp $

 #

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # 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 {

 --pragma vdbe_listing=on; pragma vdbe_trace=on;

     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