The attached patch changes all implicit casts to text to assignment and
cleans up the associated regression test damage. This change has been
discussed for the longest time; I propose that we bite the bullet and
do it now.
The issue described in
<http://archives.postgresql.org/pgsql-hackers/2007-02/msg01729.php>
should also be fixed but can be considered separately later.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
diff -cr ../cvs-pgsql/src/include/catalog/pg_cast.h ./src/include/catalog/pg_cast.h
*** ../cvs-pgsql/src/include/catalog/pg_cast.h 2007-02-03 15:06:55.000000000 +0100
--- ./src/include/catalog/pg_cast.h 2007-04-01 22:26:13.000000000 +0200
***************
*** 264,304 ****
/*
* Cross-category casts to and from TEXT
- *
- * For historical reasons, most casts to TEXT are implicit. This is BAD
- * and should be reined in.
*/
! DATA(insert ( 20 25 1289 i ));
DATA(insert ( 25 20 1290 e ));
! DATA(insert ( 21 25 113 i ));
DATA(insert ( 25 21 818 e ));
! DATA(insert ( 23 25 112 i ));
DATA(insert ( 25 23 819 e ));
! DATA(insert ( 26 25 114 i ));
DATA(insert ( 25 26 817 e ));
DATA(insert ( 25 650 1714 e ));
! DATA(insert ( 700 25 841 i ));
DATA(insert ( 25 700 839 e ));
! DATA(insert ( 701 25 840 i ));
DATA(insert ( 25 701 838 e ));
DATA(insert ( 829 25 752 e ));
DATA(insert ( 25 829 767 e ));
DATA(insert ( 650 25 730 e ));
DATA(insert ( 869 25 730 e ));
DATA(insert ( 25 869 1713 e ));
! DATA(insert ( 1082 25 749 i ));
DATA(insert ( 25 1082 748 e ));
! DATA(insert ( 1083 25 948 i ));
DATA(insert ( 25 1083 837 e ));
! DATA(insert ( 1114 25 2034 i ));
DATA(insert ( 25 1114 2022 e ));
! DATA(insert ( 1184 25 1192 i ));
DATA(insert ( 25 1184 1191 e ));
! DATA(insert ( 1186 25 1193 i ));
DATA(insert ( 25 1186 1263 e ));
! DATA(insert ( 1266 25 939 i ));
DATA(insert ( 25 1266 938 e ));
! DATA(insert ( 1700 25 1688 i ));
DATA(insert ( 25 1700 1686 e ));
DATA(insert ( 142 25 2922 e ));
DATA(insert ( 25 142 2896 e ));
--- 264,301 ----
/*
* Cross-category casts to and from TEXT
*/
! DATA(insert ( 20 25 1289 a ));
DATA(insert ( 25 20 1290 e ));
! DATA(insert ( 21 25 113 a ));
DATA(insert ( 25 21 818 e ));
! DATA(insert ( 23 25 112 a ));
DATA(insert ( 25 23 819 e ));
! DATA(insert ( 26 25 114 a ));
DATA(insert ( 25 26 817 e ));
DATA(insert ( 25 650 1714 e ));
! DATA(insert ( 700 25 841 a ));
DATA(insert ( 25 700 839 e ));
! DATA(insert ( 701 25 840 a ));
DATA(insert ( 25 701 838 e ));
DATA(insert ( 829 25 752 e ));
DATA(insert ( 25 829 767 e ));
DATA(insert ( 650 25 730 e ));
DATA(insert ( 869 25 730 e ));
DATA(insert ( 25 869 1713 e ));
! DATA(insert ( 1082 25 749 a ));
DATA(insert ( 25 1082 748 e ));
! DATA(insert ( 1083 25 948 a ));
DATA(insert ( 25 1083 837 e ));
! DATA(insert ( 1114 25 2034 a ));
DATA(insert ( 25 1114 2022 e ));
! DATA(insert ( 1184 25 1192 a ));
DATA(insert ( 25 1184 1191 e ));
! DATA(insert ( 1186 25 1193 a ));
DATA(insert ( 25 1186 1263 e ));
! DATA(insert ( 1266 25 939 a ));
DATA(insert ( 25 1266 938 e ));
! DATA(insert ( 1700 25 1688 a ));
DATA(insert ( 25 1700 1686 e ));
DATA(insert ( 142 25 2922 e ));
DATA(insert ( 25 142 2896 e ));
***************
*** 306,312 ****
/*
* Cross-category casts to and from VARCHAR
*
! * We support all the same casts as for TEXT, but none are implicit.
*/
DATA(insert ( 20 1043 1289 a ));
DATA(insert ( 1043 20 1290 e ));
--- 303,309 ----
/*
* Cross-category casts to and from VARCHAR
*
! * We support all the same casts as for TEXT.
*/
DATA(insert ( 20 1043 1289 a ));
DATA(insert ( 1043 20 1290 e ));
diff -cr ../cvs-pgsql/src/test/regress/expected/foreign_key.out ./src/test/regress/expected/foreign_key.out
*** ../cvs-pgsql/src/test/regress/expected/foreign_key.out 2007-02-14 19:35:53.000000000 +0100
--- ./src/test/regress/expected/foreign_key.out 2007-04-01 22:45:19.000000000 +0200
***************
*** 1125,1134 ****
FOREIGN KEY (x3) REFERENCES pktable(id1);
ERROR: foreign key constraint "fk_3_1" cannot be implemented
DETAIL: Key columns "x3" and "id1" are of incompatible types: real and integer.
! -- should succeed
! -- int4 promotes to text, so this is allowed (though pretty durn debatable)
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
--- 1125,1136 ----
FOREIGN KEY (x3) REFERENCES pktable(id1);
ERROR: foreign key constraint "fk_3_1" cannot be implemented
DETAIL: Key columns "x3" and "id1" are of incompatible types: real and integer.
! -- int4 does not promote to text
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
+ ERROR: foreign key constraint "fk_1_2" cannot be implemented
+ DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying.
+ -- should succeed
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
***************
*** 1150,1156 ****
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
ERROR: foreign key constraint "fk_123_231" cannot be implemented
! DETAIL: Key columns "x2" and "id3" are of incompatible types: character varying and real.
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
ERROR: foreign key constraint "fk_241_132" cannot be implemented
--- 1152,1158 ----
ALTER TABLE fktable ADD CONSTRAINT fk_123_231
FOREIGN KEY (x1,x2,x3) REFERENCES pktable(id2,id3,id1);
ERROR: foreign key constraint "fk_123_231" cannot be implemented
! DETAIL: Key columns "x1" and "id2" are of incompatible types: integer and character varying.
ALTER TABLE fktable ADD CONSTRAINT fk_241_132
FOREIGN KEY (x2,x4,x1) REFERENCES pktable(id1,id3,id2);
ERROR: foreign key constraint "fk_241_132" cannot be implemented
***************
*** 1162,1168 ****
NOTICE: drop cascades to constraint fk_5_1 on table fktable
NOTICE: drop cascades to constraint fktable_x1_fkey on table fktable
NOTICE: drop cascades to constraint fk_4_2 on table fktable
- NOTICE: drop cascades to constraint fk_1_2 on table fktable
NOTICE: drop cascades to constraint fktable_x2_fkey on table fktable
NOTICE: drop cascades to constraint fk_1_3 on table fktable
NOTICE: drop cascades to constraint fktable_x3_fkey on table fktable
--- 1164,1169 ----
diff -cr ../cvs-pgsql/src/test/regress/expected/strings.out ./src/test/regress/expected/strings.out
*** ../cvs-pgsql/src/test/regress/expected/strings.out 2007-03-25 17:24:15.000000000 +0200
--- ./src/test/regress/expected/strings.out 2007-04-01 22:34:16.000000000 +0200
***************
*** 450,456 ****
t
(1 row)
! SELECT POSITION(5 IN '1234567890') = '5' AS "5";
5
---
t
--- 450,456 ----
t
(1 row)
! SELECT POSITION('5' IN '1234567890') = '5' AS "5";
5
---
t
diff -cr ../cvs-pgsql/src/test/regress/expected/subselect.out ./src/test/regress/expected/subselect.out
*** ../cvs-pgsql/src/test/regress/expected/subselect.out 2005-12-03 22:30:52.000000000 +0100
--- ./src/test/regress/expected/subselect.out 2007-04-01 22:40:52.000000000 +0200
***************
*** 354,360 ****
insert into shipped values('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)
! values (0, 1, (select cost from parts where partnum = 1));
select * from shipped_view;
ttype | ordnum | partnum | value
-------+--------+---------+---------
--- 354,360 ----
insert into shipped values('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)
! values (0, 1, (select cost from parts where partnum = '1'));
select * from shipped_view;
ttype | ordnum | partnum | value
-------+--------+---------+---------
diff -cr ../cvs-pgsql/src/test/regress/sql/foreign_key.sql ./src/test/regress/sql/foreign_key.sql
*** ../cvs-pgsql/src/test/regress/sql/foreign_key.sql 2007-02-14 19:35:53.000000000 +0100
--- ./src/test/regress/sql/foreign_key.sql 2007-04-01 22:42:58.000000000 +0200
***************
*** 760,771 ****
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
FOREIGN KEY (x3) REFERENCES pktable(id1);
! -- should succeed
!
! -- int4 promotes to text, so this is allowed (though pretty durn debatable)
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
--- 760,771 ----
ALTER TABLE fktable ADD CONSTRAINT fk_3_1
FOREIGN KEY (x3) REFERENCES pktable(id1);
! -- int4 does not promote to text
ALTER TABLE fktable ADD CONSTRAINT fk_1_2
FOREIGN KEY (x1) REFERENCES pktable(id2);
+ -- should succeed
+
-- int4 promotes to real
ALTER TABLE fktable ADD CONSTRAINT fk_1_3
FOREIGN KEY (x1) REFERENCES pktable(id3);
diff -cr ../cvs-pgsql/src/test/regress/sql/strings.sql ./src/test/regress/sql/strings.sql
*** ../cvs-pgsql/src/test/regress/sql/strings.sql 2007-03-25 17:24:17.000000000 +0200
--- ./src/test/regress/sql/strings.sql 2007-04-01 22:32:23.000000000 +0200
***************
*** 142,148 ****
-- E021-11 position expression
SELECT POSITION('4' IN '1234567890') = '4' AS "4";
! SELECT POSITION(5 IN '1234567890') = '5' AS "5";
-- T312 character overlay function
SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f";
--- 142,148 ----
-- E021-11 position expression
SELECT POSITION('4' IN '1234567890') = '4' AS "4";
! SELECT POSITION('5' IN '1234567890') = '5' AS "5";
-- T312 character overlay function
SELECT OVERLAY('abcdef' PLACING '45' FROM 4) AS "abc45f";
diff -cr ../cvs-pgsql/src/test/regress/sql/subselect.sql ./src/test/regress/sql/subselect.sql
*** ../cvs-pgsql/src/test/regress/sql/subselect.sql 2005-12-03 22:30:52.000000000 +0100
--- ./src/test/regress/sql/subselect.sql 2007-04-01 22:39:05.000000000 +0200
***************
*** 218,224 ****
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)
! values (0, 1, (select cost from parts where partnum = 1));
select * from shipped_view;
--- 218,224 ----
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)
! values (0, 1, (select cost from parts where partnum = '1'));
select * from shipped_view;
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
