BUG #9817: Broken index detection in case of functions with variadic array parameters

The following bug has been logged on the website:

Bug reference: 9817
Logged by: Dmitry Ryabov
Email address: dmitry-ryabov@mail.ru
PostgreSQL version: 9.3.4
Operating system: Any
Description:

-- HOW REPRODUCE:

CREATE TABLE test (
id INTEGER NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY(id)
);

-- just test function
CREATE FUNCTION test_value_func (
text,
variadic text []
)
RETURNS bigint AS
$body$
select sum(position(unnest in $1)) from unnest($2)
$body$
LANGUAGE 'sql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER;

truncate test;

insert into test select generate_series(1, 1000000), md5(random()::text);

CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, 'a', 'b')));

-- index scan used
select * from test where test_value_func(value, 'a', 'b') > 60;

DROP INDEX test_idx;

-- but after restore from backup index created like this
CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])));

-- BUG! index scan isn't used
select * from test where test_value_func(value, 'a', 'b') > 60;
-- index scan used
select * from test where test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])>60

DROP INDEX test_idx;

CREATE INDEX test_idx ON public.test
USING btree ((test_value_func(value, 'a', 'b')));

-- index scan used
select * from test where test_value_func(value, 'a', 'b') > 60;
-- BUG! index scan isn't used
select * from test where test_value_func(value, VARIADIC ARRAY['a'::text,
'b'::text])>60

-- affected version: 9.3.4
-- not affected version: 9.1.13 - index scan used in both cases

DROP TABLE test;
DROP FUNCTION test_value_func(text, variadic text []);

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs