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 '' AS t_3, BOOLTBL1.* FROM BOOLTBL1; 105 106 107SELECT '' AS t_3, BOOLTBL1.* 108 FROM BOOLTBL1 109 WHERE f1 = bool 'true'; 110 111 112SELECT '' AS t_3, BOOLTBL1.* 113 FROM BOOLTBL1 114 WHERE f1 <> bool 'false'; 115 116SELECT '' AS zero, BOOLTBL1.* 117 FROM BOOLTBL1 118 WHERE booleq(bool 'false', f1); 119 120INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); 121 122SELECT '' AS f_1, 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 '' AS f_4, BOOLTBL2.* FROM BOOLTBL2; 144 145 146SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* 147 FROM BOOLTBL1, BOOLTBL2 148 WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; 149 150 151SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.* 152 FROM BOOLTBL1, BOOLTBL2 153 WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); 154 155 156SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.* 157 FROM BOOLTBL1, BOOLTBL2 158 WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false'; 159 160 161SELECT '' AS tf_12_ff_4, 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 '' AS "True", f1 173 FROM BOOLTBL1 174 WHERE f1 IS TRUE; 175 176SELECT '' AS "Not False", f1 177 FROM BOOLTBL1 178 WHERE f1 IS NOT FALSE; 179 180SELECT '' AS "False", f1 181 FROM BOOLTBL1 182 WHERE f1 IS FALSE; 183 184SELECT '' AS "Not True", f1 185 FROM BOOLTBL1 186 WHERE f1 IS NOT TRUE; 187 188SELECT '' AS "True", f1 189 FROM BOOLTBL2 190 WHERE f1 IS TRUE; 191 192SELECT '' AS "Not False", f1 193 FROM BOOLTBL2 194 WHERE f1 IS NOT FALSE; 195 196SELECT '' AS "False", f1 197 FROM BOOLTBL2 198 WHERE f1 IS FALSE; 199 200SELECT '' AS "Not True", 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-- Clean up 224-- Many tables are retained by the regression test, but these do not seem 225-- particularly useful so just get rid of them for now. 226-- - thomas 1997-11-30 227-- 228 229DROP TABLE BOOLTBL1; 230 231DROP TABLE BOOLTBL2; 232 233DROP TABLE BOOLTBL3; 234