pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17842: Adding a qual to a working query gets bogus syntax error
5+ messages / 3 participants
[nested] [flat]
* BUG #17842: Adding a qual to a working query gets bogus syntax error
@ 2023-03-14 15:48 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2023-03-14 15:48 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: buschmann@nidsa.net
The following bug has been logged on the website:
Bug reference: 17842
Logged by: Hans Buschmann
Email address: buschmann@nidsa.net
PostgreSQL version: 15.2
Operating system: Windows 11 x64
Description:
During refactoring our application I got a bogus syntax error when adding a
simple where-clause to a working query.
Working query result:
cpsdb=#
cpsdb=# -- explain analyze -- explain analyze verbose -- explain -- select *
from ( -- select count(*) from ( -- select length(sel) from (
cpsdb=# select
cpsdb-# onum
cpsdb-# ,'column'||(onum::varchar) as vname
cpsdb-# ,vlen
cpsdb-# ,nlen
cpsdb-# ,olen
cpsdb-# ,NULLIF(vlen-olen,0) as delta_len
cpsdb-# from (
cpsdb(# select *
cpsdb(# ,('0'||split_part(split_part(nline,'(',2),')',1))::smallint as
nlen
cpsdb(# ,('0'||split_part(split_part(oline,'(',2),')',1))::smallint as
olen
cpsdb(# from newcol_imp
cpsdb(# join oldcol_imp on onum=nnum
cpsdb(# join (
cpsdb(# select
cpsdb(# vnum
cpsdb(# ,split_part(vline,' ',1) as vname
cpsdb(# ,('0'||split_part(split_part(vline,'(',2),')',1))::smallint as
vlen
cpsdb(# from varchar_imp
cpsdb(# ) qv on nline like '%'||vname||'%'
cpsdb(# where nline not like '%KEY%'
cpsdb(# ) qj
cpsdb-# --limit 30
cpsdb-# where vlen!=olen
cpsdb-# -- and nlen > 0
cpsdb-# ;
onum | vname | vlen | nlen | olen | delta_len
------+------------+------+------+------+-----------
35 | column35 | 30 | 30 | 15 | 15
56 | column56 | 254 | 254 | 50 | 204
89 | column89 | 4 | 5 | 5 | -1
111 | column111 | 4 | 4 | 5 | -1
111 | column111 | 4 | 4 | 5 | -1
125 | column125 | 12 | 12 | 10 | 2
...
2362 | column2362 | 20 | 0 | 0 | 20
2365 | column2365 | 20 | 0 | 0 | 20
2366 | column2366 | 20 | 0 | 0 | 20
(185 Zeilen)
explain analyze -- explain analyze verbose -- explain -- select * from ( --
select count(*) from ( -- select length(sel) from (
select
onum
,'column'||(onum::varchar) as vname
,vlen
,nlen
,olen
,NULLIF(vlen-olen,0) as delta_len
from (
select *
,('0'||split_part(split_part(nline,'(',2),')',1))::smallint as nlen
,('0'||split_part(split_part(oline,'(',2),')',1))::smallint as olen
from newcol_imp
join oldcol_imp on onum=nnum
join (
select
vnum
,split_part(vline,' ',1) as vname
,('0'||split_part(split_part(vline,'(',2),')',1))::smallint as vlen
from varchar_imp
) qv on nline like '%'||vname||'%'
where nline not like '%KEY%'
) qj
--limit 30
where vlen!=olen
-- and nlen > 0
;
Resulting plan:
cpsdb-# ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.56..30923.22 rows=6630 width=44) (actual
time=3.889..257.870 rows=185 loops=1)
Merge Cond: (oldcol_imp.onum = newcol_imp.nnum)
Join Filter: ((('0'::text || split_part(split_part(varchar_imp.vline,
'('::text, 2), ')'::text, 1)))::smallint <> (('0'::text ||
split_part(split_part(oldcol_imp.oline, '('::text, 2), ')'::text,
1)))::smallint)
Rows Removed by Join Filter: 425
-> Index Scan using oldcol_imp_pkey on oldcol_imp (cost=0.28..66.86
rows=2372 width=44) (actual time=0.007..0.226 rows=2371 loops=1)
-> Materialize (cost=0.28..30086.66 rows=6663 width=77) (actual
time=1.571..256.925 rows=610 loops=1)
-> Nested Loop (cost=0.28..30070.00 rows=6663 width=77) (actual
time=1.570..256.824 rows=610 loops=1)
Join Filter: (newcol_imp.nline ~~ (('%'::text ||
split_part(varchar_imp.vline, ' '::text, 1)) || '%'::text))
Rows Removed by Join Filter: 1322510
-> Index Scan using newcol_imp_pkey on newcol_imp
(cost=0.28..71.79 rows=2236 width=38) (actual time=0.007..0.424 rows=2220
loops=1)
Filter: (nline !~~ '%KEY%'::text)
Rows Removed by Filter: 152
-> Materialize (cost=0.00..14.94 rows=596 width=39) (actual
time=0.000..0.015 rows=596 loops=2220)
-> Seq Scan on varchar_imp (cost=0.00..11.96 rows=596
width=39) (actual time=0.008..0.044 rows=596 loops=1)
Planning Time: 0.246 ms
Execution Time: 257.907 ms
(16 Zeilen)
When uncommenting the last comment in the query I get a syntax error:
cpsdb=#
cpsdb=# -- explain analyze -- explain analyze verbose -- explain -- select *
from ( -- select count(*) from ( -- select length(sel) from (
cpsdb=# select
cpsdb-# onum
cpsdb-# ,'column'||(onum::varchar) as vname
cpsdb-# ,vlen
cpsdb-# ,nlen
cpsdb-# ,olen
cpsdb-# ,NULLIF(vlen-olen,0) as delta_len
cpsdb-# from (
cpsdb(# select *
cpsdb(# ,('0'||split_part(split_part(nline,'(',2),')',1))::smallint as
nlen
cpsdb(# ,('0'||split_part(split_part(oline,'(',2),')',1))::smallint as
olen
cpsdb(# from newcol_imp
cpsdb(# join oldcol_imp on onum=nnum
cpsdb(# join (
cpsdb(# select
cpsdb(# vnum
cpsdb(# ,split_part(vline,' ',1) as vname
cpsdb(# ,('0'||split_part(split_part(vline,'(',2),')',1))::smallint as
vlen
cpsdb(# from varchar_imp
cpsdb(# ) qv on nline like '%'||vname||'%'
cpsdb(# where nline not like '%KEY%'
cpsdb(# ) qj
cpsdb-# --limit 30
cpsdb-# where vlen!=olen
cpsdb-# and nlen > 0
cpsdb-# ;
ERROR: invalid input syntax for type smallint: "0x86"
cpsdb=#
even adding another qual of unmodified integer columns shows a comparable
error:
cpsdb=#
cpsdb=# -- explain analyze -- explain analyze verbose -- explain -- select *
from ( -- select count(*) from ( -- select length(sel) from (
cpsdb=# select
cpsdb-# onum
cpsdb-# ,'column'||(onum::varchar) as vname
cpsdb-# ,vlen
cpsdb-# ,nlen
cpsdb-# ,olen
cpsdb-# ,NULLIF(vlen-olen,0) as delta_len
cpsdb-# from (
cpsdb(# select *
cpsdb(# ,('0'||split_part(split_part(nline,'(',2),')',1))::smallint as
nlen
cpsdb(# ,('0'||split_part(split_part(oline,'(',2),')',1))::smallint as
olen
cpsdb(# from newcol_imp
cpsdb(# join oldcol_imp on onum=nnum
cpsdb(# join (
cpsdb(# select
cpsdb(# vnum
cpsdb(# ,split_part(vline,' ',1) as vname
cpsdb(# ,('0'||split_part(split_part(vline,'(',2),')',1))::smallint as
vlen
cpsdb(# from varchar_imp
cpsdb(# ) qv on nline like '%'||vname||'%'
cpsdb(# where nline not like '%KEY%'
cpsdb(# ) qj
cpsdb-# --limit 30
cpsdb-# where vlen!=olen
cpsdb-# and onum < 100
cpsdb-# --and nlen > 0
cpsdb-# ;
ERROR: invalid input syntax for type smallint: "08,3"
cpsdb=#
The tables are simple like this:
cpsdb=# \d newcol_imp
Tabelle �admin.newcol_imp�
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+----------------------------------
nnum | integer | | not null | generated by default as
identity
nline | text | | |
Indexe:
"newcol_imp_pkey" PRIMARY KEY, btree (nnum)
cpsdb=# \d oldcol_imp
Tabelle �admin.oldcol_imp�
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+----------------------------------
onum | integer | | not null | generated by default as
identity
oline | text | | |
Indexe:
"oldcol_imp_pkey" PRIMARY KEY, btree (onum)
cpsdb=# \d varchar_imp
Tabelle �admin.varchar_imp�
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
--------+---------+--------------+---------------+----------------------------------
vnum | integer | | not null | generated by default as
identity
vline | text | | |
and contain no more then 2372 lines.
They contain all the lines from (different, historical, other database)
output from a structure pg_dump or mysql_dump
and are imported like
create table admin.newcol_imp (
iline int generated by default as identity
,cline text
)
;
copy newcol_imp (cline)
from '<output_file_path_of_structure dump.sql>'
;
alter table newcol_imp add primary key (iline);
Unfortunately I cannot provide the unmodified data.
The same syntax error reoccurs even if the query is rewritten as a CTE.
My guess is that something is wrong with quality pushdown / other plan is
choosen, but I have no clue where to look further.
Thoughts?
Hans Buschmannn
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
@ 2023-03-14 16:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-03-14 16:20 UTC (permalink / raw)
To: buschmann@nidsa.net; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
@ 2023-03-14 18:19 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-03-14 18:19 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Tue, Mar 14, 2023, 11:12 Hans Buschmann <buschmann@nidsa.net> wrote:
>
> 2. Why is it reporting an syntax error (supposed at parse time) and not a
> runtime error (supposed at execution time)
>
>
Your parsed query doesn't have a syntax error. Data it processes does,
which is found when parsing that data during query execution.
David J.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
@ 2023-03-14 18:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-03-14 18:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Hans Buschmann <buschmann@nidsa.net>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Mar 14, 2023, 11:12 Hans Buschmann <buschmann@nidsa.net> wrote:
>> 2. Why is it reporting an syntax error (supposed at parse time) and not a
>> runtime error (supposed at execution time)
> Your parsed query doesn't have a syntax error. Data it processes does,
> which is found when parsing that data during query execution.
Right. It's kind of unfortunate perhaps that we use SQLSTATEs from
the "syntax error" class for malformed data input, but that seems
unlikely to change. In any case, it's pretty obvious that this error
is not complaining about anything in the text of the query.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
@ 2023-03-14 19:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-03-14 19:26 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Tue, Mar 14, 2023, 12:08 Hans Buschmann <buschmann@nidsa.net> wrote:
>
> I think this qual pushdown is dangerous, because it is applied before the
> join and so includes invalid cases.
>
Well, it's better than any alternative that is more expensive even when the
data in question isn't prone to parsing problems. We optimize for the
common case of consistent normalized data. You have tools if you need to
operate with bad data.
David J.
> tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-03-14 19:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-14 15:48 BUG #17842: Adding a qual to a working query gets bogus syntax error PG Bug reporting form <noreply@postgresql.org>
2023-03-14 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-14 18:19 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-14 18:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-14 19:26 ` David G. Johnston <david.g.johnston@gmail.com>
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