Thread: BUG #10785: error if using x>n AND x
The following bug has been logged on the website:
Bug reference: 10785
Logged by: j.l.
Email address: lunda@bitmessage.ch
PostgreSQL version: 9.3.4
Operating system: Linux 64bit (ubuntu 12.04)
Description:
DOES WORK:
create index planet_osm_point_city_50k_index on planet_osm_point using
gist(way) where
(((capital IS NULL) OR (capital <> 'yes'::text)) AND
(place = ANY ('{city,town}'::text[])) AND
(population IS NOT NULL) AND (population <> ''::text) AND
(regexp_replace(population, '[., ]', '')::int > 50000));
DOES WORK:
create index planet_osm_point_city_50k_index on planet_osm_point using
gist(way) where
(((capital IS NULL) OR (capital <> 'yes'::text)) AND
(place = ANY ('{city,town}'::text[])) AND
(population IS NOT NULL) AND (population <> ''::text) AND
(regexp_replace(population, '[., ]', '')::int between 50000 and 99999));
DOES NOT WORK (ERROR: invalid input syntax for integer: "tower"):
create index planet_osm_point_city_50k_index on planet_osm_point using
gist(way) where
(((capital IS NULL) OR (capital <> 'yes'::text)) AND
(place = ANY ('{city,town}'::text[])) AND
(population IS NOT NULL) AND (population <> ''::text) AND
(regexp_replace(population, '[., ]', '')::int >= 50000) AND
(regexp_replace(population, '[., ]', '')::int < 100000));
lunda@bitmessage.ch writes:
> DOES NOT WORK (ERROR: invalid input syntax for integer: "tower"):
> create index planet_osm_point_city_50k_index on planet_osm_point using
> gist(way) where
> (((capital IS NULL) OR (capital <> 'yes'::text)) AND
> (place = ANY ('{city,town}'::text[])) AND
> (population IS NOT NULL) AND (population <> ''::text) AND
> (regexp_replace(population, '[., ]', '')::int >= 50000) AND
> (regexp_replace(population, '[., ]', '')::int < 100000));
You haven't provided nearly enough information for anyone else to
reproduce this problem, but I suppose that the error occurs because
you have some non-numeric entries in "population". You'll need to
code those tests more defensively, perhaps with a CASE that checks
that the string is numeric before trying to cast it.
Probably the "working" examples accidentally fail to fail because
of the order the planner chooses to apply the qual expressions in.
It's not a bug that they're not necessarily done left-to-right; see
http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
regards, tom lane
Re: BUG #10785: error if using x>n AND x
From
BM-2cUR7NEMQCynXF7KDxiMP1J9CMpmKA5Bd5@bitmessage.ch
Date:
Yes, you're right. It is my fault. The "population" column contains non-n=
umeric data.=0AThanks for reply.=0AOn Fri, Jun 27, 2014 at 20:18, "Tom La=
ne" wrote:lunda@bitmessage.ch (mailto:lunda@bitmessage.ch) writes:=0ADOE=
S NOT WORK (ERROR: invalid input syntax for integer: "tower"):=0Acreate =
index planet_osm_point_city_50k_index on planet_osm_point using=0Agist(wa=
y) where =0A(((capital IS NULL) OR (capital 'yes'::text)) AND =0A(place =
=3D ANY ('{city,town}'::text[])) AND =0A(population IS NOT NULL) AND (pop=
ulation ''::text) AND =0A(regexp_replace(population, '[., ]', '')::int >=
=3D 50000) AND =0A(regexp_replace(population, '[., ]', '')::int < 100000)=
);=0A=0AYou haven't provided nearly enough information for anyone else to=
=0Areproduce this problem, but I suppose that the error occurs because=0A=
you have some non-numeric entries in "population". You'll need to=0Acode=
those tests more defensively, perhaps with a CASE that checks=0Athat the=
string is numeric before trying to cast it.=0A=0AProbably the "working" =
examples accidentally fail to fail because=0Aof the order the planner cho=
oses to apply the qual expressions in.=0AIt's not a bug that they're not =
necessarily done left-to-right; see=0Ahttp://www.postgresql.org/docs/9.3/=
static/sql-expressions.html#SYNTAX-EXPRESS-EVAL (http://www.postgresql.or=
g/docs/9.3/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL)=0A=0A =
regards, tom lane