ERROR: invalid input syntax for integer: ""

[Adrian Klaver]

G'day,
I hope to be shown to be an idiot, but we are receiving the message
ERROR: invalid input syntax for integer: ""
when using a pl/pgsl function with some quite complex queries that seem to be working on a developer machine using postgresql 9.1.6, but not on the production machine using 9.1.7.
...
FROM reports rep
LEFT JOIN results res
ON res.reportid = rep.id <== this line is causing the error to be returned
AND res.resulttypeid = 108
AND res.del = false
…
Given that the join is between two integer columns, how could it be an invalid syntax for one of them?
Given the query is working on one machine (using a copy of the database downloaded and imported from the second machine last night) running 9.1.6, is there any reason it wouldn't work on the original machine - have there been any changes in casting that I didn't notice between 9.1.6 and 9.1.7?

I am not seeing anything obvious.
Is there a chance the import to the second machine did not go well?
Have you looked at the values for id and reportid to see if they look
alright?

cheers
Ben

[Adrian Klaver]

Thanks Adrian,

Post by Adrian Klaver
I am not seeing anything obvious.
Is there a chance the import to the second machine did not go well?

Actually, these queries work on the machine that the import was done to - but not the original. However, not all is well on the development machine, I've sent a further email.

Oops, I swapped machines.

cheers
Ben

[Tom Lane]

I hope to be shown to be an idiot, but we are receiving the message
ERROR: invalid input syntax for integer: ""

The only part of this query that looks like it could possibly produce

SELECT rep.id, res8.reportid, round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point::geometry)/1000) as dist
FROM reports rep
LEFT JOIN users u ON rep.link = u.id
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND spe.synonym = 0
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

^^^^^^^^^^^^^^^^^^^^^

WHERE rep.del IS false AND rep.projectid = 51
AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < '150' AND spe.id = '9465' AND rlu8.id = '935';
}}}

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.

The way that the error comes and goes depending on seemingly-irrelevant
changes isn't too surprising. Probably what's happening is that the
query plan changes around so that that test occurs earlier or later
relative to other join clauses.

regards, tom lane

[Tom Lane]

Post by Tom Lane
The only part of this query that looks like it could possibly produce

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