BUG #17842: Adding a qual to a working query gets bogus syntax error - Mailing list pgsql-bugs

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