[Bug]: SQL Error [XX000]: ERROR: variable not found in subplan target list

[desertmark]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Other

What happened?

The issue was found to happend on timescaledb extension hypertables using
postgres 14 and above. We tested different version of timescaledb and
doesn't seem to take any effect. Only changing pg version from 13 to 14.

Tested using images from
https://hub.docker.com/r/timescale/timescaledb/tags

Combining DELETE with WHERE EXISTS () causes variable not found in subplan target list. Query fails and the data is not removed.

I expected the query to succeed and the data to be removed.

TimescaleDB version affected

at least 2.13.0, 2.14.2 and 2.11.0

PostgreSQL version used

14 and above

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Docker

What platform did you run on?

Other

Relevant log output and stack trace

SQL Error [XX000]: ERROR: variable not found in subplan target list

How can we reproduce the bug?

To reproduce the error the following compose was used:

version: "3.5"
services:
  works:
    image: timescale/timescaledb:2.14.2-pg13
    container_name: works
    restart: always
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
  
  doesntwork:
    image: timescale/timescaledb:2.14.2-pg14
    container_name: doesntwork
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password
    ports:
        - "5433:5432"



  doesntworkeither:
    image: timescale/timescaledb:2.14.2-pg16
    container_name: doesntworkeither
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password
    ports:
        - "5434:5432"



connect to "doesntwork" or "doesntworkeither" containers and run the
following SQL:



-- Create test table
CREATE TABLE public.test1(
    id text NOT null,
    created timestamptz NOT NULL
);
-- Make the table an timescaledb hypertable
SELECT create_hypertable('public.test1', 'created', chunk_time_interval =>
interval'4 days');
-- Insert some data
INSERT INTO public.test1 VALUES (1, '2024-01-01');
-- Verify data is correclty inserted
SELECT * FROM public.test1;
-- Run the query to trigger the error 
DELETE  FROM
   "public".test1
WHERE 
   (EXISTS (SELECT 1));

[konskov]

hi @desertmark , thank you for providing the steps to reproduce. I can confirm this happens on PG15 with the latest version of TimescaleDB, but not PG13.

[desertmark]

@konskov exactly, I reported it works up to pg 13, and starts failing from pg14 and above!

Maybe my docker-compose is confussing? the container named: "works" is named like that because there is no issue there, but then taking a look at the container named "doesntwork" and "doesntworkeither" you can reproduce the issue there.