Re: BUG #14883: Syntax SQL error (42601), but should be a different error no

The following bug has been logged on the website:

Bug reference: 14883
Logged by: Stefan Hanenberg
Email address: stefan(dot)hanenberg(at)googlemail(dot)com
PostgreSQL version: 10.0
Operating system: Windows 10/64
Description:

I am not 100% sure whether this is really a bug, I report it anyway.

The following SQL statement leads to an error 42601 (syntax error), although
the error is not a syntax error.

create table t1 (a integer,b integer);
create table t2 (a integer,b integer,c integer);
select * from t1 union select * from t2;

We are currently running automated tests on hundreds of thousands SQL in
order to check, what kind of errors they contain. It is really problematic
for us that the error is a 42601 error, although it seems rather as if it is
a type error.

On 31 October 2017 at 13:36, <stefan(dot)hanenberg(at)googlemail(dot)com> wrote:
> The following SQL statement leads to an error 42601 (syntax error), although
> the error is not a syntax error.
>
> create table t1 (a integer,b integer);
> create table t2 (a integer,b integer,c integer);
> select * from t1 union select * from t2;
>
> We are currently running automated tests on hundreds of thousands SQL in
> order to check, what kind of errors they contain. It is really problematic
> for us that the error is a 42601 error, although it seems rather as if it is
> a type error.

I guess it depends on how you've written the query. If you'd done:

select a,b from t1 union select a,b,c from t2;

then a syntax error seems a bit more reasonable.

The problem is that the '*' is just expanded to all non-dropped
columns well before getting to where that error is generated.

To fix it we'd likely need to just mark that the RangeTblEntry was
expanded in expandRelAttrs() then generate some other SQLCODE if any
of the RangeTblEntries where expanded.

So it looks simple enough to fix, at first glance.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Oct 30, 2017 at 6:35 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On 31 October 2017 at 13:36, <stefan(dot)hanenberg(at)googlemail(dot)com> wrote:
> > The following SQL statement leads to an error 42601 (syntax error),
> although
> > the error is not a syntax error.
>
> > We are currently running automated tests on hundreds of thousands SQL in
> > order to check, what kind of errors they contain. It is really
> problematic
> > for us that the error is a 42601 error, although it seems rather as if
> it is
> > a type error.
>
> then a syntax error seems a bit more reasonable.
>

Someone familiar with the SQL standard would need to confirm that our
choice in this case is not governed by the standard before I'd consider
changing it.

That said, I can make an argument for 42804 (datatype_mismatch) - which
still has a syntax error classification - since the implicit row-types from
the two input relations do not match each other.

The example error would read:

UNION types (int, int) and (int, int, int) cannot be matched

David J.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> Someone familiar with the SQL standard would need to confirm that our
> choice in this case is not governed by the standard before I'd consider
> changing it.

The SQL committee takes basically no interest in this area: their
taxonomy for syntax & semantic analysis errors consists of
(wait for it...)

Class 42 syntax error or access rule violation

with exactly no standard-defined subclasses. Whatever implementations
do to distinguish different subcategories is up to them (although IIRC
we borrowed some of our subcategories from DB2, so they're not things
we came up with out of noplace).

I agree that there's some case for considering this to be
ERRCODE_DATATYPE_MISMATCH, but I'm not sure that the case is strong
enough to justify a compatibility break from changing it.

More generally, there are a *lot* of ERRCODE_SYNTAX_ERROR calls in
the backend that could arguably be changed to something more specific,
even without inventing any new subcategories for the purpose. If we were
to decide that we're willing to make a compatibility break here, I'd
rather see a patch that goes through all of them and changes what seems
reasonable. Better a big break than drip drip drip ...

regards, tom lane

Am 31.10.2017 um 03:50 schrieb Tom Lane:
> More generally, there are a *lot* of ERRCODE_SYNTAX_ERROR calls in
> the backend that could arguably be changed to something more specific,
> even without inventing any new subcategories for the purpose. If we were
> to decide that we're willing to make a compatibility break here, I'd
> rather see a patch that goes through all of them and changes what seems
> reasonable. Better a big break than drip drip drip ...

I would be really happy if the error codes would reflect more on what
the error actually is -- at least for our automated tests, it turns out
to be quite complicated without directly using Postgres -- currently, we
have to use our own SQL parser in order to double check whether the
error is actually an syntac error or not.

Thanks for your reactions!

Best,
Stefan

On Fri, Nov 3, 2017 at 4:09 AM, Stefan Hanenberg <
stefan(dot)hanenberg(at)googlemail(dot)com> wrote:

> I would be really happy if the error codes would reflect more on what the
> error actually is -- at least for our automated tests,
>

This single example doesn't really motivate me to want to vote to change
even this single instance let alone wish for the big project Tom want's.

If you are unable to scratch your own itch here you are going to need to be
more forthcoming as to your application if you hope to convince others to
do so on your behalf. Though the lack of anyone else chiming in with a "me
too" doesn't bode well.

In any case I'd say this isn't a bug, or if it is at present its a "won't
fix". If you want to continue this please begin a new thread on
pgsql-general.

David J.