Обсуждение: [BUGS] 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.hanenberg@googlemail.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.




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

On 31 October 2017 at 13:36,  <stefan.hanenberg@googlemail.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


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

"David G. Johnston" <david.g.johnston@gmail.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


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

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


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