Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcA0J-0002uI-Iz for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Mar 2023 19:08:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pcA0I-0005Nl-9f for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Mar 2023 19:08:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcA0H-0005Nc-Or for pgsql-bugs@lists.postgresql.org; Tue, 14 Mar 2023 19:08:10 +0000 Received: from ma03-relay.lansolnet.com ([176.95.46.36]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pcA0E-0004Fe-Dc for pgsql-bugs@lists.postgresql.org; Tue, 14 Mar 2023 19:08:08 +0000 Received: from unknown ([192.168.8.230]) by ma03-relay.lansolnet.com; Tue, 14 Mar 2023 20:08:02 +0100 Received: from MBX200E.cloud4partner.com (192.168.8.234) by MBX200A.cloud4partner.com (192.168.8.230) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.32; Tue, 14 Mar 2023 20:07:59 +0100 Received: from MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634]) by MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634%13]) with mapi id 15.01.0669.032; Tue, 14 Mar 2023 20:07:59 +0100 From: Hans Buschmann To: Tom Lane , "David G. Johnston" CC: PostgreSQL Bug List Subject: AW: BUG #17842: Adding a qual to a working query gets bogus syntax error Thread-Topic: BUG #17842: Adding a qual to a working query gets bogus syntax error Thread-Index: AQHZVoyF1Hfip8WgN0qSJ4E3yUuOZq76ZAAAgAAqpGv///aLAIAAAZwAgAAXtlg= Date: Tue, 14 Mar 2023 19:07:59 +0000 Message-ID: References: <17842-0697f895ced573ee@postgresql.org> <2692255.1678810846@sss.pgh.pa.us> ,<2757854.1678818318@sss.pgh.pa.us> In-Reply-To: <2757854.1678818318@sss.pgh.pa.us> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.8.240] Content-Type: multipart/alternative; boundary="_000_bc92c5d6550e4240b4d5fb2605044b60nidsanet_" MIME-Version: 1.0 X-cloud-security-sender: buschmann@nidsa.net X-cloud-security-recipient: pgsql-bugs@lists.postgresql.org X-cloud-security-Virusscan: CLEAN X-cloud-security-disclaimer: This E-Mail was scanned by E-Mailservice on ma03-relay.lansolnet.com with 3AAC019837BA X-cloud-security-connect: unknown[192.168.8.230], TLS=1, IP=192.168.8.230 X-cloud-security: scantime:.5050 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_bc92c5d6550e4240b4d5fb2605044b60nidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Tom, I totally forgot to apply explain instead of explain analyze, Here EXPLAIN for the erroneous question: cpsdb=3D# explain -- select * from ( -- select count(*) from ( -- select le= ngth(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=3Dnnum 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!=3Dolen cpsdb-# and nlen > 0 cpsdb-# ; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------- Merge Join (cost=3D0.56..10438.53 rows=3D2209 width=3D44) Merge Cond: (newcol_imp.nnum =3D oldcol_imp.onum) Join Filter: ((('0'::text || split_part(split_part(varchar_imp.vline, '(= '::text, 2), ')'::text, 1)))::smallint <> (('0'::text || split_part(split_p= art(oldcol_imp.oline, '('::text, 2), ')'::text, 1)))::smallint) -> Nested Loop (cost=3D0.28..10111.27 rows=3D2220 width=3D77) Join Filter: (newcol_imp.nline ~~ (('%'::text || split_part(varcha= r_imp.vline, ' '::text, 1)) || '%'::text)) -> Index Scan using newcol_imp_pkey on newcol_imp (cost=3D0.28..= 107.37 rows=3D745 width=3D38) Filter: ((nline !~~ '%KEY%'::text) AND ((('0'::text || split= _part(split_part(nline, '('::text, 2), ')'::text, 1)))::smallint > 0)) -> Materialize (cost=3D0.00..14.94 rows=3D596 width=3D39) -> Seq Scan on varchar_imp (cost=3D0.00..11.96 rows=3D596 = width=3D39) -> Index Scan using oldcol_imp_pkey on oldcol_imp (cost=3D0.28..66.86 = rows=3D2372 width=3D44) (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=3Dnnum 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!=3Dolen and onum < 100 --and nlen > 0 ; ------------- EXPLAIN ANALYZE for the same with an irrelevant clause for onum cpsdb=3D# cpsdb=3D# cpsdb=3D# cpsdb=3D# 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=3Dnnum 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!=3Dolen cpsdb-# and onum < 10000 cpsdb-# --and nlen > 0 cpsdb-# ; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------- Merge Join (cost=3D0.56..30929.15 rows=3D6630 width=3D44) (actual time=3D= 3.881..257.727 rows=3D185 loops=3D1) Merge Cond: (oldcol_imp.onum =3D newcol_imp.nnum) Join Filter: ((('0'::text || split_part(split_part(varchar_imp.vline, '(= '::text, 2), ')'::text, 1)))::smallint <> (('0'::text || split_part(split_p= art(oldcol_imp.oline, '('::text, 2), ')'::text, 1)))::smallint) Rows Removed by Join Filter: 425 -> Index Scan using oldcol_imp_pkey on oldcol_imp (cost=3D0.28..72.79 = rows=3D2372 width=3D44) (actual time=3D0.008..0.230 rows=3D2371 loops=3D1) Index Cond: (onum < 10000) -> Materialize (cost=3D0.28..30086.66 rows=3D6663 width=3D77) (actual = time=3D1.577..256.773 rows=3D610 loops=3D1) -> Nested Loop (cost=3D0.28..30070.00 rows=3D6663 width=3D77) (a= ctual time=3D1.576..256.675 rows=3D610 loops=3D1) 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=3D= 0.28..71.79 rows=3D2236 width=3D38) (actual time=3D0.011..0.422 rows=3D2220= loops=3D1) Filter: (nline !~~ '%KEY%'::text) Rows Removed by Filter: 152 -> Materialize (cost=3D0.00..14.94 rows=3D596 width=3D39) = (actual time=3D0.000..0.015 rows=3D596 loops=3D2220) -> Seq Scan on varchar_imp (cost=3D0.00..11.96 rows= =3D596 width=3D39) (actual time=3D0.009..0.046 rows=3D596 loops=3D1) 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 c= ase) causes the calculation of nlen (with splitpart) to be executed for eve= ry 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 record= s where the numeric or decimal cases are eliminated. I think this qual pushdown is dangerous, because it is applied before the j= oin and so includes invalid cases. Hope this is a certain clarification Hans Buschmann ________________________________ Von: Tom Lane Gesendet: Dienstag, 14. M=E4rz 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" writes: > On Tue, Mar 14, 2023, 11:12 Hans Buschmann 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 --_000_bc92c5d6550e4240b4d5fb2605044b60nidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello Tom,


