Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
(pgsql 9.2devel (25 oct) with your latest CORRESPONDING patch; linux x86_64 GNU/Linux 2.6.18-274.3.1.el5) Hi, here is another peculiarity, which I think is a bug: -- first without CORRESPONDING: $ psql -Xaf null.sql select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) -- then with CORRESPONDING: select 1 a , 2 b union all corresponding select null a, 4 b ; psql:null.sql:9: ERROR: failed to find conversion function from unknown to integer If the null value is in a table column the error does not occur: drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,2); drop table if exists t2; create table t2 (a int, b int); insert into t2 values (null,2); select a,b from t1 union all corresponding select a,b from t2 ; a | b ---+--- 1 | 2 | 2 (2 rows) I'm not sure it is actually a bug; but it seems an unneccessary error. thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
Hi, Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer It is thrown from parse_coerce.c:coerce_type method. I will try to dig deep on it. Regards, Kerem KAT On Thu, Oct 27, 2011 at 15:45, Erik Rijkers e...@xs4all.nl wrote: (pgsql 9.2devel (25 oct) with your latest CORRESPONDING patch; linux x86_64 GNU/Linux 2.6.18-274.3.1.el5) Hi, here is another peculiarity, which I think is a bug: -- first without CORRESPONDING: $ psql -Xaf null.sql select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) -- then with CORRESPONDING: select 1 a , 2 b union all corresponding select null a, 4 b ; psql:null.sql:9: ERROR: failed to find conversion function from unknown to integer If the null value is in a table column the error does not occur: drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,2); drop table if exists t2; create table t2 (a int, b int); insert into t2 values (null,2); select a,b from t1 union all corresponding select a,b from t2 ; a | b ---+--- 1 | 2 | 2 (2 rows) I'm not sure it is actually a bug; but it seems an unneccessary error. thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
Kerem Kat kerem...@gmail.com writes: Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer Yeah, this is a longstanding issue that is not simple to fix without introducing other unpleasantnesses. It is not something you should try to deal with at the same time as implementing CORRESPONDING. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
I wrote: Kerem Kat kerem...@gmail.com writes: Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer Yeah, this is a longstanding issue that is not simple to fix without introducing other unpleasantnesses. It is not something you should try to deal with at the same time as implementing CORRESPONDING. BTW, just to clarify: although that case fails, the case Erik was complaining of does work in unmodified Postgres: regression=# select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) and I agree with him that it should still work with CORRESPONDING. Even though the behavior of unlabeled NULLs is less than perfect, we definitely don't want to break cases that work now. I suspect the failure means that you tried to postpone too much work to plan time. You do have to match up the columns honestly at parse time and do the necessary type coercions on them then. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
Kerem Kat kerem...@gmail.com writes: On Thu, Oct 27, 2011 at 23:20, Tom Lane t...@sss.pgh.pa.us wrote: BTW, just to clarify: although that case fails, the case Erik was complaining of does work in unmodified Postgres: ... and I agree with him that it should still work with CORRESPONDING. That is by design, because CORRESPONDING is implemented as subqueries: Well, this appears to me to be a counterexample sufficient to refute that implementation decision. You can inject subqueries at plan time, if that helps you make things match up, but you can't rearrange things that way at parse time, as I gather you're doing or else you would not be seeing this problem. In any case, I already pointed out to you that rearranging the parse tree that way is problematic for reverse-listing the parse tree. We don't want to see subqueries injected in the results of printing parse trees with ruleutils.c. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (PATCH) Adding CORRESPONDING (NULL error)
On Thu, Oct 27, 2011 at 23:20, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Kerem Kat kerem...@gmail.com writes: Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer Yeah, this is a longstanding issue that is not simple to fix without introducing other unpleasantnesses. It is not something you should try to deal with at the same time as implementing CORRESPONDING. BTW, just to clarify: although that case fails, the case Erik was complaining of does work in unmodified Postgres: regression=# select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) and I agree with him that it should still work with CORRESPONDING. Even though the behavior of unlabeled NULLs is less than perfect, we definitely don't want to break cases that work now. I suspect the failure means that you tried to postpone too much work to plan time. You do have to match up the columns honestly at parse time and do the necessary type coercions on them then. regards, tom lane That is by design, because CORRESPONDING is implemented as subqueries: select 1 a , 2 b union all corresponding select null a, 4 b ; is equivalent to SELECT a, b FROM ( SELECT 1 a, 2 b ) foo UNION ALL SELECT a, b FROM ( SELECT null a, 4 b ) foo2; which gives the same error in unpatched postgres. Regards, Kerem KAT -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
