So I was informed today that UNION types integer and text cannot be
matched. Alright, but it failed to tell which particular expressions
in this 3-branch, 30-columns-each UNION clause in a 100-line statement
it was talking about. So I made the attached patch to give some better
pointers. Example:

peter=# values(0,1), (1::bigint,2), ('text'::text,3);
ERROR: 42804: VALUES types bigint at position 2 and text at position 3
cannot be matched in instance 1

I'm not sure about the terminology "position" and "instance"; they're
just two coordinates to get at the problem.

None of this will help if you have multiple unrelated clauses that
invoke select_common_type(), but that might be better handled using the
parser location mechanism.

Comments?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Peter Eisentraut" <peter_e@gmx.net> writes:

> peter=# values(0,1), (1::bigint,2), ('text'::text,3);
> ERROR: 42804: VALUES types bigint at position 2 and text at position 3
> cannot be matched in instance 1
>
> I'm not sure about the terminology "position" and "instance"; they're
> just two coordinates to get at the problem.

Wouldn't that just be column 1 in rows 2 and 3?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Gregory Stark wrote:
> "Peter Eisentraut" <peter_e@gmx.net> writes:
> > peter=# values(0,1), (1::bigint,2), ('text'::text,3);
> > ERROR: 42804: VALUES types bigint at position 2 and text at
> > position 3 cannot be matched in instance 1
> >
> > I'm not sure about the terminology "position" and "instance";
> > they're just two coordinates to get at the problem.
>
> Wouldn't that just be column 1 in rows 2 and 3?

In this case yes, but the routine is also used for UNION, IN, GREATEST,
JOIN/USING and others.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane wrote:
> +1 on using the parser location mechanism and avoiding the
> terminology problem altogether.

I figured we would let the parser only point to the UNION or VALUES or
whatever word. It would be fairly cumbersome to drag the individual
expression positions down into select_common_value() for full
precision.

> I fear though that we're not set up
> to have multiple locations in one error report. Will it be
> sufficient if we point at one of the two offending expressions? (I'd
> guess pointing at the second makes the most sense, if feasible.)

I don't think that would help. In the example I was looking at 90
expression and I had no idea in most cases what their results types
are, so if it tells me that the 15th expression somewhere doesn't
match, I would need to know which is the other mismatching one.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane wrote:
>> +1 on using the parser location mechanism and avoiding the
>> terminology problem altogether.

> I figured we would let the parser only point to the UNION or VALUES or
> whatever word. It would be fairly cumbersome to drag the individual
> expression positions down into select_common_value() for full
> precision.

Possibly. I was thinking of demanding that callers pass an additional
list containing positions for the expressions, but hadn't looked to see
how easy that might be. In any case, if we need to point at both
expressions then it's not gonna work.

For the VALUES case, the suggestion of "row" and "column" terminology
seems the right thing, but for UNION it would be better to use "branch"
perhaps ("row" certainly seems misleading). How can we make that work
without indulging in untranslatable keyword-insertion?

Another possibility is "alternative" and "column", which seems like it
applies more or less equally poorly to both cases.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:

> For the VALUES case, the suggestion of "row" and "column" terminology
> seems the right thing, but for UNION it would be better to use "branch"
> perhaps ("row" certainly seems misleading). How can we make that work
> without indulging in untranslatable keyword-insertion?
>
> Another possibility is "alternative" and "column", which seems like it
> applies more or less equally poorly to both cases.

Maybe it would be good to have distinctive terminology if at all
possible, as it will be clearer for both cases.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> For the VALUES case, the suggestion of "row" and "column" terminology
> seems the right thing, but for UNION it would be better to use "branch"
> perhaps ("row" certainly seems misleading). How can we make that work
> without indulging in untranslatable keyword-insertion?

Hm, I guess the SQL spec terminology in both cases would be "table
expression".

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Peter Eisentraut <peter_e@gmx.net> writes:
> ... I'm not sure about the terminology "position" and "instance"; they're
> just two coordinates to get at the problem.

> None of this will help if you have multiple unrelated clauses that
> invoke select_common_type(), but that might be better handled using the
> parser location mechanism.

Were there any objections to changing this patch so that it reports
the second expression's parser location, instead of some arbitrary
numbers? The way I'm envisioning doing it is:

1. Invent an exprLocation() function (comparable to, say, exprType)
that knows how to get the parser location from any subtype of Node that
has one.

2. Make a variant of select_common_type() that takes a list of Exprs
instead of just type OIDs. It can get the type IDs from these
using exprType(), and it can get their locations using exprLocation()
if needed.

We could almost just replace the current form of select_common_type()
with the version envisioned in #2. In a quick grep, there is only
one usage of select_common_type() that isn't invoking it on a list
of exprType() results that could be trivially changed over to the
underlying expressions instead --- and that is in
transformSetOperationTree, which may be dealing with inputs that
are previously-resolved output types for child set operations.
I'm not sure about a better way to complain about type mismatches in
nested set operations, anyway. We could possibly keep track of one of
the sub-expressions that had determined the resolved output type, and
point to that, but it would likely seem a bit arbitrary to the user.
Thoughts anyone?

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

Added to TODO:

* Improve reporting of UNION type mismatches

http://archives.postgresql.org/pgsql...4/msg00944.php
http://archives.postgresql.org/pgsql...3/msg00597.php


---------------------------------------------------------------------------

Peter Eisentraut wrote:
> So I was informed today that UNION types integer and text cannot be
> matched. Alright, but it failed to tell which particular expressions
> in this 3-branch, 30-columns-each UNION clause in a 100-line statement
> it was talking about. So I made the attached patch to give some better
> pointers. Example:
>
> peter=# values(0,1), (1::bigint,2), ('text'::text,3);
> ERROR: 42804: VALUES types bigint at position 2 and text at position 3
> cannot be matched in instance 1
>
> I'm not sure about the terminology "position" and "instance"; they're
> just two coordinates to get at the problem.
>
> None of this will help if you have multiple unrelated clauses that
> invoke select_common_type(), but that might be better handled using the
> parser location mechanism.
>
> Comments?
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

-
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers