pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: fotonszekta@gmail.com
Subject: BUG #18188: wrong varnullingrels
Date: Thu, 09 Nov 2023 20:26:41 +0000
Message-ID: <18188-2e472071c919b3f1@postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 18188
Logged by: Gábor Szabó
Email address: fotonszekta@gmail.com
PostgreSQL version: 16.1
Operating system: windows, ubuntu
Description:
/*
wrong varnullingrels:
Needed a table function call, a LEFT JOIN with that table, and an other join
AFTER the left join.
(The first join must be LEFT, the second join can be any tpye. No data in
tables needed.)
Reproduced on:
Windows 11 + PostgreSQL 16.0, 16.1
Ubuntu 22.04.3 + PostgreSQL 16.1
Tested with no error:
Windows 11 + PostgrSQL 15.4, 15.5,
Ubuntu 22.04.3 + PostgreSQL 12.17, 12.16,
Ubuntu 20.04.6 + PostgreSQL 14.9
Error message from verbose log:
ERROR: XX000: wrong varnullingrels (b) (expected (b 3)) for Var 2/1
LOCATION: search_indexed_tlist_for_var, setrefs.c:2807
STATEMENT: SELECT
table_2.disp,
table_3.table_3_id
FROM table_1
LEFT JOIN table_2 ON table_2.table_2_id = table_1.table_2_id
LEFT JOIN table_3 ON table_3.table_3_id = table_1.table_3_id
script to reproduce:
*/
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.1
-- Dumped by pg_dump version 16.1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--
-- *not* creating schema, since initdb creates it
ALTER SCHEMA public OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: table_2; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.table_2 (
table_2_id integer NOT NULL
);
ALTER TABLE public.table_2 OWNER TO postgres;
--
-- Name: disp(public.table_2); Type: FUNCTION; Schema: public; Owner:
postgres
--
CREATE FUNCTION public.disp(p_table_2 public.table_2) RETURNS integer
LANGUAGE sql
AS $$
SELECT p_table_2.table_2_id
$$;
ALTER FUNCTION public.disp(p_table_2 public.table_2) OWNER TO postgres;
--
-- Name: table_1; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.table_1 (
table_1_id integer NOT NULL,
table_2_id integer,
table_3_id integer
);
ALTER TABLE public.table_1 OWNER TO postgres;
--
-- Name: table_3; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.table_3 (
table_3_id integer NOT NULL
);
ALTER TABLE public.table_3 OWNER TO postgres;
--
-- Data for Name: table_1; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY public.table_1 (table_1_id, table_2_id, table_3_id) FROM stdin;
\.
--
-- Data for Name: table_2; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY public.table_2 (table_2_id) FROM stdin;
\.
--
-- Data for Name: table_3; Type: TABLE DATA; Schema: public; Owner:
postgres
--
COPY public.table_3 (table_3_id) FROM stdin;
\.
--
-- Name: table_1 table_1_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY public.table_1
ADD CONSTRAINT table_1_pkey PRIMARY KEY (table_1_id);
--
-- Name: table_2 table_2_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY public.table_2
ADD CONSTRAINT table_2_pkey PRIMARY KEY (table_2_id);
--
-- Name: table_3 table_3_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY public.table_3
ADD CONSTRAINT table_3_pkey PRIMARY KEY (table_3_id);
--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
-- restore search_path:
SELECT pg_catalog.set_config('search_path', 'pg_catalog, public', false);
-- query:
SELECT
table_2.disp,
table_3.table_3_id
FROM table_1
LEFT JOIN table_2 ON table_2.table_2_id = table_1.table_2_id
LEFT JOIN table_3 ON table_3.table_3_id = table_1.table_3_id;
view thread (2+ messages) latest in thread
Message-ID: <18188-2e472071c919b3f1@postgresql.org>
Permalink: ../18188-2e472071c919b3f1@postgresql.org/
Also on: postgresql.org/message-id/18188-2e472071c919b3f1@postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, fotonszekta@gmail.com
Subject: Re: BUG #18188: wrong varnullingrels
In-Reply-To: <18188-2e472071c919b3f1@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox