The following bug has been logged on the website:
Bug reference: 18292
Logged by: Zuming Jiang
Email address: zuming.jiang@inf.ethz.ch
PostgreSQL version: 16.1
Operating system: Ubuntu 20.04
Description:
My fuzzer finds a suspicious alarm in Postgres 17devel: "ERROR: relation
"hobbies_r" does not exist". I report it as I think it might be an
unexpected error.
--- Set up database ---
create table exeet_t2 (vkey int4);
insert into exeet_t2 values (5);
CREATE TABLE person (name text);
insert into person values ('mike');
CREATE TABLE hobbies_r (name text, person text);
CREATE TABLE equipment_r (name text, hobby text);
INSERT INTO hobbies_r (name, person)
SELECT 'posthacking', p.name
FROM person* p;
INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking');
CREATE FUNCTION hobbies(person)
RETURNS setof hobbies_r
AS 'select * from hobbies_r where person = $1.name'
LANGUAGE SQL;
CREATE FUNCTION equipment(hobbies_r)
RETURNS setof equipment_r
AS 'select * from equipment_r where hobby = $1.name'
LANGUAGE SQL;
create schema simple1;
create function simple1.simpletarget(int) returns int language plpgsql
as $$begin return $1;end$$;
create function simpletarget(int) returns int language plpgsql
as $$begin return $1 + 100;end$$;
create or replace function simplecaller() returns int language plpgsql
as $$
declare
sum int := 0;begin
for n in 1..10 loop
sum := sum + simpletarget(n);if n = 5 then
set local search_path = 'simple1';end if;end loop;return sum;end$$;
The fuzzer generates a test case:
--- Test case ---
select
(SELECT (p.hobbies).equipment.name FROM ONLY person p order by 1 limit 1)
as c_11
from
(select
1 as c_0
from
exeet_t2 as ref_0
where '555' = ((select cast(simplecaller() as text)))) as subq_0;
--- Expected behavior ---
The test case should not trigger any error.
--- Actual behavior ---
The test case trigger an error:
ERROR: relation "hobbies_r" does not exist
--- Postgres version ---
Github commit: 15235abbf34f6b246f7681e88dccf8c2796a245b
Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
--- Platform information ---
Platform: Ubuntu 20.04
Kernel: Linux 5.4.0-147-generic
--- Note ---
The following query can be successfully executed:
select
(select name from hobbies_r order by 1 limit 1) as c_11
from
(select
1 as c_0
from
exeet_t2 as ref_0
where '555' = ((select cast(simplecaller() as text)))) as subq_0; ---
return {'posthacking'}