Обсуждение: ERROR: invalid input syntax for integer: ""
G'day,
I hope to be shown to be an idiot, but we are receiving the message=20
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.
The source of our confusion is stemming from the fact that the offending =
line (being the join predicate that if removed allows the query to work) =
is comparing two values in two tables:
...
FROM reports rep
LEFT JOIN results res=20
ON res.reportid =3D rep.id <=3D=3D this line is causing the =
error to be returned
AND res.resulttypeid =3D 108=20
AND res.del =3D false
=85
I have included the full query executed by the function at the bottom of =
the email.
In the first it is an integer primary key, in the second a not null =
integer, as shown below:
Table "data.reports"
Column | Type | =
Modifiers =20
=
---------------+--------------------------+-------------------------------=
-----------------------
id | integer | not null default =
nextval('reports_id_seq'::regclass)
projectid | integer |=20
=85
Indexes:
"reports_pkey" PRIMARY KEY, btree (id)
Table "data.results"
Column | Type | =
Modifiers =20
=
---------------+--------------------------+-------------------------------=
-----------------------
id | integer | not null default =
nextval('results_id_seq'::regclass)
reportid | integer | not null
=85
Indexes:
"results_pkey" PRIMARY KEY, btree (id)
"results_del_btree" btree (del)
"results_idx_reportid" btree (reported)
My questions then are :
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?
cheers
Ben
The full query is :
{{{
SELECT rep.id, res8.reportid, =
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
LEFT JOIN results res8 ON res8.reportid =3D rep.id AND res8.resulttypeid =
=3D 108 AND res8.del =3D false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =3D =
res8.resultvalue::int
WHERE rep.del IS false AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150' AND spe.id =3D '9465' AND rlu8.id =3D =
'935';
}}}
--=20
Ben Madin
m : +61 448 887 220
e : ben@ausvet.com.au
On 02/05/2013 08:24 PM, Ben Madin wrote: > 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 postgresql9.1.6, but not on the production machine using 9.1.7. > > The source of our confusion is stemming from the fact that the offending line (being the join predicate that if removedallows the query to work) is comparing two values in two tables: > > ... > 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 > > > > My questions then are : > > 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 machinelast night) running 9.1.6, is there any reason it wouldn't work on the original machine - have there been any changesin 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 adrian.klaver@gmail.com
Thanks Adrian, On 2013-02-06, at 12:40 , Adrian Klaver <adrian.klaver@gmail.com> wrote: > 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. > Have you looked at the values for id and reportid to see if they look = alright? Yes, those I've scanned seem OK - I've tried some tests looking for = nulls etc. (there are several million reports, and about 30 times as = many results, hence I'm not posting a reproducible example - yet!) cheers Ben --=20 Ben Madin m : +61 448 887 220 e : ben@ausvet.com.au
On 02/05/2013 08:24 PM, Ben Madin wrote:
> 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
postgresql9.1.6, but not on the production machine using 9.1.7.
>
> The source of our confusion is stemming from the fact that the offending line (being the join predicate that if
removedallows the query to work) is comparing two values in two tables:
>
>
> Ben
>
> The full query is :
>
> {{{
> 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';
> }}}
>
Follow up questions:
1) Where is this query being run from?
2) Why are the integers at the end of the query quoted?
--
Adrian Klaver
adrian.klaver@gmail.com
On 02/05/2013 08:52 PM, Ben Madin wrote: > Thanks Adrian, > > On 2013-02-06, at 12:40 , Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> 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 wellon the development machine, I've sent a further email. Oops, I swapped machines. > cheers > > Ben > > -- Adrian Klaver adrian.klaver@gmail.com
Thanks Adrian,
On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 02/05/2013 08:24 PM, Ben Madin wrote:
>> The full query is :
>>=20
>> {{{
>> SELECT rep.id, res8.reportid, =
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist
>> FROM reports rep
>> LEFT JOIN users u ON rep.link =3D u.id
>> LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode
>> LEFT JOIN species spe ON rep.species::text like spe.speccode::text =
AND spe.synonym =3D 0
>> LEFT JOIN results res8 ON res8.reportid =3D rep.id AND =
res8.resulttypeid =3D 108 AND res8.del =3D false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =
=3D res8.resultvalue::int
>> WHERE rep.del IS false AND rep.projectid =3D 51
>> AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150' AND spe.id =3D '9465' AND rlu8.id =3D =
'935';
>> }}}
>>=20
>=20
>=20
> Follow up questions:
>=20
> 1) Where is this query being run from?
It is meant to be being executed in a pl/pgsql function as part of a =
loop - the rep.id is then used to return the corresponding rows. This =
function is working on the dev machine. The query I have appended is =
produced in the function as below. When I throw the query as above at =
the psql command line, it works on the dev machine. (but not on the =
production box). The final part of the function looks like :
{{{
RAISE NOTICE 'The final query is : %', querystring;
FOR repid, dist IN EXECUTE querystring LOOP
RETURN QUERY SELECT reportid, surname, city, state, postcode, =
telephone, species, breed, status, dist FROM data_view WHERE reportid =3D =
repid;
END LOOP;
RETURN;
}}}
> 2) Why are the integers at the end of the query quoted?
I have quote_literal(speciesid) etc, even thought it is an int parameter =
to the query. I realise it isn't needed, but it was working on one. =
FWIW, I have tried it without all of the quotes (manually removed), but =
it doesn't make any difference to the result.
cheers
Ben
--=20
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.
On 02/05/2013 09:01 PM, Ben Madin wrote:
> Thanks Adrian,
>
> On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> On 02/05/2013 08:24 PM, Ben Madin wrote:
>>> The full query is :
>>>
>>> {{{
>>> 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'
ANDspe.id = '9465' AND rlu8.id = '935';
>>> }}}
>>>
>>
>>
>> Follow up questions:
>>
>> 1) Where is this query being run from?
>
> It is meant to be being executed in a pl/pgsql function as part of a loop - the rep.id is then used to return the
correspondingrows. This function is working on the dev machine. The query I have appended is produced in the function
asbelow. When I throw the query as above at the psql command line, it works on the dev machine. (but not on the
productionbox). The final part of the function looks like :
So to be clear the querystring below is the query shown above?
That still leaves the issue of why it fails in psql?
At this point I am stumped. Maybe someone else has an idea. There is
always the outside chance I get a light bulb moment, don't hold your
breath:)
>
> {{{
> RAISE NOTICE 'The final query is : %', querystring;
>
> FOR repid, dist IN EXECUTE querystring LOOP
> RETURN QUERY SELECT reportid, surname, city, state, postcode, telephone, species, breed, status, dist FROM
data_viewWHERE reportid = repid;
> END LOOP;
> RETURN;
> }}}
>
> cheers
>
> Ben
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 02/05/2013 09:01 PM, Ben Madin wrote:
> Thanks Adrian,
>
> On 2013-02-06, at 12:52 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>> On 02/05/2013 08:24 PM, Ben Madin wrote:
>>> The full query is :
>>>
>>> {{{
>>> 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'
ANDspe.id = '9465' AND rlu8.id = '935';
>>> }}}
>>>
>>
>>
>> Follow up questions:
Dim bulb moment.
What happens if you run a simplified version of the query?
One that just LEFT JOINS reports to results ON reportid=rep.id.
>
> cheers
>
> Ben
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
Ben Madin <ben@ausvet.com.au> writes:
> 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
that error is the res8.resultvalue-to-int cast:
> 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
Adrian,
On 2013-02-06, at 13:33 , Adrian Klaver <adrian.klaver@gmail.com> wrote:
> Dim bulb moment.
>=20
> What happens if you run a simplified version of the query?
>=20
> One that just LEFT JOINS reports to results ON reportid=3Drep.id.
A fair question - it only makes it more confusing :
{{{
SELECT rep.id, res8.reportid
FROM reports rep=20
LEFT JOIN results res8 ON res8.reportid =3D rep.id AND res8.resulttypeid =
=3D 108 AND res8.del =3D false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =3D =
res8.resultvalue::int
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND rlu8.id =3D '935';
}}}
works perfectly well - so does:
{{{
SELECT rep.id, =
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150'=20
AND spe.id =3D '9465';
}}}
but the combination only works on the older db=85
{{{
SELECT rep.id, res.reportid,=20
=
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point::geometry)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
LEFT JOIN results res ON res.reportid =3D rep.id AND res.resulttypeid =3D =
108 AND res.del =3D false
LEFT JOIN resultlookup rlu ON rlu.resulttypesid =3D 108 AND rlu.id =3D =
res.resultvalue::int
WHERE rep.del IS false=20
AND rep.projectid =3D 51=20
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < '150'=20
AND spe.id =3D '9465'=20
AND rlu.id =3D '935';
ERROR: invalid input syntax for integer: ""
}}}
cheers
Ben
--=20
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.
Thanks Tom,
On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.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 =3D rep.id AND =
res8.resulttypeid =3D 108 AND res8.del =3D false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND rlu8.id =
=3D 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 =3D 108. These =
all have numeric values, vis :
select distinct resultvalue from results where resulttypeid =3D 108 =
order by resultvalue; =
=20
resultvalue=20
-------------
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=85 but it seems that the rlu8.id =3D 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.=20
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=85 it works(the commented code below =
being the change.)=20
SELECT rep.id, --res.reportid,=20
=
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741=
C0', post.the_point)/1000) as dist=20
FROM reports rep=20
LEFT JOIN users u ON rep.link =3D u.id=20
LEFT JOIN postcodes post ON u.postcode::integer =3D post.postcode =20
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND =
spe.synonym =3D 0 =20
--LEFT JOIN results res ON rep.id =3D res.reportid AND res.resulttypeid =
=3D 108 AND res.del is false
WHERE rep.del IS false AND rep.projectid =3D 51
AND round(st_distance_sphere( =
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', =
post.the_point)/1000) < 150
AND spe.id =3D 9465;
I'm really not sure what to do here.
cheers
Ben
--=20
Ben Madin
m : +61 448 887 220
e : ben@ausvet.com.au
On 02/06/2013 01:28 AM, Ben Madin wrote: > Thanks Tom, > > On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.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 areNULL, but not for this resulttypeid. > > I'm really not sure what to do here. When I run into issues like this I start over from scratch and build the query up a layer at a time using the minimum information necessary. Once I get the 'simple' case working then I start adding in more information. > > cheers > > Ben > > > > -- Adrian Klaver adrian.klaver@gmail.com
Ben Madin <ben@ausvet.com.au> writes:
> On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.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
areNULL, 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
Thank you to all for your help on this problem. I've summarised the =
resolution in the hope that it might help someone else.
With all the advice I have gone forward and discovered that the issue =
related to a postcode anomaly. A client had provided a new postbox =
postcode (the application normally prevents this for postboxes because =
we can't locate properties, but because it was new - and our database =
didn't have a record of it - this check had been bypassed). This meant =
there was no geometry associated with the postcode, and when it was =
joined to the postcodes table (which has varchars for postcodes because =
in Australia some postcodes begin with 0, which needs to be printed to =
allow automatic sorting) during the distance checking function (which =
looked like this in pl/pgsql):
round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000)
If a geometry is NULL, the st_distance_sphere postgis function returned =
NULL.=20
NULL/1000 =3D NULL
round(NULL) =3D NULL
AND NULL < 150 =3D NULL
so the predicate probably looks like:
AND round(NULL/1000) < 150
AND NULL, so no row returned.
This can't be used in a comparison, so to get around this (thanks Tom) :
coalesce(round(st_distance_sphere( '$$ || pccentre || $$', =
post.the_point)/1000),0) < $$ || quote_literal(distance);
which works - problem no longer being seen.
My final throught relates to the message:
ERROR: invalid input syntax for integer: ''
The '' suggests (I don't think I was the only one who thought this) that =
we were looking for a string comparison. I guess the NULL value is in =
there between the quotes.
cheers
Ben
On 2013-02-07, at 00:01 , Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ben Madin <ben@ausvet.com.au> writes:
>> On 2013-02-06, at 13:42 , Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The only part of this query that looks like it could possibly =
produce
>>> that error is the res8.resultvalue-to-int cast:
>=20
>>>> LEFT JOIN results res8 ON res8.reportid =3D rep.id AND =
res8.resulttypeid =3D 108 AND res8.del =3D false
>>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid =3D 108 AND =
rlu8.id =3D 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.
>=20
>> 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.
>=20
> 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.
>=20
>> So my understanding, working left to right was that the res.8 table =
rows should be limited to those rows which have a resulttypeid =3D 108.
>=20
> 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 =3D 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.
>=20
>> I'm really not sure what to do here.
>=20
> 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...
>=20
> regards, tom lane
--=20
Ben Madin
t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au
AusVet Animal Health Services
P.O. Box 5467
Broome WA 6725
Australia
AusVet's website: http://www.ausvet.com.au
This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.
On 02/06/2013 11:50 PM, Ben Madin wrote:
> Thank you to all for your help on this problem. I've summarised the resolution in the hope that it might help someone
else.
>
> With all the advice I have gone forward and discovered that the issue related to a postcode anomaly. A client had
provideda new postbox postcode (the application normally prevents this for postboxes because we can't locate
properties,but because it was new - and our database didn't have a record of it - this check had been bypassed). This
meantthere was no geometry associated with the postcode, and when it was joined to the postcodes table (which has
varcharsfor postcodes because in Australia some postcodes begin with 0, which needs to be printed to allow automatic
sorting)during the distance checking function (which looked like this in pl/pgsql):
>
> round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000)
>
> If a geometry is NULL, the st_distance_sphere postgis function returned NULL.
>
> NULL/1000 = NULL
>
> round(NULL) = NULL
>
> AND NULL < 150 = NULL
>
> so the predicate probably looks like:
>
> AND round(NULL/1000) < 150
>
> AND NULL, so no row returned.
>
> This can't be used in a comparison, so to get around this (thanks Tom) :
>
> coalesce(round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000),0) < $$ || quote_literal(distance);
>
> which works - problem no longer being seen.
>
> My final throught relates to the message:
>
> ERROR: invalid input syntax for integer: ''
>
> The '' suggests (I don't think I was the only one who thought this) that we were looking for a string comparison. I
guessthe NULL value is in there between the quotes.
I tend to doubt that. For one NULL is a valid input for an integer and
two a NULL would not have quotes.
Going back over your original query I found this discrepancy, not sure
if it applies:
In the SELECT list you have:
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000)
in the AND clause:
round(st_distance_sphere(
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000)
Note the cast to geometry in the first but not the second call to
st_distance_sphere.
>
> cheers
>
> Ben
>
>
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com