BUG #10785: error if using x>n AND x<m in where clause, BETWEEN n AND m works as supposed
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));
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Yes, you're right. It is my fault. The "population" column contains non-numeric data.
Thanks for reply.
On Fri, Jun 27, 2014 at 20:18, "Tom Lane" wrote:lunda@bitmessage.ch (mailto: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 (http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL)
regards, tom lane