gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alias.test (about)

     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