Обсуждение: Port Bug Report: select of array columns

============================================================================
                        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:
---------


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

> 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?

The array index is one-based. This is a documented feature, and is
consistant with other index numbering (such as arguments to substring())
in SQL92.

> michael=> select fil_arr[1] from test;    -- should be possible, too
> ERROR:  type name lookup of fil_arr failed

On my list of things to look at, though I haven't yet done it so if
someone else wants to pick it up it's available. Reported earlier by
Oliver.

                      - Tom