[Bug]: Columnar Table Query Yields Incorrect/No Results with Dynamic Filter from Joined Row-Store Table

[jordi-ydteam]

What's wrong?

Bug Description

• Environment: PostgreSQL (version 16.8) utilizing a columnar storage extension (hydra columnar version 11.1-12) for certain tables.
• Problem Statement: Queries against a columnar table (e.g., [your_schema_name]."COLUMNAR_DATA_TABLE") fail to retrieve the expected dataset when a key identifier column (e.g., device_id_column) is filtered using a value dynamically derived from a separate, row-store table (e.g., [your_schema_name]."ROWSTORE_METADATA_TABLE") via standard SQL constructs like IN (subquery) or INNER JOIN.
• Expected Behavior: The query should retrieve all records from the columnar table that match the device_id_column (obtained from the ROWSTORE_METADATA_TABLE for a given name_column_in_metadata) and other specified filter conditions (e.g., a time range, portfolio_id_column).
• Actual Behavior: The query returns zero or a significantly incorrect number of rows from the columnar table, despite confirmation that:
  • The device_id_column value derived from ROWSTORE_METADATA_TABLE is correct.
  • The columnar table (COLUMNAR_DATA_TABLE) contains numerous records matching this device_id_column within the specified filter conditions (e.g., around [expected_record_count] records).
  • An identical query structure against a row-store version of the main data table works correctly.
  • Using a hardcoded literal value for the device_id_column in the filter against COLUMNAR_DATA_TABLE also works correctly.

Steps to Reproduce (Conceptual)

1. Setup:

   • ROWSTORE_METADATA_TABLE (row-store table): Contains mapping like (name_column_in_metadata TEXT, type_column_in_metadata TEXT, device_id_column TEXT PRIMARY KEY). Example: ('[example_name_value]', '[example_type_value]', '[example_device_id_value]').
   • COLUMNAR_DATA_TABLE (columnar table, possibly partitioned): Contains data like (device_id_column TEXT, "time" TIMESTAMP, portfolio_id_column TEXT, type_column_in_metadata TEXT, ...kpi_columns...). Contains [expected_record_count] records for device_id_column = '[example_device_id_value]' within a specific time range.

2. Failing Query (using IN (subquery)):

   SELECT COUNT(*) -- or other aggregations/columns
   FROM "[your_schema_name]"."COLUMNAR_DATA_TABLE" AS t
   WHERE t."portfolio_id_column" = '[example_portfolio_id_value]'
     AND t."type_column_in_metadata" = '[example_type_value]'
     AND t."time" >= '2024-01-01 00:00:00'
     AND t."time" <= '2024-02-01 00:00:00' -- Or other relevant end time
     AND t."device_id_column" IN (SELECT meta."device_id_column"
                                  FROM "[your_schema_name]"."ROWSTORE_METADATA_TABLE" meta
                                  WHERE meta."type_column_in_metadata" = '[example_type_value]'
                                    AND meta."name_column_in_metadata" = '[example_name_value]');
   -- Based on EXPLAIN ANALYZE, scans on COLUMNAR_DATA_TABLE partitions yield 0 or very few rows.

3. Failing Query (using INNER JOIN):

   SELECT COUNT(*) -- or other aggregations/columns
   FROM "[your_schema_name]"."COLUMNAR_DATA_TABLE" AS t
   INNER JOIN "[your_schema_name]"."ROWSTORE_METADATA_TABLE" AS meta ON t."device_id_column" = meta."device_id_column"
   WHERE t."portfolio_id_column" = '[example_portfolio_id_value]'
     AND t."type_column_in_metadata" = '[example_type_value]' -- Assuming this column is also in COLUMNAR_DATA_TABLE
     AND meta."type_column_in_metadata" = '[example_type_value]'
     AND meta."name_column_in_metadata" = '[example_name_value]'
     AND t."time" >= '2024-01-01 00:00:00'
     AND t."time" <= '2024-02-01 00:00:00'; -- Or other relevant end time
   -- Based on EXPLAIN ANALYZE, scans on COLUMNAR_DATA_TABLE partitions yield 0 or very few rows.

