============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Michael Tomuschat
Your email address : michael@knoto.tuebingen.com
Category : runtime: back-end: SQL
Severity : serious
Summary: select of array columns
System Configuration
--------------------
Operating System : Linux 2.0.32
PostgreSQL version : 6.4
Compiler used : gcc
Hardware:
---------
Pentium 64 MB
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
create table test ( art_id int, fil_arr int[]);
insert into test values ( 1, '{1, 2, 3, 4}');
insert into test values ( 2, '{2, 3, 4, 5}');
insert into test values ( 3, '{3, 4, 5, 6}');
michael=> select * from test;
art_id|fil_arr
------+---------
1|{1,2,3,4}
2|{2,3,4,5}
3|{3,4,5,6}
(3 rows) -- is o.k.
michael=> select * from test where 1 = test.fil_arr[1];
art_id|fil_arr
------+---------
1|{1,2,3,4}
(1 row) -- shouldn't the array - index start with 0?
michael=> select test.fil_arr[1:2] from test;
fil_arr
-------
{1,2}
{2,3}
{3,4}
(3 rows) -- is o.k.
michael=> select fil_arr[1] from test; -- should be possible, too
ERROR: type name lookup of fil_arr failed
michael=> select * from test where 1 IN (test.fil_arr[1]); -- seems not to work in 'IN' statements
ERROR: parser: parse error at or near "."
AND: ( but not a bug): There seems to be no possibility to
check, if there is an value in an array
select * from test where 3 IN ( test.fil_arr[1:3]);
--------------------------------------------------------------------------
Test Case:
----------
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------
