roachtest: internal error: ordering column group contains non-equivalent columns

[cockroach-teamcity]

Note: This build has runtime assertions enabled. If the same failure was hit in a run without assertions enabled, there should be a similar failure without this message. If there isn't one, then this failure is likely due to an assertion violation or (assertion) timeout.

roachtest.unoptimized-query-oracle/disable-rules=half/rand-multi-region failed with artifacts on release-24.2 @ 5764366875adca70c3a3ff3d4d13aaf599bc53a7:

(query_comparison_util.go:416).runOneRoundQueryComparison: internal error while running optimized statement. 643 statements run: pq: internal error: ordering column group (6,19,30,37,45,58) contains non-equivalent columns (op limit)
test artifacts and logs in: /artifacts/unoptimized-query-oracle/disable-rules=half/rand-multi-region/run_1

Parameters:

• ROACHTEST_arch=amd64
• ROACHTEST_cloud=gce
• ROACHTEST_coverageBuild=false
• ROACHTEST_cpu=4
• ROACHTEST_encrypted=false
• ROACHTEST_runtimeAssertionsBuild=true
• ROACHTEST_ssd=0
  Help

See: roachtest README

See: How To Investigate (internal)

See: Grafana

This test on roachdash | Improve this report!

Jira issue: CRDB-44268

[yuzefovich]

Reduced repro on a binary compiled with --crdb_test build tag:

CREATE TABLE t (
  col1_0  BOOL,
  col1_3  INT2,
  col1_5  INT4,
  col1_8  STRING AS (lower(CAST(col1_0 AS STRING))) VIRTUAL,
  col1_9  INT8,
  col1_10 INT8 AS (col1_5 + col1_3) VIRTUAL,
  PRIMARY KEY (col1_5)
);

  SELECT NULL
    FROM t AS t1
    JOIN t AS t2 ON t1.col1_5 = t2.col1_10 AND t1.col1_5 <= t2.col1_5
    JOIN t AS t3 ON t2.col1_10 = t3.col1_5 AND t2.col1_3 = t3.col1_5
ORDER BY t2.col1_0, t3.col1_5, t2.col1_10, t2.col1_8
   LIMIT 2;

[DrewKimball]

An equality that was used to simplify a limit ordering is replaced by PushSelectIntoInlinableProject with an equality against the computed column expression:

  - │    │              │         │         │    └── projections
  - │    │              │         │         │         └── col1_5:11 + col1_3:10 [as=col1_10:14, outer=(10,11), immutable]
  - │    │              │         │         └── filters
  - │    │              │         │              └── col1_3:10 = col1_10:14 [outer=(10,14), constraints=(/10: (/NULL - ]; /14: (/NULL - ]), fd=(10)==(14), (14)==(10)]
  + │    │              │         │         │    └── filters
  + │    │              │         │         │         └── col1_3:10 = (col1_5:11 + col1_3:10) [outer=(10,11), immutable, constraints=(/10: (/NULL - ])]
  + │    │              │         │         └── projections
  + │    │              │         │              └── col1_5:11 + col1_3:10 [as=col1_10:14, outer=(10,11), immutable]

The resulting expression can no longer prove that the equality between columns 14 and 19 holds, and we trip the assertion.