# 2008 September 16
#
# 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.
#
# $Id: selectC.test,v 1.3 2008/09/17 00:13:12 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Ticket #
do_test selectC-1.1 {
execsql {
CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES(1,'aaa','bbb');
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 VALUES(2,'ccc','ddd');
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE y IN ('aaabbb','xxx');
}
} {1 aaabbb}
do_test selectC-1.2 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE b||c IN ('aaabbb','xxx');
}
} {1 aaabbb}
do_test selectC-1.3 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE y='aaabbb'
}
} {1 aaabbb}
do_test selectC-1.4 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE b||c='aaabbb'
}
} {1 aaabbb}
do_test selectC-1.5 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE x=2
}
} {2 cccddd}
do_test selectC-1.6 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE a=2
}
} {2 cccddd}
do_test selectC-1.7 {
execsql {
SELECT DISTINCT a AS x, b||c AS y
FROM t1
WHERE +y='aaabbb'
}
} {1 aaabbb}
do_test selectC-1.8 {
execsql {
SELECT a AS x, b||c AS y
FROM t1
GROUP BY x, y
HAVING y='aaabbb'
}
} {1 aaabbb}
do_test selectC-1.9 {
execsql {
SELECT a AS x, b||c AS y
FROM t1
GROUP BY x, y
HAVING b||c='aaabbb'
}
} {1 aaabbb}
do_test selectC-1.10 {
execsql {
SELECT a AS x, b||c AS y
FROM t1
WHERE y='aaabbb'
GROUP BY x, y
}
} {1 aaabbb}
do_test selectC-1.11 {
execsql {
SELECT a AS x, b||c AS y
FROM t1
WHERE b||c='aaabbb'
GROUP BY x, y
}
} {1 aaabbb}
do_test selectC-1.12 {
execsql {
SELECT DISTINCT upper(b) AS x
FROM t1
ORDER BY x
}
} {AAA CCC}
do_test selectC-1.13 {
execsql {
SELECT upper(b) AS x
FROM t1
GROUP BY x
ORDER BY x
}
} {AAA CCC}
do_test selectC-1.14 {
execsql {
SELECT upper(b) AS x
FROM t1
ORDER BY x DESC
}
} {CCC AAA AAA}
finish_test