agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Array casting in where : unexpected behavior
Date: Wed, 25 May 2016 15:40:59 +0300
Message-ID: <57459D5B.9040009@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAKFQuwZA1tm1TsSNAd6Yer=u1E_ZEc9KKG9soR6KjefJto73Ug@mail.gmail.com>
References: <57454957.5070706@matrix.gatewaynet.com>
	<CAKFQuwZA1tm1TsSNAd6Yer=u1E_ZEc9KKG9soR6KjefJto73Ug@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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 <mailto:achill@matrix.gatewaynet.com>>wrote:
>
>     dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id <http://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 <http://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 <http://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

view thread (3+ messages)

Message-ID: <57459D5B.9040009@matrix.gatewaynet.com>
Permalink:  ../57459D5B.9040009@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/57459D5B.9040009@matrix.gatewaynet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: Array casting in where : unexpected behavior
  In-Reply-To: <57459D5B.9040009@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox