aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/expr.test21
-rw-r--r--test/select4.test44
-rw-r--r--test/subselect.test7
-rw-r--r--test/trigger2.test14
-rw-r--r--test/unique.test35
5 files changed, 97 insertions, 24 deletions
diff --git a/test/expr.test b/test/expr.test
index 784b80298..0d93df662 100644
--- a/test/expr.test
+++ b/test/expr.test
@@ -11,7 +11,7 @@
# This file implements regression tests for SQLite library. The
# focus of this file is testing expressions.
#
-# $Id: expr.test,v 1.19 2002/03/24 13:13:29 drh Exp $
+# $Id: expr.test,v 1.20 2002/05/26 20:54:34 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -22,8 +22,7 @@ execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
proc test_expr {name settings expr result} {
do_test $name [format {
- execsql {UPDATE test1 SET %s}
- execsql {SELECT %s FROM test1}
+ execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
} $settings $expr] $result
}
@@ -63,7 +62,7 @@ test_expr expr-1.33 {i1=1, i2=2} {i1=1 OR i2=1} {1}
test_expr expr-1.34 {i1=1, i2=2} {i1=2 OR i2=2} {1}
test_expr expr-1.35 {i1=1, i2=2} {i1-i2=-1} {1}
test_expr expr-1.36 {i1=1, i2=0} {not i1} {0}
-test_expr expr-1.37 {i1=1, i2=NULL} {not i2} {1}
+test_expr expr-1.37 {i1=1, i2=0} {not i2} {1}
test_expr expr-1.38 {i1=1} {-i1} {-1}
test_expr expr-1.39 {i1=1} {+i1} {1}
test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3}
@@ -320,8 +319,8 @@ proc test_expr2 {name expr result} {
test_expr2 expr-7.2 {a<10 AND a>8} {9}
test_expr2 expr-7.3 {a<=10 AND a>=8} {8 9 10}
test_expr2 expr-7.4 {a>=8 AND a<=10} {8 9 10}
-test_expr2 expr-7.5 {a>=20 OR a<=1} {{} 1 20}
-test_expr2 expr-7.6 {b!=4 AND a<=3} {{} 1 3}
+test_expr2 expr-7.5 {a>=20 OR a<=1} {1 20}
+test_expr2 expr-7.6 {b!=4 AND a<=3} {1 3}
test_expr2 expr-7.7 {b==8 OR b==16 OR b==32} {3 4 5}
test_expr2 expr-7.8 {NOT b<>8 OR b==1024} {3 10}
test_expr2 expr-7.9 {b LIKE '10%'} {10 20}
@@ -332,13 +331,13 @@ test_expr2 expr-7.13 {b GLOB '*1[456]'} {4}
test_expr2 expr-7.14 {a ISNULL} {{}}
test_expr2 expr-7.15 {a NOTNULL AND a<3} {1 2}
test_expr2 expr-7.16 {a AND a<3} {1 2}
-test_expr2 expr-7.17 {NOT a} {{}}
+test_expr2 expr-7.17 {NOT a} {}
test_expr2 expr-7.18 {a==11 OR (b>1000 AND b<2000)} {10 11}
-test_expr2 expr-7.19 {a<=1 OR a>=20} {{} 1 20}
-test_expr2 expr-7.20 {a<1 OR a>20} {{}}
-test_expr2 expr-7.21 {a>19 OR a<1} {{} 20}
+test_expr2 expr-7.19 {a<=1 OR a>=20} {1 20}
+test_expr2 expr-7.20 {a<1 OR a>20} {}
+test_expr2 expr-7.21 {a>19 OR a<1} {20}
test_expr2 expr-7.22 {a!=1 OR a=100} \
- {{} 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
+ {2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
test_expr2 expr-7.23 {(a notnull AND a<4) OR a==8} {1 2 3 8}
test_expr2 expr-7.24 {a LIKE '2_' OR a==8} {8 20}
test_expr2 expr-7.25 {a GLOB '2?' OR a==8} {8 20}
diff --git a/test/select4.test b/test/select4.test
index 3a32dc0de..2b47b3574 100644
--- a/test/select4.test
+++ b/test/select4.test
@@ -12,7 +12,7 @@
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
-# $Id: select4.test,v 1.6 2002/05/24 16:14:16 drh Exp $
+# $Id: select4.test,v 1.7 2002/05/26 20:54:35 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -249,6 +249,48 @@ do_test select4-6.2 {
}
} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
+# NULLs are distinct. Make sure the UNION operator recognizes this
+#
+do_test select4-6.3 {
+ execsql {
+ SELECT NULL UNION SELECT NULL UNION
+ SELECT 1 UNION SELECT 2 AS 'x'
+ ORDER BY x;
+ }
+} {{} {} 1 2}
+do_test select4-6.3 {
+ execsql {
+ SELECT NULL UNION ALL SELECT NULL UNION ALL
+ SELECT 1 UNION ALL SELECT 2 AS 'x'
+ ORDER BY x;
+ }
+} {{} {} 1 2}
+
+# Make sure the DISTINCT keyword treats NULLs as DISTINCT
+#
+do_test select4-6.4 {
+ execsql {
+ SELECT * FROM (
+ SELECT NULL, 1 UNION ALL SELECT NULL, 1
+ );
+ }
+} {{} 1 {} 1}
+do_test select4-6.5 {
+ execsql {
+ SELECT DISTINCT * FROM (
+ SELECT NULL, 1 UNION ALL SELECT NULL, 1
+ );
+ }
+} {{} 1 {} 1}
+do_test select4-6.6 {
+ execsql {
+ SELECT DISTINCT * FROM (
+ SELECT 1,2 UNION ALL SELECT 1,2
+ );
+ }
+} {1 2}
+
+
# Make sure column names are correct when a compound select appears as
# an expression in the WHERE clause.
#
diff --git a/test/subselect.test b/test/subselect.test
index e2f2559f5..392ab5319 100644
--- a/test/subselect.test
+++ b/test/subselect.test
@@ -12,7 +12,7 @@
# focus of this file is testing SELECT statements that are part of
# expressions.
#
-# $Id: subselect.test,v 1.4 2001/09/16 00:13:28 drh Exp $
+# $Id: subselect.test,v 1.5 2002/05/26 20:54:35 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -55,9 +55,8 @@ do_test subselect-1.3c {
# NULL as the result. Check it out.
#
do_test subselect-1.4 {
- execsql {INSERT INTO t1 VALUES(NULL,8)}
- execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=5)}
-} {8}
+ execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
+} {2}
# Try multiple subselects within a single expression.
#
diff --git a/test/trigger2.test b/test/trigger2.test
index adce11450..ae103ea85 100644
--- a/test/trigger2.test
+++ b/test/trigger2.test
@@ -77,7 +77,7 @@ foreach tbl_defn [ list \
CREATE TRIGGER before_update_row BEFORE UPDATE ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
@@ -85,7 +85,7 @@ foreach tbl_defn [ list \
CREATE TRIGGER after_update_row AFTER UPDATE ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
@@ -94,7 +94,7 @@ foreach tbl_defn [ list \
CREATE TRIGGER conditional_update_row AFTER UPDATE ON tbl FOR EACH ROW
WHEN old.a = 1
BEGIN
- INSERT INTO clog VALUES ( (SELECT max(idx) + 1 FROM clog),
+ INSERT INTO clog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM clog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
@@ -120,7 +120,7 @@ foreach tbl_defn [ list \
INSERT INTO tbl VALUES (300, 200);
CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
0, 0);
@@ -128,7 +128,7 @@ foreach tbl_defn [ list \
CREATE TRIGGER delete_after_row AFTER DELETE ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
old.a, old.b,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
0, 0);
@@ -148,7 +148,7 @@ foreach tbl_defn [ list \
DELETE FROM rlog;
CREATE TRIGGER insert_before_row BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
0, 0,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
@@ -156,7 +156,7 @@ foreach tbl_defn [ list \
CREATE TRIGGER insert_after_row AFTER INSERT ON tbl FOR EACH ROW
BEGIN
- INSERT INTO rlog VALUES ( (SELECT max(idx) + 1 FROM rlog),
+ INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
0, 0,
(SELECT sum(a) FROM tbl), (SELECT sum(b) FROM tbl),
new.a, new.b);
diff --git a/test/unique.test b/test/unique.test
index 146ddf028..27cb3e716 100644
--- a/test/unique.test
+++ b/test/unique.test
@@ -12,7 +12,7 @@
# focus of this file is testing the CREATE UNIQUE INDEX statement,
# and primary keys, and the UNIQUE constraint on table columns
#
-# $Id: unique.test,v 1.3 2001/12/21 14:30:44 drh Exp $
+# $Id: unique.test,v 1.4 2002/05/26 20:54:35 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -162,4 +162,37 @@ do_test unique-3.4 {
}
} {1 {constraint failed}}
+# Make sure NULLs are distinct as far as the UNIQUE tests are
+# concerned.
+#
+do_test unique-4.1 {
+ execsql {
+ CREATE TABLE t4(a UNIQUE, b, c, UNIQUE(b,c));
+ INSERT INTO t4 VALUES(1,2,3);
+ INSERT INTO t4 VALUES(NULL, 2, NULL);
+ SELECT * FROM t4;
+ }
+} {1 2 3 {} 2 {}}
+do_test unique-4.2 {
+ catchsql {
+ INSERT INTO t4 VALUES(NULL, 3, 4);
+ }
+} {0 {}}
+do_test unique-4.3 {
+ execsql {
+ SELECT * FROM t4
+ }
+} {1 2 3 {} 2 {} {} 3 4}
+do_test unique-4.4 {
+ catchsql {
+ INSERT INTO t4 VALUES(2, 2, NULL);
+ }
+} {0 {}}
+do_test unique-4.5 {
+ execsql {
+ SELECT * FROM t4
+ }
+} {1 2 3 {} 2 {} {} 3 4 2 2 {}}
+
+
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 14:07:12 +0000