BUG #9817: Broken index detection in case of functions with variadic array parameters - Mailing list pgsql-bugs

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 []);