I totally forgot to apply explain instead of explain analyze,


Here EXPLAIN for the erroneous question:



cpsdb=3D# explain -- select * from ( -- select count(*) from ( -- sele= ct 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=3Dnnum
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!=3Dolen
cpsdb-# and nlen > 0
cpsdb-# ;
                    =                      = ;                     &nb= sp;                     &= nbsp;                 QUERY PLAN&nb= sp;                     &= nbsp;                    =           
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------
 Merge Join  (cost=3D0.56..10438.53 rows=3D2209 width=3D44)<= /div>
   Merge Cond: (newcol_imp.nnum =3D oldcol_imp.onum)
   Join Filter: ((('0'::text || split_part(split_part(varcha= r_imp.vline, '('::text, 2), ')'::text, 1)))::smallint <> (('0'::text = || split_part(split_part(oldcol_imp.oline, '('::text, 2), ')'::text, 1)))::= smallint)
   ->  Nested Loop  (cost=3D0.28..10111.27 rows= =3D2220 width=3D77)
         Join Filter: (newcol_imp.nline ~~ ((= '%'::text || split_part(varchar_imp.vline, ' '::text, 1)) || '%'::text))
         ->  Index Scan using newcol_= imp_pkey on newcol_imp  (cost=3D0.28..107.37 rows=3D745 width=3D38)
               Filter: ((nline= !~~ '%KEY%'::text) AND ((('0'::text || split_part(split_part(nline, '('::t= ext, 2), ')'::text, 1)))::smallint > 0))
         ->  Materialize  (cost= =3D0.00..14.94 rows=3D596 width=3D39)
               ->  Seq= Scan on varchar_imp  (cost=3D0.00..11.96 rows=3D596 width=3D39)
   ->  Index Scan using oldcol_imp_pkey on oldcol_im= p  (cost=3D0.28..66.86 rows=3D2372 width=3D44)
(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=3Dnnum
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!=3Dolen
and onum < 100
--and nlen > 0
;

-------------
EXPLAIN ANALYZE for the same with an irrelevant clause for onum
cpsdb=3D#
cpsdb=3D#
cpsdb=3D#
cpsdb=3D# explain analyze -- explain analyze verbose -- explain -- sel= ect * 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=3Dnnum
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!=3Dolen
cpsdb-# and onum < 10000
cpsdb-# --and nlen > 0
cpsdb-# ;
                    =                      = ;                     &nb= sp;                     &= nbsp;                 QUERY PLAN&nb= sp;                     &= nbsp;                    =           
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------
 Merge Join  (cost=3D0.56..30929.15 rows=3D6630 width=3D44) = (actual time=3D3.881..257.727 rows=3D185 loops=3D1)
   Merge Cond: (oldcol_imp.onum =3D newcol_imp.nnum)
   Join Filter: ((('0'::text || split_part(split_part(varcha= r_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_im= p  (cost=3D0.28..72.79 rows=3D2372 width=3D44) (actual time=3D0.008..0= .230 rows=3D2371 loops=3D1)
         Index Cond: (onum < 10000)
   ->  Materialize  (cost=3D0.28..30086.66 rows= =3D6663 width=3D77) (actual time=3D1.577..256.773 rows=3D610 loops=3D1)
         ->  Nested Loop  (cost= =3D0.28..30070.00 rows=3D6663 width=3D77) (actual time=3D1.576..256.675 row= s=3D610 loops=3D1)
               Join Filter: (n= ewcol_imp.nline ~~ (('%'::text || split_part(varchar_imp.vline, ' '::text, = 1)) || '%'::text))
               Rows Removed by= Join Filter: 1322510
               ->  Ind= ex Scan using newcol_imp_pkey on newcol_imp  (cost=3D0.28..71.79 rows= =3D2236 width=3D38) (actual time=3D0.011..0.422 rows=3D2220 loops=3D1)
                    =  Filter: (nline !~~ '%KEY%'::text)
                    =  Rows Removed by Filter: 152
               ->  Mat= erialize  (cost=3D0.00..14.94 rows=3D596 width=3D39) (actual time=3D0.= 000..0.015 rows=3D596 loops=3D2220)
                    =  ->  Seq Scan on varchar_imp  (cost=3D0.00..11.96 rows=3D= 596 width=3D39) (actual time=3D0.009..0.046 rows=3D596 loops=3D1)
 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 er= ror case) causes the calculation of nlen (with splitpart) to be executed fo= r 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 va= lid 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=E4rz 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) an= d 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<= br> is not complaining about anything in the text of the query.

            &nb= sp;           regards, to= m lane
--_000_bc92c5d6550e4240b4d5fb2605044b60nidsanet_--