boolean.out

--
-- BOOLEAN
--
--
-- sanity check - if this fails go insane!
--
SELECT 1 AS one;
 one 
-----
   1
(1 row)

-- ******************testing built-in type bool********************
-- check bool type-casting as well as and, or, not in qualifications--
SELECT bool 't' AS true;
 true 
------
 t
(1 row)

SELECT bool 'f' AS false;
 false 
-------
 f
(1 row)

SELECT bool 't' or bool 'f' AS true;
 true 
------
 t
(1 row)

SELECT bool 't' and bool 'f' AS false;
 false 
-------
 f
(1 row)

SELECT not bool 'f' AS true;
 true 
------
 t
(1 row)

SELECT bool 't' = bool 'f' AS false;
 false 
-------
 f
(1 row)

SELECT bool 't' <> bool 'f' AS true;
 true 
------
 t
(1 row)

CREATE TABLE BOOLTBL1 (f1 bool);
INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');
INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true');
-- BOOLTBL1 should be full of true's at this point 
SELECT '' AS t_3, BOOLTBL1.*;
 t_3 | f1 
-----+----
     | t
     | t
     | t
(3 rows)

SELECT '' AS t_3, BOOLTBL1.*
   FROM BOOLTBL1
   WHERE f1 = bool 'true';
 t_3 | f1 
-----+----
     | t
     | t
     | t
(3 rows)

SELECT '' AS t_3, BOOLTBL1.* 
   FROM BOOLTBL1
   WHERE f1 <> bool 'false';
 t_3 | f1 
-----+----
     | t
     | t
     | t
(3 rows)

SELECT '' AS zero, BOOLTBL1.*
   FROM BOOLTBL1
   WHERE booleq(bool 'false', f1);
 zero | f1 
------+----
(0 rows)

INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f');
SELECT '' AS f_1, BOOLTBL1.* 
   FROM BOOLTBL1
   WHERE f1 = bool 'false';
 f_1 | f1 
-----+----
     | f
(1 row)

CREATE TABLE BOOLTBL2 (f1 bool);
INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f');
INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false');
INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False');
INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE');
-- This is now an invalid expression
-- For pre-v6.3 this evaluated to false - thomas 1997-10-23
INSERT INTO BOOLTBL2 (f1) 
   VALUES (bool 'XXX');  
ERROR:  invalid input syntax for type boolean: "XXX"
-- BOOLTBL2 should be full of false's at this point 
SELECT '' AS f_4, BOOLTBL2.*;
 f_4 | f1 
-----+----
     | f
     | f
     | f
     | f
(4 rows)

SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
   WHERE BOOLTBL2.f1 <> BOOLTBL1.f1;
 tf_12 | f1 | f1 
-------+----+----
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
(12 rows)

SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
   WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1);
 tf_12 | f1 | f1 
-------+----+----
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
       | t  | f
(12 rows)

SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.*
   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false';
 ff_4 | f1 | f1 
------+----+----
      | f  | f
      | f  | f
      | f  | f
      | f  | f
(4 rows)

SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.*
   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true'
   ORDER BY BOOLTBL1.f1, BOOLTBL2.f1;
 tf_12_ff_4 | f1 | f1 
------------+----+----
            | f  | f
            | f  | f
            | f  | f
            | f  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
            | t  | f
(16 rows)

--
-- SQL92 syntax
-- Try all combinations to ensure that we get nothing when we expect nothing
-- - thomas 2000-01-04
--
SELECT '' AS "True", f1
   FROM BOOLTBL1
   WHERE f1 IS TRUE;
 True | f1 
------+----
      | t
      | t
      | t
(3 rows)

SELECT '' AS "Not False", f1
   FROM BOOLTBL1
   WHERE f1 IS NOT FALSE;
 Not False | f1 
-----------+----
           | t
           | t
           | t
(3 rows)

SELECT '' AS "False", f1
   FROM BOOLTBL1
   WHERE f1 IS FALSE;
 False | f1 
-------+----
       | f
(1 row)

SELECT '' AS "Not True", f1
   FROM BOOLTBL1
   WHERE f1 IS NOT TRUE;
 Not True | f1 
----------+----
          | f
(1 row)

SELECT '' AS "True", f1
   FROM BOOLTBL2
   WHERE f1 IS TRUE;
 True | f1 
------+----
(0 rows)

SELECT '' AS "Not False", f1
   FROM BOOLTBL2
   WHERE f1 IS NOT FALSE;
 Not False | f1 
-----------+----
(0 rows)

SELECT '' AS "False", f1
   FROM BOOLTBL2
   WHERE f1 IS FALSE;
 False | f1 
-------+----
       | f
       | f
       | f
       | f
(4 rows)

SELECT '' AS "Not True", f1
   FROM BOOLTBL2
   WHERE f1 IS NOT TRUE;
 Not True | f1 
----------+----
          | f
          | f
          | f
          | f
(4 rows)

--
-- Clean up
-- Many tables are retained by the regression test, but these do not seem
--  particularly useful so just get rid of them for now.
--  - thomas 1997-11-30
--
DROP TABLE  BOOLTBL1;
DROP TABLE  BOOLTBL2;