Clean up syntax to use SQL92-ish type coersion

 rather than the Postgres "::" notation.
All of these tests have been completely inspected and give correct results.

11 files changed, 124 insertions, 99 deletions

diff --git a/src/test/regress/sql/boolean.sql b/src/test/regress/sql/boolean.sql
index 4f1cef2de40..86d4bb428ce 100644
--- a/src/test/regress/sql/boolean.sql
+++ b/src/test/regress/sql/boolean.sql
@@ -1,7 +1,5 @@
 --
--- boolean.source
---
--- $Header: /cvsroot/pgsql/src/test/regress/sql/boolean.sql,v 1.5 1997/12/01 02:45:59 thomas Exp $
+-- BOOLEAN
 --
 
 --
@@ -14,28 +12,28 @@ SELECT 1 AS one;
 
 -- check bool type-casting as well as and, or, not in qualifications--
 
-SELECT 't'::bool AS true;
+SELECT bool 't' AS true;
 
-SELECT 'f'::bool AS false;
+SELECT bool 'f' AS false;
 
-SELECT 't'::bool or 'f'::bool AS true;
+SELECT bool 't' or bool 'f' AS true;
 
-SELECT 't'::bool and 'f'::bool AS false;
+SELECT bool 't' and bool 'f' AS false;
 
-SELECT not 'f'::bool AS true;
+SELECT not bool 'f' AS true;
 
-SELECT 't'::bool = 'f'::bool AS false;
+SELECT bool 't' = bool 'f' AS false;
 
-SELECT 't'::bool <> 'f'::bool AS true;
+SELECT bool 't' <> bool 'f' AS true;
 
 
 CREATE TABLE BOOLTBL1 (f1 bool);
 
-INSERT INTO BOOLTBL1 (f1) VALUES ('t'::bool);
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 't');
 
-INSERT INTO BOOLTBL1 (f1) VALUES ('True'::bool);
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'True');
 
-INSERT INTO BOOLTBL1 (f1) VALUES ('true'::bool);
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'true');
 
 
 -- BOOLTBL1 should be full of true's at this point 
@@ -44,38 +42,38 @@ SELECT '' AS t_3, BOOLTBL1.*;
 
 SELECT '' AS t_3, BOOLTBL1.*
    FROM BOOLTBL1
-   WHERE f1 = 'true'::bool;
+   WHERE f1 = bool 'true';
 
 
 SELECT '' AS t_3, BOOLTBL1.* 
    FROM BOOLTBL1
-   WHERE f1 <> 'false'::bool;
+   WHERE f1 <> bool 'false';
 
 SELECT '' AS zero, BOOLTBL1.*
    FROM BOOLTBL1
-   WHERE booleq('false'::bool, f1);
+   WHERE booleq(bool 'false', f1);
 
-INSERT INTO BOOLTBL1 (f1) VALUES ('f'::bool);
+INSERT INTO BOOLTBL1 (f1) VALUES (bool 'f');
 
 SELECT '' AS f_1, BOOLTBL1.* 
    FROM BOOLTBL1
-   WHERE f1 = 'false'::bool;
+   WHERE f1 = bool 'false';
 
 
 CREATE TABLE BOOLTBL2 (f1 bool);
 
-INSERT INTO BOOLTBL2 (f1) VALUES ('f'::bool);
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'f');
 
-INSERT INTO BOOLTBL2 (f1) VALUES ('false'::bool);
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'false');
 
-INSERT INTO BOOLTBL2 (f1) VALUES ('False'::bool);
+INSERT INTO BOOLTBL2 (f1) VALUES (bool 'False');
 
-INSERT INTO BOOLTBL2 (f1) VALUES ('FALSE'::bool);
+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 ('XXX'::bool);  
+   VALUES (bool 'XXX');  
 
 -- BOOLTBL2 should be full of false's at this point 
 SELECT '' AS f_4, BOOLTBL2.*;
@@ -90,33 +88,51 @@ SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
 
 
 SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.*
-   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = 'false'::bool;
+   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = bool 'false';
 
 
 SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.*
-   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = 'true'::bool
+   WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = bool 'true'
    ORDER BY BOOLTBL1.f1, BOOLTBL2.f1;
 
 --
--- SQL92 syntax - thomas 1997-11-30
+-- SQL92 syntax
+-- Try all combinations to ensure that we get nothing when we expect nothing
+-- - thomas 2000-01-04
 --
 
-SELECT '' AS "True", BOOLTBL1.* 
+SELECT '' AS "True", f1
    FROM BOOLTBL1
    WHERE f1 IS TRUE;
 
-SELECT '' AS "Not False", BOOLTBL1.* 
+SELECT '' AS "Not False", f1
    FROM BOOLTBL1
    WHERE f1 IS NOT FALSE;
 
