Implicit casts to text

Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Text Size: Normal / Large
Donate
Contact

Header And Logo

The world's most advanced open source database.

Site Navigation

Implicit casts to text

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Implicit casts to text
Date: Sun, 1 Apr 2007 23:29:32 +0200
Message-id: <200704012329.33450.peter_e@gmx.net> <text/plain>

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;

Follow-Ups:
- Re: Implicit casts to text
  - From: Tom Lane

Prev by Date: Re: Bug in UTF8-Validation Code?
Next by Date: Re: [PATCHES] Full page writes improvement, code update
Previous by thread: Re: Oracle indemnifies PostgreSQL on its patents
Next by thread: Re: Implicit casts to text
Indexes:
- Main
- Thread

Home | Main Index | Thread Index

Search

Peripheral Links

Header And Logo

Site Navigation

Section Navigation

Implicit casts to text

Search archives
	Advanced Search