Re: ERROR: invalid input syntax for integer: ""

Ben Madin <ben(at)ausvet(dot)com(dot)au> writes:
> On 2013-02-06, at 13:42 , Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The only part of this query that looks like it could possibly produce
>> that error is the res8.resultvalue-to-int cast:

>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 AND res8.del = false
>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = res8.resultvalue::int
>> ^^^^^^^^^^^^^^^^^^^^^
>> Presumably, there are some empty strings in results.resultvalue, and if
>> the query happens to try to compare one of them to rlu8.id, kaboom.

> Yes - this would be the case if it tried to match it against the resultvalue only - some of the values in the table are NULL, but not for this resulttypeid.

NULLs are not the problem (casting a NULL to anything is still a NULL).
The problem you've got is with empty strings, which are not at all the
same thing, even if Oracle can't tell the difference.

> So my understanding, working left to right was that the res.8 table rows should be limited to those rows which have a resulttypeid = 108.

Please recall the section in the fine manual where it points out that
WHERE clauses are not evaluated left-to-right. In the case at hand
I think the planner may be able to rearrange the join order, such that
the rlu8 join is done first. Now, having said that, I'm not real sure
why the res8.resulttypeid = 108 clause couldn't be applied at scan level
not join level. But you really need to be looking at EXPLAIN output
rather than theorizing about what order the clauses will be checked in.

> I'm really not sure what to do here.

You need to make sure the join clause is safe to evaluate for any data
present in the table. The first question I'd ask is why isn't
resultvalue of a numeric type to start with --- this whole problem
smells of crummy schema design. Or at least, why can't you use NULL
for the offending values instead of empty strings. If you really can't
fix the data representation, you need to complicate the join clause to
make it not try to convert non-integral strings to ints. One possible
solution is "nullif(res8.resultvalue, '')::int", if empty strings are
the only hazard. If they're not, you could do something with a CASE
expression using a regex test on the string...

regards, tom lane