-SELECT '' AS "False", BOOLTBL1.* 
+SELECT '' AS "False", f1
    FROM BOOLTBL1
    WHERE f1 IS FALSE;
 
-SELECT '' AS "Not True", BOOLTBL1.* 
+SELECT '' AS "Not True", f1
    FROM BOOLTBL1
    WHERE f1 IS NOT TRUE;
 
+SELECT '' AS "True", f1
+   FROM BOOLTBL2
+   WHERE f1 IS TRUE;
+
+SELECT '' AS "Not False", f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT FALSE;
+
+SELECT '' AS "False", f1
+   FROM BOOLTBL2
+   WHERE f1 IS FALSE;
+
+SELECT '' AS "Not True", f1
+   FROM BOOLTBL2
+   WHERE f1 IS NOT TRUE;
+
 --
 -- Clean up
 -- Many tables are retained by the regression test, but these do not seem
diff --git a/src/test/regress/sql/char.sql b/src/test/regress/sql/char.sql
index 49a13a0fb94..ba7167fc659 100644
--- a/src/test/regress/sql/char.sql
+++ b/src/test/regress/sql/char.sql
@@ -1,14 +1,12 @@
---  ****************** test built-in type char **************
 --
+-- CHAR
 -- all inputs are SILENTLY truncated at 1 character
 --
 
 -- fixed-length by value
 -- internally passed by value if <= 4 bytes in storage
--- Not sure why this is a really useful test,
---  but this test has been here forever. - thomas 1997-11-30
 
-SELECT 'c'::char = 'c'::char AS true;
+SELECT char 'c' = char 'c' AS true;
 
 --
 -- Build a table for testing
diff --git a/src/test/regress/sql/float4.sql b/src/test/regress/sql/float4.sql
index 1bf1308bd1a..28c12c6fa45 100644
--- a/src/test/regress/sql/float4.sql
+++ b/src/test/regress/sql/float4.sql
@@ -1,4 +1,6 @@
---  *************testing built-in type float4 ****************
+--
+-- FLOAT4
+--
 
 CREATE TABLE FLOAT4_TBL (f1  float4);
 
diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql
index bc41a610859..8fc7a7baf42 100644
--- a/src/test/regress/sql/float8.sql
+++ b/src/test/regress/sql/float8.sql
@@ -1,4 +1,6 @@
---  *************testing built-in type float8 ****************
+--
+-- FLOAT8
+--
 
 CREATE TABLE FLOAT8_TBL(f1 float8);
 
diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql
index a6b71b3efe2..56cbb4c7c9c 100644
--- a/src/test/regress/sql/int2.sql
+++ b/src/test/regress/sql/int2.sql
@@ -1,5 +1,5 @@
--- *************testing built-in type int2 ****************
 --
+-- INT2
 -- NOTE: int2 operators never check for over/underflow!
 -- Some of these answers are consequently numerically incorrect.
 --
@@ -27,49 +27,49 @@ INSERT INTO INT2_TBL(f1) VALUES ('asdf');
 
 SELECT '' AS five, INT2_TBL.*;
 
-SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int2;
+SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0';
 
-SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int4;
+SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0';
 
-SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int2;
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int2 '0';
 
-SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int4;
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = int4 '0';
 
-SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int2;
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int2 '0';
 
-SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int4;
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < int4 '0';
 
-SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int2;
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0';
 
-SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int4;
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0';
 
-SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int2;
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int2 '0';
 
-SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int4;
+SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > int4 '0';
 
-SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int2;
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0';
 
-SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int4;
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0';
 
 -- positive odds 
-SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2;
+SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
 
 -- any evens 
-SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2;
+SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
 
-SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT2_TBL i;
+SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i;
 
diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql
index 6a32ee88d4d..96e947eebaa 100644
--- a/src/test/regress/sql/int4.sql
+++ b/src/test/regress/sql/int4.sql
@@ -1,5 +1,5 @@
---  *************testing built-in type int4 ****************
 --
+-- INT4
 -- WARNING: int4 operators never check for over/underflow!
 -- Some of these answers are consequently numerically incorrect.
 --
@@ -27,51 +27,51 @@ INSERT INTO INT4_TBL(f1) VALUES ('asdf');
 
 SELECT '' AS five, INT4_TBL.*;
 
-SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int2;
+SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0';
 
-SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> '0'::int4;
+SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0';
 
-SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int2;
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0';
 
-SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = '0'::int4;
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0';
 
-SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int2;
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0';
 
-SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < '0'::int4;
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0';
 
-SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int2;
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0';
 
-SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= '0'::int4;
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0';
 
-SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int2;
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0';
 
-SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > '0'::int4;
+SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0';
 
-SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int2;
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0';
 
-SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= '0'::int4;
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
 
 -- positive odds 
-SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2;
+SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
 
 -- any evens 
-SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2;
+SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
 
-SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i;
 
-SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT4_TBL i;
+SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i;
 
 --
 -- more complex expressions
@@ -86,13 +86,13 @@ SELECT 2- -1 AS three;
 
 SELECT 2 - -2 AS four;
 
-SELECT '2'::int2 * '2'::int2 = '16'::int2 / '4'::int2 AS true;
+SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true;
 
-SELECT '2'::int4 * '2'::int2 = '16'::int2 / '4'::int4 AS true;
+SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true;
 
-SELECT '2'::int2 * '2'::int4 = '16'::int4 / '4'::int2 AS true;
+SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true;
 
-SELECT '1000'::int4 < '999'::int4 AS false;
+SELECT int4 '1000' < int4 '999' AS false;
 
 SELECT 4! AS twenty_four;
 
@@ -104,9 +104,9 @@ SELECT 2 + 2 / 2 AS three;
 
 SELECT (2 + 2) / 2 AS two;
 
-SELECT dsqrt('64'::float8) AS eight;
+SELECT dsqrt(float8 '64') AS eight;
 
-SELECT |/'64'::float8 AS eight;
+SELECT |/float8 '64' AS eight;
 
-SELECT ||/'27'::float8 AS three;
+SELECT ||/float8 '27' AS three;
 
diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql
index 1b997e9ed81..01de557d602 100644
--- a/src/test/regress/sql/int8.sql
+++ b/src/test/regress/sql/int8.sql
@@ -1,4 +1,5 @@
 --
+-- INT8
 -- Test int8 64-bit integers.
 --
 CREATE TABLE INT8_TBL(q1 int8, q2 int8);
diff --git a/src/test/regress/sql/name.sql b/src/test/regress/sql/name.sql
index 11235caaa3d..882364fba8a 100644
--- a/src/test/regress/sql/name.sql
+++ b/src/test/regress/sql/name.sql
@@ -1,12 +1,12 @@
---**************** testing built-in type name **************
 --
+-- NAME
 -- all inputs are silently truncated at NAMEDATALEN (32) characters
 --
 
 -- fixed-length by reference
-SELECT 'name string'::name = 'name string'::name AS "True";
+SELECT name 'name string' = name 'name string' AS "True";
 
-SELECT 'name string'::name = 'name string '::name AS "False";
+SELECT name 'name string' = name 'name string ' AS "False";
 
 --
 --
diff --git a/src/test/regress/sql/oid.sql b/src/test/regress/sql/oid.sql
index ae337e05fa6..a33cfaca4fa 100644
--- a/src/test/regress/sql/oid.sql
+++ b/src/test/regress/sql/oid.sql
@@ -1,4 +1,7 @@
--- *************testing built-in type oid ****************
+--
+-- OID
+--
+
 CREATE TABLE OID_TBL(f1 oid);
 
 INSERT INTO OID_TBL(f1) VALUES ('1234');
@@ -17,7 +20,7 @@ INSERT INTO OID_TBL(f1) VALUES ('asdfasd');
 SELECT '' AS five, OID_TBL.*;
 
 
-SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = '1234'::oid;
+SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 = oid '1234';
 
 SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 <> '1234';
 
@@ -29,4 +32,4 @@ SELECT '' AS two, o.* FROM OID_TBL o WHERE o.f1 >= '1234';
 
 SELECT '' AS one, o.* FROM OID_TBL o WHERE o.f1 > '1234';
 
-DROP TABLE  OID_TBL;
+DROP TABLE OID_TBL;
diff --git a/src/test/regress/sql/text.sql b/src/test/regress/sql/text.sql
index fb9daf4fd56..60daf7077c6 100644
--- a/src/test/regress/sql/text.sql
+++ b/src/test/regress/sql/text.sql
@@ -1,8 +1,10 @@
--- *************testing built-in type text  ****************
+--
+-- TEXT
+--
 
-SELECT 'this is a text string'::text = 'this is a text string'::text AS true;
+SELECT text 'this is a text string' = text 'this is a text string' AS true;
 
-SELECT 'this is a text string'::text = 'this is a text strin'::text AS false;
+SELECT text 'this is a text string' = text 'this is a text strin' AS false;
 
 CREATE TABLE TEXT_TBL (f1 text);
 
diff --git a/src/test/regress/sql/varchar.sql b/src/test/regress/sql/varchar.sql
index ead5da6b262..e28c8f9b932 100644
--- a/src/test/regress/sql/varchar.sql
+++ b/src/test/regress/sql/varchar.sql
@@ -1,4 +1,5 @@
---  ****************** test built-in type varchar **************
+--
+-- VARCHAR
 --
 
 CREATE TABLE VARCHAR_TBL(f1 varchar(1));