sql: Planner drops WHERE condition if conditions are in a specific order

[lucacri]

Describe the problem

I noticed that the order of simple WHERE conditions ( field = X ) on the same table changes the result of the query, resulting in the field condition being ignored.

The query in question (simplified) is:

-- "bad" query
SELECT
  id
FROM
  clients
WHERE
  team_id = $1:::INT8
  AND (
      EXISTS(
        SELECT
          *
        FROM
          visits
        WHERE
          clients.id = visits.client_id
          AND clients.team_id = visits.team_id
          AND (
              EXISTS(
                SELECT
                  *
                FROM
                  class_models
                WHERE
                  visits.class_model_id = class_models.id
                  AND visits.team_id = class_models.team_id
                  AND is_virtual = $9:::BOOL
                  AND event_type = $11:::INT4
              )
            )
      )
    );

-- 27 results

Notice the order of the WHERE conditions inside the class_models subquery (is_virtual and event_type). This query runs as if the is_virtual condition wasn't present at all, giving wrong data in return.

I then tried to swap the fields, like so:

-- good query
SELECT
  id
FROM
  clients
WHERE
  team_id = $1:::INT8
  AND (
      EXISTS(
        SELECT
          *
        FROM
          visits
        WHERE
          clients.id = visits.client_id
          AND clients.team_id = visits.team_id
          AND (
              EXISTS(
                SELECT
                  *
                FROM
                  class_models
                WHERE
                  visits.class_model_id = class_models.id
                  AND visits.team_id = class_models.team_id
                  AND event_type = $9:::INT4
                  AND is_virtual = $12:::BOOL
              )
            )
      )
    )

-- 9 results

Even checking the planner on the dashboard, I can see that the condition is not applied in the "bad" query.

To Reproduce

I have the diagnostic zip files for both queries.

Expected behavior
The order of the where clauses shouldn't matter in the final result, and definitely the planner shouldn't drop one condition

Additional data / screenshots

Sent to Cockroach already

Environment:

• CockroachDB version: 21.1.9
• Server OS: GKE instances (containerOS)
• Client app: any

[lucacri]

I tried rolling back to 21.1.7 and the issue is still present

An hour or so after the rollback to 21.1.7, I can happily report that the issue is NOT present on 21.1.7.

[RaduBerinde]

Thanks for the report! We have been able to reproduce and we are investigating.

[mgartner]

@lucacri thanks again for the detailed report. We have a fix for the issue that should be included in the next 21.1 release, 21.1.10.

[lucacri]

That was fast :) thank you so much, @mgartner, @ajwerner and the whole team!