1-- 2-- BOOLEAN 3-- 4-- 5-- sanity check - if this fails go insane! 6-- 7SELECT 1 AS one; 8 one 9----- 10 1 11(1 row) 12 13-- ******************testing built-in type bool******************** 14-- check bool input syntax 15SELECT true AS true; 16 true 17------ 18 t 19(1 row) 20 21SELECT false AS false; 22 false 23------- 24 f 25(1 row) 26 27SELECT bool 't' AS true; 28 true 29------ 30 t 31(1 row) 32 33SELECT bool ' f ' AS false; 34 false 35------- 36 f 37(1 row) 38 39SELECT bool 'true' AS true; 40 true 41------ 42 t 43(1 row) 44 45SELECT bool 'test' AS error; 46ERROR: invalid input syntax for type boolean: "test" 47LINE 1: SELECT bool 'test' AS error; 48 ^ 49SELECT bool 'false' AS false; 50 false 51------- 52 f 53(1 row) 54 55SELECT bool 'foo' AS error; 56ERROR: invalid input syntax for type boolean: "foo" 57LINE 1: SELECT bool 'foo' AS error; 58 ^ 59SELECT bool 'y' AS true; 60 true 61------ 62 t 63(1 row) 64 65SELECT bool 'yes' AS true; 66 true 67------ 68 t 69(1 row) 70 71SELECT bool 'yeah' AS error; 72ERROR: invalid input syntax for type boolean: "yeah" 73LINE 1: SELECT bool 'yeah' AS error; 74 ^ 75SELECT bool 'n' AS false; 76 false 77------- 78 f 79(1 row) 80 81SELECT bool 'no' AS false; 82 false 83------- 84 f 85(1 row) 86 87SELECT bool 'nay' AS error; 88ERROR: invalid input syntax for type boolean: "nay" 89LINE 1: SELECT bool 'nay' AS error; 90 ^ 91SELECT bool 'on' AS true; 92 true 93------ 94 t 95(1 row) 96 97SELECT bool 'off' AS false; 98 false 99------- 100 f 101(1 row) 102 103SELECT bool 'of' AS false; 104 false 105------- 106 f 107(1 row) 108 109SELECT bool 'o' AS error; 110ERROR: invalid input syntax for type boolean: "o" 111LINE 1: SELECT bool 'o' AS error; 112 ^ 113SELECT bool 'on_' AS error; 114ERROR: invalid input syntax for type boolean: "on_" 115LINE 1: SELECT bool 'on_' AS error; 116 ^ 117SELECT bool 'off_' AS error; 118ERROR: invalid input syntax for type boolean: "off_" 119LINE 1: SELECT bool 'off_' AS error; 120 ^ 121SELECT bool '1' AS true; 122 true 123------ 124 t 125(1 row) 126 127SELECT bool '11' AS error; 128ERROR: invalid input syntax for type boolean: "11" 129LINE 1: SELECT bool '11' AS error; 130 ^ 131SELECT bool '0' AS false; 132 false 133------- 134 f 135(1 row) 136 137SELECT bool '000' AS error; 138ERROR: invalid input syntax for type boolean: "000" 139LINE 1: SELECT bool '000' AS error; 140 ^ 141SELECT bool '' AS error; 142ERROR: invalid input syntax for type boolean: "" 143LINE 1: SELECT bool '' AS error; 144 ^ 145-- and, or, not in qualifications 146SELECT bool 't' or bool 'f' AS true; 147 true 148------ 149 t 150(1 row) 151 152SELECT bool 't' and bool 'f' AS false; 153 false 154------- 155 f 156(1 row) 157 158SELECT not bool 'f' AS true; 159 true 160------ 161 t 162(1 row) 163 164SELECT bool 't' = bool 'f' AS false; 165 false 166------- 167 f 168(1 row) 169 170SELECT bool 't' <> bool 'f' AS true; 171 true 172------ 173 t 174(1 row) 175 176SELECT bool 't' > bool 'f' AS true; 177 true 178------ 179 t 180(1 row) 181 182SELECT bool 't' >= bool 'f' AS true; 183 true 184------ 185 t 186(1 row) 187 188SELECT bool 'f' < bool 't' AS true; 189 true 190------ 191 t 192(1 row) 193 194SELECT bool 'f' <= bool 't' AS true; 195 true 196------ 197 t 198(1 row) 199 200-- explicit casts to/from text 201SELECT 'TrUe'::text::boolean AS true, 'fAlse'::text::boolean AS false; 202 true | false 203------+------- 204 t | f 205(1 row) 206 207SELECT ' true '::text::boolean AS true, 208 ' FALSE'::text::boolean AS false; 209 true | false 210------+------- 211 t | f 212(1 row) 213 214SELECT true::boolean::text AS true, false::boolean::text AS false; 215 true | false 216------+------- 217 true | false 218(1 row) 219 220SELECT ' tru e '::text::boolean AS invalid; -- error 221ERROR: invalid input syntax for type boolean: " tru e " 222SELECT ''::text::boolean AS invalid; -- error 223ERROR: invalid input syntax for type boolean: "" 224CREATE TABLE BOOLTBL1 (f1 bool); 225INSERT INTO BOOLTBL1 (f1) VALUES (bool 't'); 226INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True'); 227INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true'); 228-- BOOLTBL1 should be full of true's at this point 229SELECT BOOLTBL1.* FROM BOOLTBL1; 230 f1 231---- 232 t 233 t 234 t 235(3 rows) 236 237SELECT BOOLTBL1.* 238 FROM BOOLTBL1 239 WHERE f1 = bool 'true'; 240 f1 241---- 242 t 243 t 244 t 245(3 rows) 246 247SELECT BOOLTBL1.* 248 FROM BOOLTBL1 249 WHERE f1 <> bool 'false'; 250 f1 251---- 252 t 253 t 254 t 255(3 rows) 256 257SELECT BOOLTBL1.* 258 FROM BOOLTBL1 259 WHERE booleq(bool 'false', f1); 260 f1 261---- 262(0 rows) 263 264INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f'); 265SELECT BOOLTBL1.* 266 FROM BOOLTBL1 267 WHERE f1 = bool 'false'; 268 f1 269---- 270 f 271(1 row) 272 273CREATE TABLE BOOLTBL2 (f1 bool); 274INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f'); 275INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false'); 276INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False'); 277INSERT INTO BOOLTBL2 (f1) VALUES (bool 'FALSE'); 278-- This is now an invalid expression 279-- For pre-v6.3 this evaluated to false - thomas 1997-10-23 280INSERT INTO BOOLTBL2 (f1) 281 VALUES (bool 'XXX'); 282ERROR: invalid input syntax for type boolean: "XXX" 283LINE 2: VALUES (bool 'XXX'); 284 ^ 285-- BOOLTBL2 should be full of false's at this point 286SELECT BOOLTBL2.* FROM BOOLTBL2; 287 f1 288---- 289 f 290 f 291 f 292 f 293(4 rows) 294 295SELECT BOOLTBL1.*, BOOLTBL2.* 296 FROM BOOLTBL1, BOOLTBL2 297 WHERE BOOLTBL2.f1 <> BOOLTBL1.f1; 298 f1 | f1 299----+---- 300 t | f 301 t | f 302 t | f 303 t | f 304 t | f 305 t | f 306 t | f 307 t | f 308 t | f 309 t | f 310 t | f 311 t | f 312(12 rows) 313 314SELECT BOOLTBL1.*, BOOLTBL2.* 315 FROM BOOLTBL1, BOOLTBL2 316 WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1); 317 f1 | f1 318----+---- 319 t | f 320 t | f 321 t | f 322 t | f 323 t | f 324 t | f 325 t | f 326 t | f 327 t | f 328 t | f 329 t | f 330 t | f 331(12 rows) 332 333SELECT BOOLTBL1.*, BOOLTBL2.* 334 FROM BOOLTBL1, BOOLTBL2 335 WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false'; 336 f1 | f1 337----+---- 338 f | f 339 f | f 340 f | f 341 f | f 342(4 rows) 343 344SELECT BOOLTBL1.*, BOOLTBL2.* 345 FROM BOOLTBL1, BOOLTBL2 346 WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true' 347 ORDER BY BOOLTBL1.f1, BOOLTBL2.f1; 348 f1 | f1 349----+---- 350 f | f 351 f | f 352 f | f 353 f | f 354 t | f 355 t | f 356 t | f 357 t | f 358 t | f 359 t | f 360 t | f 361 t | f 362 t | f 363 t | f 364 t | f 365 t | f 366(16 rows) 367 368-- 369-- SQL syntax 370-- Try all combinations to ensure that we get nothing when we expect nothing 371-- - thomas 2000-01-04 372-- 373SELECT f1 374 FROM BOOLTBL1 375 WHERE f1 IS TRUE; 376 f1 377---- 378 t 379 t 380 t 381(3 rows) 382 383SELECT f1 384 FROM BOOLTBL1 385 WHERE f1 IS NOT FALSE; 386 f1 387---- 388 t 389 t 390 t 391(3 rows) 392 393SELECT f1 394 FROM BOOLTBL1 395 WHERE f1 IS FALSE; 396 f1 397---- 398 f 399(1 row) 400 401SELECT f1 402 FROM BOOLTBL1 403 WHERE f1 IS NOT TRUE; 404 f1 405---- 406 f 407(1 row) 408 409SELECT f1 410 FROM BOOLTBL2 411 WHERE f1 IS TRUE; 412 f1 413---- 414(0 rows) 415 416SELECT f1 417 FROM BOOLTBL2 418 WHERE f1 IS NOT FALSE; 419 f1 420---- 421(0 rows) 422 423SELECT f1 424 FROM BOOLTBL2 425 WHERE f1 IS FALSE; 426 f1 427---- 428 f 429 f 430 f 431 f 432(4 rows) 433 434SELECT f1 435 FROM BOOLTBL2 436 WHERE f1 IS NOT TRUE; 437 f1 438---- 439 f 440 f 441 f 442 f 443(4 rows) 444 445-- 446-- Tests for BooleanTest 447-- 448CREATE TABLE BOOLTBL3 (d text, b bool, o int); 449INSERT INTO BOOLTBL3 (d, b, o) VALUES ('true', true, 1); 450INSERT INTO BOOLTBL3 (d, b, o) VALUES ('false', false, 2); 451INSERT INTO BOOLTBL3 (d, b, o) VALUES ('null', null, 3); 452SELECT 453 d, 454 b IS TRUE AS istrue, 455 b IS NOT TRUE AS isnottrue, 456 b IS FALSE AS isfalse, 457 b IS NOT FALSE AS isnotfalse, 458 b IS UNKNOWN AS isunknown, 459 b IS NOT UNKNOWN AS isnotunknown 460FROM booltbl3 ORDER BY o; 461 d | istrue | isnottrue | isfalse | isnotfalse | isunknown | isnotunknown 462-------+--------+-----------+---------+------------+-----------+-------------- 463 true | t | f | f | t | f | t 464 false | f | t | t | f | f | t 465 null | f | t | f | t | t | f 466(3 rows) 467 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. 471CREATE TABLE booltbl4(isfalse bool, istrue bool, isnul bool); 472INSERT 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 476SELECT istrue AND isnul AND istrue FROM booltbl4; 477 ?column? 478---------- 479 (null) 480(1 row) 481 482SELECT istrue AND istrue AND isnul FROM booltbl4; 483 ?column? 484---------- 485 (null) 486(1 row) 487 488SELECT isnul AND istrue AND istrue FROM booltbl4; 489 ?column? 490---------- 491 (null) 492(1 row) 493 494SELECT isfalse AND isnul AND istrue FROM booltbl4; 495 ?column? 496---------- 497 f 498(1 row) 499 500SELECT istrue AND isfalse AND isnul FROM booltbl4; 501 ?column? 502---------- 503 f 504(1 row) 505 506SELECT 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 514SELECT isfalse OR isnul OR isfalse FROM booltbl4; 515 ?column? 516---------- 517 (null) 518(1 row) 519 520SELECT isfalse OR isfalse OR isnul FROM booltbl4; 521 ?column? 522---------- 523 (null) 524(1 row) 525 526SELECT isnul OR isfalse OR isfalse FROM booltbl4; 527 ?column? 528---------- 529 (null) 530(1 row) 531 532SELECT isfalse OR isnul OR istrue FROM booltbl4; 533 ?column? 534---------- 535 t 536(1 row) 537 538SELECT istrue OR isfalse OR isnul FROM booltbl4; 539 ?column? 540---------- 541 t 542(1 row) 543 544SELECT isnul OR istrue OR isfalse FROM booltbl4; 545 ?column? 546---------- 547 t 548(1 row) 549 550-- 551-- Clean up 552-- Many tables are retained by the regression test, but these do not seem 553-- particularly useful so just get rid of them for now. 554-- - thomas 1997-11-30 555-- 556DROP TABLE BOOLTBL1; 557DROP TABLE BOOLTBL2; 558DROP TABLE BOOLTBL3; 559DROP TABLE BOOLTBL4; 560