AryaWu/sqlite

1# 2016-08-222#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# Use of row values in CASE statements.12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set ::testprefix rowvalue817 18do_execsql_test 1.1 {19  CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);20  INSERT INTO t1(a,b,c,d) VALUES21     (1,1,2,3),22     (2,2,3,4),23     (3,1,2,4),24     (4,2,3,5),25     (5,3,4,6),26     (6,4,5,9);27  SELECT a, CASE (b,c) WHEN (1,2) THEN 'aleph'28                       WHEN (2,3) THEN 'bet'29                       WHEN (3,4) THEN 'gimel'30                       ELSE '-' END,31         '|'32    FROM t133   ORDER BY a;34} {1 aleph | 2 bet | 3 aleph | 4 bet | 5 gimel | 6 - |}35do_execsql_test 1.2 {36  SELECT a, CASE (b,c,d) WHEN (1,2,3) THEN 'aleph'37                         WHEN (2,3,4) THEN 'bet'38                         WHEN (3,4,6) THEN 'gimel'39                         ELSE '-' END,40         '|'41    FROM t142   ORDER BY a;43} {1 aleph | 2 bet | 3 - | 4 - | 5 gimel | 6 - |}44 45do_execsql_test 2.1 {46  CREATE TABLE t2(x INTEGER PRIMARY KEY, y);47  INSERT INTO t2(x,y) VALUES(1,6),(2,5),(3,4),(4,3),(5,2),(6,1);48  SELECT x, CASE (SELECT b,c FROM t1 WHERE a=y)49            WHEN (1,2) THEN 'aleph'50            WHEN (2,3) THEN 'bet'51            WHEN (3,4) THEN 'gimel'52            ELSE '-' END,53         '|'54    FROM t255   ORDER BY +x;56} {1 - | 2 gimel | 3 bet | 4 aleph | 5 bet | 6 aleph |}57 58 59finish_test60