4. Working Query (hardcoded device_id_column):

   SELECT COUNT(*)
   FROM "[your_schema_name]"."COLUMNAR_DATA_TABLE" AS t
   WHERE t."portfolio_id_column" = '[example_portfolio_id_value]'
     AND t."type_column_in_metadata" = '[example_type_value]'
     AND t."time" >= '2024-01-01 00:00:00'
     AND t."time" <= '2024-02-01 00:00:00' -- Or other relevant end time
     AND t."device_id_column" = '[example_device_id_value]';
   -- Correctly finds approx. [expected_record_count] rows in COLUMNAR_DATA_TABLE (before grouping).

Key Debugging Findings

• The issue is specific to the columnar table (COLUMNAR_DATA_TABLE). An identical query structure against a row-store equivalent works as expected.
• EXPLAIN ANALYZE on failing queries showed that the scan on ROWSTORE_METADATA_TABLE correctly identified the single device_id_column, but the subsequent scan/join operation on COLUMNAR_DATA_TABLE partitions (using columnar_table.device_id_column = metadata_table.device_id_column in the condition) returned 0 rows.
• A detailed diagnostic query confirmed that the device_id_column value from ROWSTORE_METADATA_TABLE is bit-for-bit identical to the hardcoded literal. This diagnostic query, which used a scalar subquery lookup (WHERE columnar_table.device_id_column = (SELECT id_from_metadata_cte)), successfully counted all [expected_record_count] records in COLUMNAR_DATA_TABLE.

Workaround Implemented

The issue was successfully worked around by restructuring the query. The device_id_column from ROWSTORE_METADATA_TABLE is first selected into a Common Table Expression (CTE) (e.g., ResolvedIdCTE). Then, when querying COLUMNAR_DATA_TABLE, its device_id_column is filtered by comparing it to the device_id_column from this ResolvedIdCTE using an equality check against a scalar subquery.

Working structure example (for a simplified count):

WITH ResolvedIdCTE AS (
    SELECT "device_id_column" AS the_id_from_metadata
    FROM "[your_schema_name]"."ROWSTORE_METADATA_TABLE"
    WHERE "type_column_in_metadata" = '[example_type_value]' 
      AND "name_column_in_metadata" = '[example_name_value]'
    LIMIT 1 -- Ensures scalar result if combination isn't unique
)
SELECT COUNT(*)
FROM "[your_schema_name]"."COLUMNAR_DATA_TABLE" sc
WHERE sc."device_id_column" = (SELECT the_id_from_metadata FROM ResolvedIdCTE) -- Scalar subquery comparison
  AND sc."portfolio_id_column" = '[example_portfolio_id_value]'
  AND sc."type_column_in_metadata" = '[example_type_value]'
  AND sc."time" >= '2024-01-01 00:00:00'
  AND sc."time" <= '2024-02-01 00:00:00'; -- Or other relevant end time
-- This structure allows COLUMNAR_DATA_TABLE to retrieve the [expected_record_count] records.

Suspected Root Cause

The problem appears to stem from how the PostgreSQL query optimizer, or more specifically the execution engine for the columnar storage extension, handles predicates applied to columnar table scans when the filter value is a "dynamic" parameter derived from a JOIN with a row-store table (e.g., columnar_table.device_id_column = row_store_table.device_id_column) or an IN (subquery) list.
It seems the columnar scan operator fails to correctly use this derived parameter in conjunction with other filters. However, when the filter value is presented as a pre-resolved scalar (making the comparison similar to columnar_table.device_id_column = '[literal_value]'), the columnar scan behaves correctly. This suggests a potential inefficiency or issue in predicate pushdown or parameter handling within the columnar execution path for certain join types or IN list processing when interacting with values derived from row-store tables.