Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1b5Y8g-0000iK-T8 for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2016 12:42:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1b5Y8g-0005pL-3E for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2016 12:42:18 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1b5Y7c-0004fY-3L for pgsql-sql@postgresql.org; Wed, 25 May 2016 12:41:12 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1b5Y7T-0000OK-E3 for pgsql-sql@postgresql.org; Wed, 25 May 2016 12:41:10 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id u4PCexM0035409; Wed, 25 May 2016 15:40:59 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Array casting in where : unexpected behavior To: pgsql-sql@postgresql.org References: <57454957.5070706@matrix.gatewaynet.com> From: Achilleas Mantzios Message-ID: <57459D5B.9040009@matrix.gatewaynet.com> Date: Wed, 25 May 2016 15:40:59 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------060209050501040303010705" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------060209050501040303010705 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hello David, On 25/05/2016 15:29, David G. Johnston wrote: > On Wed, May 25, 2016 at 2:42 AM, Achilleas Mantzios >wrote: > > dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id =rd.fdid and fd.dbtag='observer') as qry WHERE vals[1]::int=8844; > ERROR: invalid input syntax for integer: "19/10/2015" > dynacom=# > > > ​There order of evaluation in this query does not have to match the physical order. > > If you need to ensure the join and 'observer' filters are applied first you have two options. > > Make "qry" into a CTE. This, at least presently, introduces an optimization fence. > > Add "OFFSET 0" to the qry subquery: SELECT * FROM (SELECT ... FROM ... OFFSET 0) AS qry WHERE ...​ > > ​That too introduces an optimization fence. > > You could (I think) also do something like. > > WHERE CASE WHEN vals[1] !~ '^\d+$' THEN false ELSE vals[1]::int = ​8844 END > Thank you a lot for your thorough explanation, the above might be the best solution. > or, more obviously, since vals is a text array, WHERE vals[1] = '8844' > > > dynacom=# select vals1 FROM (select vals[1]::int as vals1 from fb_forms_defs fd, fb_reports_dets rd where fd.id =rd.fdid and fd.dbtag='observer') as qry WHERE vals1=8844; > ERROR: invalid input syntax for integer: "19/10/2015" > dynacom=# > > ^^^ is this normal? Isn't vals1 guaranteed to be integer since its the type defined in the subselect ? > > > This gets rewritten to: "WHERE vals[1]::int = 8844" and pushed down and so is susceptible to failing. I understand, but still this does not look conceptually right. > > However this seems to work : > > dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id =rd.fdid and fd.dbtag='observer') as qry WHERE vals[1] ~ E'^\\d+$' AND vals[1]::int=8844; > vals > -------- > {8844} > (1 row) > > > ​ This works just by chance.​ So I guess we must introduce an optimization fence by the means of a CTE or the use OFFSET. Since we must replicate this query on a 8.3 (well 100s of them actually) as well, I guess OFFSET is the way to go. > > ​David J.​ > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------060209050501040303010705 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Hello David,

On 25/05/2016 15:29, David G. Johnston wrote:
On Wed, May 25, 2016 at 2:42 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer') as qry WHERE vals[1]::int=8844;
ERROR:  invalid input syntax for integer: "19/10/2015"
dynacom=#

​There order of evaluation in this query does not have to match the physical order.

If you need to ensure the join and 'observer' filters are applied first you have two options.

Make "qry" into a CTE.  This, at least presently, introduces an optimization fence.

Add "OFFSET 0" to the qry subquery: SELECT * FROM (SELECT ... FROM ... OFFSET 0) AS qry WHERE ...​

​That too introduces an optimization fence.

You could (I think) also do something like.

WHERE CASE WHEN vals[1] !~ '^\d+$' THEN false ELSE vals[1]::int = ​8844 END

Thank you a lot for your thorough explanation, the above might be the best solution.
or, more obviously, since vals is a text array, WHERE vals[1] = '8844'


dynacom=# select vals1 FROM (select vals[1]::int as vals1 from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer') as qry WHERE vals1=8844;
ERROR:  invalid input syntax for integer: "19/10/2015"
dynacom=#

^^^ is this normal? Isn't vals1 guaranteed to be integer since its the type defined in the subselect ?


This gets rewritten to:  "WHERE vals[1]::int = 8844" and pushed down and so is susceptible to failing.
I understand, but still this does not look conceptually right.

However this seems to work :

dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer') as qry WHERE vals[1] ~ E'^\\d+$' AND vals[1]::int=8844;
  vals
--------
 {8844}
(1 row)

​ This works just by chance.​
 
So I guess we must introduce an optimization fence by the means of a CTE or the use OFFSET. Since we must replicate this query on a 8.3 (well 100s of them actually) as well, I guess OFFSET is the way to go.

​David J.​



-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------060209050501040303010705--