Skip to content

Commit 1e22166

Browse files
anarazelanarazel
committed
Expand AND/OR regression tests around NULL handling.
Previously there were no tests verifying that NULL handling in AND/OR was correct (i.e. that NULL rather than false is returned if expression doesn't return true). Author: Andres Freund
1 parent 4f63e85 commit 1e22166

File tree

2 files changed

+112
-0
lines changed

2 files changed

+112
-0
lines changed

‎src/test/regress/expected/boolean.out

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -465,6 +465,88 @@ FROM booltbl3 ORDER BY o;
465465
null | f | t | f | t | t | f
466466
(3 rows)
467467

468+
-- Test to make sure short-circuiting and NULL handling is
469+
-- correct. Use a table as source to prevent constant simplification
470+
-- to interfer.
471+
CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool);
472+
INSERT INTO booltbl4 VALUES (false, true, null);
473+
\pset null '(null)'
474+
-- AND expression need to return null if there's any nulls and not all
475+
-- of the value are true
476+
SELECT istrue AND isnul AND istrue FROM booltbl4;
477+
?column?
478+
----------
479+
(null)
480+
(1 row)
481+
482+
SELECT istrue AND istrue AND isnul FROM booltbl4;
483+
?column?
484+
----------
485+
(null)
486+
(1 row)
487+
488+
SELECT isnul AND istrue AND istrue FROM booltbl4;
489+
?column?
490+
----------
491+
(null)
492+
(1 row)
493+
494+
SELECT isfalse AND isnul AND istrue FROM booltbl4;
495+
?column?
496+
----------
497+
f
498+
(1 row)
499+
500+
SELECT istrue AND isfalse AND isnul FROM booltbl4;
501+
?column?
502+
----------
503+
f
504+
(1 row)
505+
506+
SELECT isnul AND istrue AND isfalse FROM booltbl4;
507+
?column?
508+
----------
509+
f
510+
(1 row)
511+
512+
-- OR expression need to return null if there's any nulls and none
513+
-- of the value is true
514+
SELECT isfalse OR isnul OR isfalse FROM booltbl4;
515+
?column?
516+
----------
517+
(null)
518+
(1 row)
519+
520+
SELECT isfalse OR isfalse OR isnul FROM booltbl4;
521+
?column?
522+
----------
523+
(null)
524+
(1 row)
525+
526+
SELECT isnul OR isfalse OR isfalse FROM booltbl4;
527+
?column?
528+
----------
529+
(null)
530+
(1 row)
531+
532+
SELECT isfalse OR isnul OR istrue FROM booltbl4;
533+
?column?
534+
----------
535+
t
536+
(1 row)
537+
538+
SELECT istrue OR isfalse OR isnul FROM booltbl4;
539+
?column?
540+
----------
541+
t
542+
(1 row)
543+
544+
SELECT isnul OR istrue OR isfalse FROM booltbl4;
545+
?column?
546+
----------
547+
t
548+
(1 row)
549+
468550
--
469551
-- Clean up
470552
-- Many tables are retained by the regression test, but these do not seem
@@ -474,3 +556,4 @@ FROM booltbl3 ORDER BY o;
474556
DROP TABLE BOOLTBL1;
475557
DROP TABLE BOOLTBL2;
476558
DROP TABLE BOOLTBL3;
559+
DROP TABLE BOOLTBL4;

‎src/test/regress/sql/boolean.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -219,6 +219,33 @@ SELECT
219219
b IS NOT UNKNOWN AS isnotunknown
220220
FROM booltbl3 ORDER BY o;
221221

222+
223+
-- Test to make sure short-circuiting and NULL handling is
224+
-- correct. Use a table as source to prevent constant simplification
225+
-- to interfer.
226+
CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool);
227+
INSERT INTO booltbl4 VALUES (false, true, null);
228+
\pset null '(null)'
229+
230+
-- AND expression need to return null if there's any nulls and not all
231+
-- of the value are true
232+
SELECT istrue AND isnul AND istrue FROM booltbl4;
233+
SELECT istrue AND istrue AND isnul FROM booltbl4;
234+
SELECT isnul AND istrue AND istrue FROM booltbl4;
235+
SELECT isfalse AND isnul AND istrue FROM booltbl4;
236+
SELECT istrue AND isfalse AND isnul FROM booltbl4;
237+
SELECT isnul AND istrue AND isfalse FROM booltbl4;
238+
239+
-- OR expression need to return null if there's any nulls and none
240+
-- of the value is true
241+
SELECT isfalse OR isnul OR isfalse FROM booltbl4;
242+
SELECT isfalse OR isfalse OR isnul FROM booltbl4;
243+
SELECT isnul OR isfalse OR isfalse FROM booltbl4;
244+
SELECT isfalse OR isnul OR istrue FROM booltbl4;
245+
SELECT istrue OR isfalse OR isnul FROM booltbl4;
246+
SELECT isnul OR istrue OR isfalse FROM booltbl4;
247+
248+
222249
--
223250
-- Clean up
224251
-- Many tables are retained by the regression test, but these do not seem
@@ -231,3 +258,5 @@ DROP TABLE BOOLTBL1;
231258
DROP TABLE BOOLTBL2;
232259

233260
DROP TABLE BOOLTBL3;
261+
262+
DROP TABLE BOOLTBL4;

0 commit comments

Comments
 (0)