Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch is-distinct-from Excluding Merge-Ins
This is equivalent to a diff from caf8f574e5 to 68c168b5e5
|
2015-06-15
| ||
| 10:49 | Comment typo fix. No code changes. (check-in: 17f185adb9 user: drh tags: trunk) | |
|
2015-06-13
| ||
| 19:05 | Added test cases based on Bruce Momjian's talk on SQL NULLs. (Leaf check-in: 68c168b5e5 user: drh tags: is-distinct-from) | |
| 11:48 | Add the IS DISTINCT FROM and IS NOT DISTINCT FROM operators. (check-in: 0be4478209 user: drh tags: is-distinct-from) | |
| 11:19 | Omit mutex calls in pcache1 when the mutex is NULL, for a significant performance improvement. (check-in: caf8f574e5 user: drh tags: trunk) | |
| 11:10 | Avoid unnecessary mutex usage in pcache1, for a significant speedup. (Closed-Leaf check-in: dcf4fb8d76 user: drh tags: faster-pcache1-fetch) | |
|
2015-06-12
| ||
| 13:04 | Minor performance optimization in pcache1.c. (check-in: 2e8ad2ead9 user: drh tags: trunk) | |
Changes to src/parse.y.
| ︙ | ︙ | |||
987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 |
pA->pRight = 0;
}
}
}
// expr1 IS expr2
// expr1 IS NOT expr2
//
// If expr2 is NULL then code as TK_ISNULL or TK_NOTNULL. If expr2
// is any other expression, code as TK_IS or TK_ISNOT.
//
expr(A) ::= expr(X) IS expr(Y). {
spanBinaryExpr(&A,pParse,TK_IS,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_ISNULL);
}
expr(A) ::= expr(X) IS NOT expr(Y). {
spanBinaryExpr(&A,pParse,TK_ISNOT,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_NOTNULL);
}
%include {
/* Construct an expression node for a unary prefix operator
*/
static void spanUnaryPrefix(
ExprSpan *pOut, /* Write the new expression node here */
Parse *pParse, /* Parsing context to record errors */
| > > > > > > > > > > | 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 |
pA->pRight = 0;
}
}
}
// expr1 IS expr2
// expr1 IS NOT expr2
// expr1 IS DISTINCT FROM expr2
// expr1 IS NOT DISTINCT FROM expr2
//
// If expr2 is NULL then code as TK_ISNULL or TK_NOTNULL. If expr2
// is any other expression, code as TK_IS or TK_ISNOT.
//
expr(A) ::= expr(X) IS expr(Y). {
spanBinaryExpr(&A,pParse,TK_IS,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_ISNULL);
}
expr(A) ::= expr(X) IS NOT expr(Y). {
spanBinaryExpr(&A,pParse,TK_ISNOT,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_NOTNULL);
}
expr(A) ::= expr(X) IS DISTINCT FROM expr(Y). {
spanBinaryExpr(&A,pParse,TK_ISNOT,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_NOTNULL);
}
expr(A) ::= expr(X) IS NOT DISTINCT FROM expr(Y). {
spanBinaryExpr(&A,pParse,TK_IS,&X,&Y);
binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_ISNULL);
}
%include {
/* Construct an expression node for a unary prefix operator
*/
static void spanUnaryPrefix(
ExprSpan *pOut, /* Write the new expression node here */
Parse *pParse, /* Parsing context to record errors */
|
| ︙ | ︙ |
Added test/momjian1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 |
# 2015-06-12
#
# 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.
#
#***********************************************************************
#
# Test cases derived from Bruce Momjian's talk on NULL handling in SQL.
# http://momjian.us/main/writings/pgsql/nulls.pdf
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
db null nil
do_execsql_test momjian1-100 {
SELECT null;
} {nil}
do_execsql_test momjian1-110 {
CREATE TABLE nulltest (x INTEGER, y INTEGER);
INSERT INTO nulltest VALUES (1,NULL);
SELECT * FROM nulltest;
} {1 nil}
do_execsql_test momjian1-120 {
INSERT INTO nulltest (x) VALUES (2);
SELECT * FROM nulltest;
} {1 nil 2 nil}
do_catchsql_test momjian1-130 {
CREATE TABLE nulltest2 (x INTEGER NOT NULL, y INTEGER NOT NULL);
INSERT INTO nulltest2 VALUES (3,NULL )
} {1 {NOT NULL constraint failed: nulltest2.y}}
do_catchsql_test momjian1-131 {
INSERT INTO nulltest2 (x) VALUES (4);
} {1 {NOT NULL constraint failed: nulltest2.y}}
do_execsql_test momjian1-140 {
SELECT NULL+1;
} {nil}
do_execsql_test momjian1-141 {
SELECT NULL || 'a';
} {nil}
do_execsql_test momjian1-142 {
SELECT 'b' || NULL;
} {nil}
do_execsql_test momjian1-150 {
CREATE TABLE inctest (x INTEGER);
INSERT INTO inctest VALUES (30), (40),(NULL);
SELECT x + 1 FROM inctest;
} {31 41 nil}
do_execsql_test momjian1-160 {
SELECT NULL = 1;
} {nil}
do_execsql_test momjian1-161 {
SELECT NULL = '';
} {nil}
do_execsql_test momjian1-162 {
SELECT NULL = NULL;
} {nil}
do_execsql_test momjian1-163 {
SELECT NULL < NULL + 1;
} {nil}
do_execsql_test momjian1-170 {
SELECT 1 WHERE 1;
} {1}
do_execsql_test momjian1-171 {
SELECT 1 WHERE 0;
} {}
do_execsql_test momjian1-172 {
SELECT 1 WHERE NULL;
} {}
do_execsql_test momjian1-180 {
SELECT 1 AND NULL;
} {nil}
do_execsql_test momjian1-181 {
SELECT NOT NULL;
} {nil}
do_execsql_test momjian1-190 {
SELECT * FROM inctest WHERE x >= 0;
} {30 40}
do_execsql_test momjian1-191 {
SELECT * FROM inctest WHERE x < 0;
} {}
do_execsql_test momjian1-192 {
SELECT * FROM inctest WHERE x < 0 OR x >= 0;
} {30 40}
do_execsql_test momjian1-200 {
SELECT * FROM inctest WHERE x <> 10;
} {30 40}
do_execsql_test momjian1-201 {
SELECT * FROM inctest WHERE x <> 10 OR x = 10;
} {30 40}
do_execsql_test momjian1-210 {
SELECT 1 <> 2 AND 1 <> 3;
} {1}
do_execsql_test momjian1-211 {
SELECT 1 <> 2 AND 1 <> 3 AND 1 <> NULL;
} {nil}
do_execsql_test momjian1-220 {
SELECT 'a' IN (SELECT NULL);
} {nil}
do_execsql_test momjian1-221 {
SELECT 'a' NOT IN (SELECT NULL);
} {nil}
do_execsql_test momjian1-230 {
SELECT 'a' IN (SELECT 'a' UNION ALL SELECT NULL);
} {1}
do_execsql_test momjian1-231 {
SELECT 'a' NOT IN (SELECT 'a' UNION ALL SELECT NULL);
} {0}
do_execsql_test momjian1-232 {
SELECT 'a' IN (SELECT 'b' UNION ALL SELECT NULL);
} {nil}
do_execsql_test momjian1-233 {
SELECT 'a' NOT IN (SELECT 'b' UNION ALL SELECT NULL);
} {nil}
do_execsql_test momjian1-240 {
SELECT 'a' = 'b' OR 'a' = NULL;
} {nil}
do_execsql_test momjian1-241 {
SELECT 'a' <> 'b' AND 'a' <> NULL;
} {nil}
do_execsql_test momjian1-250 {
SELECT NULL = NULL;
} {nil}
do_execsql_test momjian1-251 {
SELECT NULL IS NULL;
} {1}
do_execsql_test momjian1-252 {
SELECT NULL IS NOT NULL;
} {0}
do_execsql_test momjian1-260 {
SELECT * FROM inctest WHERE x IS NULL;
} {nil}
do_execsql_test momjian1-261 {
SELECT * FROM inctest WHERE x IS NOT NULL;
} {30 40}
do_execsql_test momjian1-270 {
SELECT 2 IS DISTINCT FROM 1;
SELECT 2 IS NOT 1
} {1 1}
do_execsql_test momjian1-271 {
SELECT NULL IS DISTINCT FROM 1;
SELECT NULL IS NOT 1;
} {1 1}
do_execsql_test momjian1-272 {
SELECT NULL IS DISTINCT FROM NULL;
SELECT NULL IS NOT NULL;
} {0 0}
do_execsql_test momjian1-273 {
SELECT NULL <> 1;
} {nil}
do_execsql_test momjian1-280 {
-- SELECT * FROM inctest WHERE x IS DISTINCT FROM 30;
SELECT * FROM inctest WHERE x IS NOT 30;
} {40 nil}
do_execsql_test momjian1-281 {
-- SELECT * FROM inctest WHERE x IS NOT DISTINCT FROM 30;
SELECT * FROM inctest WHERE x IS 30;
} {30}
do_execsql_test momjian1-300 {
CREATE TABLE disttest (x INTEGER, y INTEGER);
INSERT INTO disttest VALUES (1, 1), (2, 3), (NULL, NULL);
SELECT * FROM disttest where x IS NOT DISTINCT FROM y;
SELECT * FROM disttest WHERE x IS y;
} {1 1 nil nil 1 1 nil nil}
do_execsql_test momjian1-310 {
WITH ordertest AS (
SELECT NULL
UNION ALL
SELECT 2
UNION ALL
SELECT 1
UNION ALL
SELECT NULL
)
SELECT * FROM ordertest
ORDER BY 1;
} {nil nil 1 2}
do_execsql_test momjian1-320 {
CREATE TABLE uniqtest (x INTEGER);
CREATE UNIQUE INDEX i_uniqtest ON uniqtest (x);
INSERT INTO uniqtest VALUES (1),(NULL), (NULL);
SELECT * FROM uniqtest;
} {1 nil nil}
do_execsql_test momjian1-330 {
CREATE TABLE aggtest (x INTEGER);
INSERT INTO aggtest VALUES (7), (8), (NULL);
SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
} {3 2 15 7 8 7.5}
do_execsql_test momjian1-331 {
DELETE FROM aggtest;
SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
} {0 0 nil nil nil nil}
do_execsql_test momjian1-340 {
DELETE FROM aggtest;
INSERT INTO aggtest VALUES (7), (8), (NULL), (NULL);
SELECT x, COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x)
FROM aggtest
GROUP BY x ORDER BY x;
} {nil 2 0 nil nil nil nil 7 1 1 7 7 7 7.0 8 1 1 8 8 8 8.0}
do_execsql_test momjian1-350 {
SELECT COALESCE(NULL, 0);
} {0}
do_execsql_test momjian1-351 {
SELECT COALESCE(NULL, 'I am null.');
} {{I am null.}}
do_execsql_test momjian1-352 {
CREATE TABLE nullmaptest(x TEXT);
INSERT INTO nullmaptest VALUES ('f'), ('g'), (NULL);
SELECT x, COALESCE(x, 'n/a') FROM nullmaptest;
} {f f g g nil n/a}
do_execsql_test momjian1-353 {
SELECT 'a' || COALESCE(NULL, '') || 'b';
} {ab}
do_execsql_test momjian1-354 {
SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
} {15 15}
do_execsql_test momjian1-355 {
DELETE FROM aggtest;
SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
} {nil 0}
do_execsql_test momjian1-360 {
CREATE TABLE emptytest (x INTEGER);
SELECT * from emptytest;
} {}
do_execsql_test momjian1-361 {
SELECT (SELECT * from emptytest);
} {nil}
do_execsql_test momjian1-362 {
SELECT (SELECT * from emptytest) IS NULL;
} {1}
finish_test
|