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 1pc988-0000Tu-Av for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Mar 2023 18:12:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pc986-0003N5-BY for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Mar 2023 18:12:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pc986-0003Mu-1J for pgsql-bugs@lists.postgresql.org; Tue, 14 Mar 2023 18:12:10 +0000 Received: from ma04-relay.lansolnet.com ([176.95.46.38]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pc983-0007Lk-5G for pgsql-bugs@lists.postgresql.org; Tue, 14 Mar 2023 18:12:09 +0000 Received: from unknown ([192.168.8.231]) by ma04-relay.lansolnet.com; Tue, 14 Mar 2023 19:12:04 +0100 Received: from MBX200F.cloud4partner.com (192.168.8.235) by MBX200B.cloud4partner.com (192.168.8.231) 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 19:12:03 +0100 Received: from MBX200E.cloud4partner.com (192.168.8.234) by MBX200F.cloud4partner.com (192.168.8.235) 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 19:12:02 +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 19:12:02 +0100 From: Hans Buschmann To: Tom Lane CC: "pgsql-bugs@lists.postgresql.org" 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: AQHZVoyF1Hfip8WgN0qSJ4E3yUuOZq76ZAAAgAAqpGs= Date: Tue, 14 Mar 2023 18:12:02 +0000 Message-ID: References: <17842-0697f895ced573ee@postgresql.org>,<2692255.1678810846@sss.pgh.pa.us> In-Reply-To: <2692255.1678810846@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_dd6cfce2fca54079b1ad7cd044ee4b38nidsanet_" 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 ma04-relay.lansolnet.com with 09537B801F4 X-cloud-security-connect: unknown[192.168.8.231], TLS=1, IP=192.168.8.231 X-cloud-security: scantime:.3944 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_dd6cfce2fca54079b1ad7cd044ee4b38nidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Tom, thanks for the quick response. You are probably right with your assumption of a not convertible substring,= but some questions remain: 1. When the question runs succesfully, all lines are subject to the join of= qj (newcol_imp and oldcol_imp heve exact the same number of lines, matchin= g logically), but why does a limitation of the result set trigger the error= ? (There really may be some substrings from declarations of numeric(4,1) whic= h are not excluded properly from conversion to smallint. The '0'|| tries to handle an empty string) 2. Why is it reporting an syntax error (supposed at parse time) and not a r= untime error (supposed at execution time) 3. Why does an uncorrelated qual like and onum < 100 trigger an error too? ( I know that a limitation can change the choosen plan, but which one canno= t be seen because of the error.) The behavior of having a succesfull full query, but getting the error with = some quals seems to me very unintuitive. PS: The exactness and correctness of this specific query is certainly not o= verall important for me, but I wanted to point out this very unexpected beh= avior for some other users. When I find some time, I'll try to provide some anonymized data. Best regards Hans Buschmann ________________________________ Von: Tom Lane Gesendet: Dienstag, 14. M=E4rz 2023 17:20 An: Hans Buschmann Cc: pgsql-bugs@lists.postgresql.org Betreff: Re: BUG #17842: Adding a qual to a working query gets bogus syntax= error PG Bug reporting form writes: > During refactoring our application I got a bogus syntax error when adding= a > simple where-clause to a working query. My guess is that this: > ('0'||split_part(split_part(nline,'(',2),')',1))::smallint as nlen sometimes produces a string that fails to cast to smallint, which you accidentally don't notice because it never gets evaluated for troublesome values of nline --- until you add the "nlen > 0" condition. There isn't anything in your query that prevents that from being evaluated fairly early. This isn't a bug, or at least you have provided no data that would motivate (or indeed allow) anyone else to poke into it more closely. regards, tom lane --_000_dd6cfce2fca54079b1ad7cd044ee4b38nidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello Tom,


thanks for the quick response.


You are probably right with your assumption of a not convertible substri= ng, but some questions remain:


1. When the question runs succesfully, all lines are subject to the join= of qj (newcol_imp and oldcol_imp heve exact the same number of lines, matc= hing logically), but why does a limitation of the result set trigger the er= ror?


(There really may be some substrings from declarations of numeric(4,1) w= hich are not excluded properly from conversion to smallint.

The '0'||<rest> tries to handle an empty string)


2. Why is it reporting an syntax error (supposed at parse time) and not = a runtime error (supposed at execution time)


3. Why does an uncorrelated qual like and onum < 100 trigger an error= too? 

( I know that a limitation can change the choosen plan, but which one ca= nnot be seen because of the error.)


The behavior of having a succesfull full query, but getting the err= or with some quals seems to me very unintuitive.


PS: The exactness and correctness of this specific query is certainly no= t overall important for me, but I wanted to point out this very unexpected = behavior for some other users.


When I find some time, I'll try to provide some anonymized data.


Best regards

Hans Buschmann

Von: Tom Lane <tgl@sss= .pgh.pa.us>
Gesendet: Dienstag, 14. M=E4rz 2023 17:20
An: Hans Buschmann
Cc: pgsql-bugs@lists.postgresql.org
Betreff: Re: BUG #17842: Adding a qual to a working query gets bogus= syntax error
 
PG Bug reporting form <noreply@postgresql.org&g= t; writes:
> During refactoring our application I got a bogus syntax error when add= ing a
> simple where-clause to a working query.

My guess is that this:

> ('0'||split_part(split_part(nline,'(',2),')',1))::smallint as nlen

sometimes produces a string that fails to cast to smallint, which you
accidentally don't notice because it never gets evaluated for troublesome values of nline --- until you add the "nlen > 0" condition.&nb= sp; There isn't
anything in your query that prevents that from being evaluated fairly
early.

This isn't a bug, or at least you have provided no data that would
motivate (or indeed allow) anyone else to poke into it more closely.

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