Re: Array casting in where : unexpected behavior

On Wed, May 25, 2016 at 2:42 AM, Achilleas Mantzios <
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=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

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.

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.​

​David J.​