Port Bug Report: select of array columns

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Michael Tomuschat
Your email address : michael(at)knoto(dot)tuebingen(dot)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:
---------

--------------------------------------------------------------------------