From: Hans Buschmann <buschmann@nidsa.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: AW: BUG #17842: Adding a qual to a working query gets bogus syntax error
Date: Tue, 14 Mar 2023 18:12:02 +0000
Message-ID: <dd6cfce2fca54079b1ad7cd044ee4b38@nidsa.net> (raw)
In-Reply-To: <2692255.1678810846@sss.pgh.pa.us>
References: <17842-0697f895ced573ee@postgresql.org>
<2692255.1678810846@sss.pgh.pa.us>
Hello Tom,
thanks for the quick response.
You are probably right with your assumption of a not convertible substring, but some questions remain:
1. When the question runs succesfully, all lines are subject to the join of qj (newcol_imp and oldcol_imp heve exact the same number of lines, matching logically), but why does a limitation of the result set trigger the error?
(There really may be some substrings from declarations of numeric(4,1) which are not excluded properly from conversion to smallint.
The '0'||<rest> tries to handle an empty string)
2. Why is it reporting an syntax error (supposed at parse time) and not a runtime error (supposed at execution time)
3. Why does an uncorrelated qual like and onum < 100 trigger an error too?
( I know that a limitation can change the choosen plan, but which one cannot be seen because of the error.)
The behavior of having a succesfull full query, but getting the error with some quals seems to me very unintuitive.
PS: The exactness and correctness of this specific query is certainly not overall important for me, but I wanted to point out this very unexpected behavior for some other users.
When I find some time, I'll try to provide some anonymized data.
Best regards
Hans Buschmann
________________________________
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: Dienstag, 14. März 2023 17:20
An: Hans Buschmann
Cc: pgsql-bugs@lists.postgresql.org
Betreff: Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
PG Bug reporting form <noreply@postgresql.org> writes:
> During refactoring our application I got a bogus syntax error when adding a
> simple where-clause to a working query.
My guess is that this:
> ('0'||split_part(split_part(nline,'(',2),')',1))::smallint as nlen
sometimes produces a string that fails to cast to smallint, which you
accidentally don't notice because it never gets evaluated for troublesome
values of nline --- until you add the "nlen > 0" condition. There isn't
anything in your query that prevents that from being evaluated fairly
early.
This isn't a bug, or at least you have provided no data that would
motivate (or indeed allow) anyone else to poke into it more closely.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: buschmann@nidsa.net, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: AW: BUG #17842: Adding a qual to a working query gets bogus syntax error
In-Reply-To: <dd6cfce2fca54079b1ad7cd044ee4b38@nidsa.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox