BUG #17842: Adding a qual to a working query gets bogus syntax error

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