From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | buschmann(at)nidsa(dot)net |
Subject: | BUG #17842: Adding a qual to a working query gets bogus syntax error |
Date: | 2023-03-14 15:48:29 |
Message-ID: | 17842-0697f895ced573ee@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17842
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-03-14 16:20:46 | Re: BUG #17842: Adding a qual to a working query gets bogus syntax error |
Previous Message | Tom Lane | 2023-03-14 15:27:34 | Re: BUG #17841: COPY Command Invalid QUOTES |