1-- 2-- BOOLEAN 3-- 4 5-- 6-- sanity check - if this fails go insane! 7-- 8SELECT 1 AS one; 9 10 11-- ******************testing built-in type bool******************** 12 13-- check bool input syntax 14 15SELECT true AS true; 16 17SELECT false AS false; 18 19SELECT bool 't' AS true; 20 21SELECT bool ' f ' AS false; 22 23SELECT bool 'true' AS true; 24 25SELECT bool 'test' AS error; 26 27SELECT bool 'false' AS false; 28 29SELECT bool 'foo' AS error; 30 31SELECT bool 'y' AS true; 32 33SELECT bool 'yes' AS true; 34 35SELECT bool 'yeah' AS error; 36 37SELECT bool 'n' AS false; 38 39SELECT bool 'no' AS false; 40 41SELECT bool 'nay' AS error; 42 43SELECT bool 'on' AS true; 44 45SELECT bool 'off' AS false; 46 47SELECT bool 'of' AS false; 48 49SELECT bool 'o' AS error; 50 51SELECT bool 'on_' AS error; 52 53SELECT bool 'off_' AS error; 54 55SELECT bool '1' AS true; 56 57SELECT bool '11' AS error; 58 59SELECT bool '0' AS false; 60 61SELECT bool '000' AS error; 62 63SELECT bool '' AS error; 64 65-- and, or, not in qualifications 66 67SELECT bool 't' or bool 'f' AS true; 68 69SELECT bool 't' and bool 'f' AS false; 70 71SELECT not bool 'f' AS true; 72 73SELECT bool 't' = bool 'f' AS false; 74 75SELECT bool 't' <> bool 'f' AS true; 76 77SELECT bool 't' > bool 'f' AS true; 78 79SELECT bool 't' >= bool 'f' AS true; 80 81SELECT bool 'f' < bool 't' AS true; 82 83SELECT bool 'f' <= bool 't' AS true; 84 85-- explicit casts to/from text 86SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false; 87SELECT ' true '::text::boolean AS true, 88 ' FALSE'::text::boolean AS false; 89SELECT true::boolean::text AS true, false::boolean::text AS false; 90 91SELECT ' tru e '::text::boolean AS invalid; -- error 92SELECT ''::text::boolean AS invalid; -- error 93 94CREATE TABLE BOOLTBL1 (f1 bool); 95 96INSERT INTO BOOLTBL1 (f1) VALUES (bool 't'); 97 98INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True'); 99 100INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true'); 101 102 103-- BOOLTBL1 should be full of true's at this point 104SELECT BOOLTBL1.* FROM BOOLTBL1; 105 106 107SELECT BOOLTBL1.* 108 FROM BOOLTBL1 109 WHERE f1 = bool 'true'; 110 111 112SELECT BOOLTBL1.* 113 FROM BOOLTBL1 114 WHERE f1 <> bool 'false'; 115 116SELECT BOOLTBL1.* 117 FROM BOOLTBL1 118 WHERE booleq(bool 'false', f1); 119 120INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); 121 122SELECT BOOLTBL1.* 123 FROM BOOLTBL1 124 WHERE f1 = bool 'false'; 125 126 127CREATE TABLE BOOLTBL2 (f1 bool); 128 129INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f'); 130 131INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false'); 132 133INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False'); 134 135INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE'); 136 137-- This is now an invalid expression 138-- For pre-v6.3 this evaluated to false - thomas 1997-10-23 139INSERT INTO BOOLTBL2 (f1) 140 VALUES (bool 'XXX'); 141 142-- BOOLTBL2 should be full of false's at this point 143SELECT BOOLTBL2.* FROM BOOLTBL2; 144 145 146SELECT BOOLTBL1.*, BOOLTBL2.* 147 FROM BOOLTBL1, BOOLTBL2 148 WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; 149 150 151SELECT BOOLTBL1.*, BOOLTBL2.* 152 FROM BOOLTBL1, BOOLTBL2 153 WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); 154 155 156SELECT BOOLTBL1.*, BOOLTBL2.* 157 FROM BOOLTBL1, BOOLTBL2 158 WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false'; 159 160 161SELECT BOOLTBL1.*, BOOLTBL2.* 162 FROM BOOLTBL1, BOOLTBL2 163 WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true' 164 ORDER BY BOOLTBL1.f1, BOOLTBL2.f1; 165 166-- 167-- SQL syntax 168-- Try all combinations to ensure that we get nothing when we expect nothing 169-- - thomas 2000-01-04 170-- 171 172SELECT f1 173 FROM BOOLTBL1 174 WHERE f1 IS TRUE; 175 176SELECT f1 177 FROM BOOLTBL1 178 WHERE f1 IS NOT FALSE; 179 180SELECT f1 181 FROM BOOLTBL1 182 WHERE f1 IS FALSE; 183 184SELECT f1 185 FROM BOOLTBL1 186 WHERE f1 IS NOT TRUE; 187 188SELECT f1 189 FROM BOOLTBL2 190 WHERE f1 IS TRUE; 191 192SELECT f1 193 FROM BOOLTBL2 194 WHERE f1 IS NOT FALSE; 195 196SELECT f1 197 FROM BOOLTBL2 198 WHERE f1 IS FALSE; 199 200SELECT f1 201 FROM BOOLTBL2 202 WHERE f1 IS NOT TRUE; 203 204-- 205-- Tests for BooleanTest 206-- 207CREATE TABLE BOOLTBL3 (d text, b bool, o int); 208INSERT INTO BOOLTBL3 (d, b, o) VALUES ('true', true, 1); 209INSERT INTO BOOLTBL3 (d, b, o) VALUES ('false', false, 2); 210INSERT INTO BOOLTBL3 (d, b, o) VALUES ('null', null, 3); 211 212SELECT 213 d, 214 b IS TRUE AS istrue, 215 b IS NOT TRUE AS isnottrue, 216 b IS FALSE AS isfalse, 217 b IS NOT FALSE AS isnotfalse, 218 b IS UNKNOWN AS isunknown, 219 b IS NOT UNKNOWN AS isnotunknown 220FROM booltbl3 ORDER BY o; 221 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. 226CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool); 227INSERT 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 232SELECT istrue AND isnul AND istrue FROM booltbl4; 233SELECT istrue AND istrue AND isnul FROM booltbl4; 234SELECT isnul AND istrue AND istrue FROM booltbl4; 235SELECT isfalse AND isnul AND istrue FROM booltbl4; 236SELECT istrue AND isfalse AND isnul FROM booltbl4; 237SELECT 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 241SELECT isfalse OR isnul OR isfalse FROM booltbl4; 242SELECT isfalse OR isfalse OR isnul FROM booltbl4; 243SELECT isnul OR isfalse OR isfalse FROM booltbl4; 244SELECT isfalse OR isnul OR istrue FROM booltbl4; 245SELECT istrue OR isfalse OR isnul FROM booltbl4; 246SELECT isnul OR istrue OR isfalse FROM booltbl4; 247 248 249-- 250-- Clean up 251-- Many tables are retained by the regression test, but these do not seem 252-- particularly useful so just get rid of them for now. 253-- - thomas 1997-11-30 254-- 255 256DROP TABLE BOOLTBL1; 257 258DROP TABLE BOOLTBL2; 259 260DROP TABLE BOOLTBL3; 261 262DROP TABLE BOOLTBL4; 263