Re: Array casting in where : unexpected behavior

Hello David,

On 25/05/2016 15:29, David G. Johnston wrote:
> On Wed, May 25, 2016 at 2:42 AM, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)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