Home > mailing lists

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

From	Hans Buschmann
Subject	AW: BUG #17842: Adding a qual to a working query gets bogus syntax error
Date	March 14, 2023 22:07:59
Msg-id	bc92c5d6550e4240b4d5fb2605044b60@nidsa.net Whole thread Raw
In response to	Re: BUG #17842: Adding a qual to a working query gets bogus syntax error (Tom Lane <tgl@sss.pgh.pa.us>)
Responses	Re: BUG #17842: Adding a qual to a working query gets bogus syntax error
List	pgsql-bugs

Tree view

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=# 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)

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

pgsql-bugs by date:

From: Tom Lane
Date: 14 March 2023, 21:25:18
Subject: Re: BUG #17842: Adding a qual to a working query gets bogus syntax error

From: "David G. Johnston"
Date: 14 March 2023, 22:26:41
Subject: Re: BUG #17842: Adding a qual to a working query gets bogus syntax error

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

Previous

Next