Thanks Tom,
On 2013-02-06, at 13:42 , Tom Lane <tgl@xxxxxxxxxxxxx> 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.
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. These all have numeric values, vis :
select distinct resultvalue from results where resulttypeid = 108 order by resultvalue;
resultvalue
-------------
932.0
933.0
934.0
935.0
936.0
937.0
938.0
939.0
940.0
3224.0
(10 rows)
and it should then be only these rows that are joined to the resultlookup table… but it seems that the rlu8.id = res8.resultvalue is being done first.
Can I prevent that? Using a subquery, or a some other approach.
> 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.
That might just be it - the query explain is different for the same query on each machine.
Just to confuse the issue, if I take the resultlookup table out completely, I still get the same error. So maybe it isn't that join at all that is raising the error.
If I take the results table out… it works(the commented code below being the change.)
SELECT rep.id, --res.reportid,
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/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 res ON rep.id = res.reportid AND res.resulttypeid = 108 AND res.del is false
WHERE rep.del IS false AND rep.projectid = 51
AND round(st_distance_sphere( '0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < 150
AND spe.id = 9465;
I'm really not sure what to do here.
cheers
Ben
--
Ben Madin
m : +61 448 887 220
e : ben@xxxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
