postgres.git / summary / log / commit / refs

commit    1e22166e5ebbc3df7684209657ea58ba880546d6
Author:   Andres Freund <andres@anarazel.de>
Date:     Fri Mar 09 01:07:16 2018 +0000
First released in: 11.0

    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


 src/test/regress/expected/boolean.out | 83 +++++++++++++++++++++++++++++++++++
 src/test/regress/sql/boolean.sql      | 29 ++++++++++++
 2 files changed, 112 insertions(+)

diff --git a/src/test/regress/expected/boolean.out b/src/test/regress/expected/boolean.out
index a6e6000c669..a812aee00c5 100644
--- a/src/test/regress/expected/boolean.out
+++ b/src/test/regress/expected/boolean.out
@@ -465,6 +465,88 @@ FROM booltbl3 ORDER BY o;
  null  | f      | t         | f       | t          | t         | f
 (3 rows)
 
+-- Test to make sure short-circuiting and NULL handling is
+-- correct. Use a table as source to prevent constant simplification
+-- to interfer.
+CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool);
+INSERT INTO booltbl4 VALUES (false, true, null);
+\pset null '(null)'
+-- AND expression need to return null if there's any nulls and not all
+-- of the value are true
+SELECT istrue AND isnul AND istrue FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT istrue AND istrue AND isnul FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT isnul AND istrue AND istrue FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT isfalse AND isnul AND istrue FROM booltbl4;
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT istrue AND isfalse AND isnul FROM booltbl4;
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT isnul AND istrue AND isfalse FROM booltbl4;
+ ?column? 
+----------
+ f
+(1 row)
+
+-- OR expression need to return null if there's any nulls and none
+-- of the value is true
+SELECT isfalse OR isnul OR isfalse FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT isfalse OR isfalse OR isnul FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT isnul OR isfalse OR isfalse FROM booltbl4;
+ ?column? 
+----------
+ (null)
+(1 row)
+
+SELECT isfalse OR isnul OR istrue FROM booltbl4;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT istrue OR isfalse OR isnul FROM booltbl4;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT isnul OR istrue OR isfalse FROM booltbl4;
+ ?column? 
+----------
+ t
+(1 row)
+
 --
 -- Clean up
 -- Many tables are retained by the regression test, but these do not seem
@@ -474,3 +556,4 @@ FROM booltbl3 ORDER BY o;
 DROP TABLE  BOOLTBL1;
 DROP TABLE  BOOLTBL2;
 DROP TABLE  BOOLTBL3;
+DROP TABLE  BOOLTBL4;
diff --git a/src/test/regress/sql/boolean.sql b/src/test/regress/sql/boolean.sql
index cbf335467b5..df61fa4e3e3 100644
--- a/src/test/regress/sql/boolean.sql
+++ b/src/test/regress/sql/boolean.sql
@@ -219,6 +219,33 @@ SELECT
     b IS NOT UNKNOWN AS isnotunknown
 FROM booltbl3 ORDER BY o;
 
+
+-- Test to make sure short-circuiting and NULL handling is
+-- correct. Use a table as source to prevent constant simplification
+-- to interfer.
+CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool);
+INSERT INTO booltbl4 VALUES (false, true, null);
+\pset null '(null)'
+
+-- AND expression need to return null if there's any nulls and not all
+-- of the value are true
+SELECT istrue AND isnul AND istrue FROM booltbl4;
+SELECT istrue AND istrue AND isnul FROM booltbl4;
+SELECT isnul AND istrue AND istrue FROM booltbl4;
+SELECT isfalse AND isnul AND istrue FROM booltbl4;
+SELECT istrue AND isfalse AND isnul FROM booltbl4;
+SELECT isnul AND istrue AND isfalse FROM booltbl4;
+
+-- OR expression need to return null if there's any nulls and none
+-- of the value is true
+SELECT isfalse OR isnul OR isfalse FROM booltbl4;
+SELECT isfalse OR isfalse OR isnul FROM booltbl4;
+SELECT isnul OR isfalse OR isfalse FROM booltbl4;
+SELECT isfalse OR isnul OR istrue FROM booltbl4;
+SELECT istrue OR isfalse OR isnul FROM booltbl4;
+SELECT isnul OR istrue OR isfalse FROM booltbl4;
+
+
 --
 -- Clean up
 -- Many tables are retained by the regression test, but these do not seem
@@ -231,3 +258,5 @@ DROP TABLE  BOOLTBL1;
 DROP TABLE  BOOLTBL2;
 
 DROP TABLE  BOOLTBL3;
+
+DROP TABLE  BOOLTBL4;


[parent: 4f63e85eb149]