Thread
-
BUG #17842: Adding a qual to a working query gets bogus syntax error
PG Bug reporting form <noreply@postgresql.org> — 2023-03-14T15:48:29Z
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 -
Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-14T16:20:46Z
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 -
AW: BUG #17842: Adding a qual to a working query gets bogus syntax error
Hans Buschmann <buschmann@nidsa.net> — 2023-03-14T18:12:02Z
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 -
Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
David G. Johnston <david.g.johnston@gmail.com> — 2023-03-14T18:19:32Z
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. > >
-
Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-14T18:25:18Z
"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
-
AW: BUG #17842: Adding a qual to a working query gets bogus syntax error
Hans Buschmann <buschmann@nidsa.net> — 2023-03-14T19:07:59Z
Hello Tom, I totally forgot to apply explain instead of explain analyze, Here EXPLAIN for the erroneous question: cpsdb=# 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-# ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=0.56..10438.53 rows=2209 width=44) Merge Cond: (newcol_imp.nnum = oldcol_imp.onum) 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) -> Nested Loop (cost=0.28..10111.27 rows=2220 width=77) Join Filter: (newcol_imp.nline ~~ (('%'::text || split_part(varchar_imp.vline, ' '::text, 1)) || '%'::text)) -> Index Scan using newcol_imp_pkey on newcol_imp (cost=0.28..107.37 rows=745 width=38) Filter: ((nline !~~ '%KEY%'::text) AND ((('0'::text || split_part(split_part(nline, '('::text, 2), ')'::text, 1)))::smallint > 0)) -> Materialize (cost=0.00..14.94 rows=596 width=39) -> Seq Scan on varchar_imp (cost=0.00..11.96 rows=596 width=39) -> Index Scan using oldcol_imp_pkey on oldcol_imp (cost=0.28..66.86 rows=2372 width=44) (10 Zeilen) --------------- EXPLAIN for the same with a restrictive clause for onum: 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 onum < 100 --and nlen > 0 ; ------------- EXPLAIN ANALYZE for the same with an irrelevant clause for onum cpsdb=# cpsdb=# 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 < 10000 cpsdb-# --and nlen > 0 cpsdb-# ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=0.56..30929.15 rows=6630 width=44) (actual time=3.881..257.727 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..72.79 rows=2372 width=44) (actual time=0.008..0.230 rows=2371 loops=1) Index Cond: (onum < 10000) -> Materialize (cost=0.28..30086.66 rows=6663 width=77) (actual time=1.577..256.773 rows=610 loops=1) -> Nested Loop (cost=0.28..30070.00 rows=6663 width=77) (actual time=1.576..256.675 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.011..0.422 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.009..0.046 rows=596 loops=1) Planning Time: 0.259 ms Execution Time: 257.766 ms (17 Zeilen) And indeed it shows, that qual pushdown (Filter line 8 in the first error case) causes the calculation of nlen (with splitpart) to be executed for every tuple in newcol_imp. But as written with the inner joins I intended to calculate nlen only after the joins. This is always OK because varchar_imp has only 596 valid records where the numeric or decimal cases are eliminated. I think this qual pushdown is dangerous, because it is applied before the join and so includes invalid cases. Hope this is a certain clarification Hans Buschmann ________________________________ Von: Tom Lane <tgl@sss.pgh.pa.us> Gesendet: Dienstag, 14. März 2023 19:25 An: David G. Johnston Cc: Hans Buschmann; PostgreSQL Bug List Betreff: Re: BUG #17842: Adding a qual to a working query gets bogus syntax error "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 -
Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
David G. Johnston <david.g.johnston@gmail.com> — 2023-03-14T19:26:41Z
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 >