The following bug has been logged on the website:
Bug reference: 6496
Logged by: Luciano Barcellos
Email address: luciano@geocontrol.com.br
PostgreSQL version: 8.4.8
Operating system: Debian Squeeze (2.6.32-5-amd64)
Description:=20=20=20=20=20=20=20=20
-- Create and populate table
CREATE TABLE public.is_this_a_bug ( "name" VARCHAR(50) );
INSERT INTO public.is_this_a_bug VALUES ( '270I' );
INSERT INTO public.is_this_a_bug VALUES ( '270V' );
INSERT INTO public.is_this_a_bug VALUES ( '520I' );
INSERT INTO public.is_this_a_bug VALUES ( '520V' );
INSERT INTO public.is_this_a_bug VALUES ( '900I' );
INSERT INTO public.is_this_a_bug VALUES ( '900V' );
-- Query table: Ok
SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3) FROM public.is_this_a_bug
bug;
-- Wrong SQL. Reports no syntax error but yields unexpected data
SELECT s."name" FROM (SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3)
FROM public.is_this_a_bug bug) s;
-- Fixed SQL.
SELECT s."name" FROM (SELECT DISTINCT SUBSTRING(bug."name" FROM 1 FOR 3) AS
"name" FROM public.is_this_a_bug bug) s;