sql: streaming group by with redundant ordering can produce incorrect results

[mgartner]

If the SimplifyRootOrdering rule does not fire and a query has an ORDER BY clause with the same column and direction listed consecutively, an incorrect result can be produced. Observe in the example below, where SimplifyRootOrdering has been commented out, that the two SELECT statements are equivalent, but produce difference results:

statement ok
CREATE TABLE t (
  a INT,
  b INT,
  c INT
)

statement ok
INSERT INTO t VALUES
  (NULL, NULL, 100),
  (NULL, NULL, NULL),
  (1, 10, 100),
  (NULL, NULL, 100),
  (NULL, 10, 100),
  (NULL, NULL, NULL)

query IIII
SELECT a, b, c, count(*) FROM t
GROUP BY b, a, c
ORDER BY c, b DESC
----
NULL  NULL  NULL  2
1     10    100   1
NULL  10    100   1
NULL  NULL  100   2

query IIII
SELECT a, b, c, count(*) FROM t
GROUP BY b, a, c
ORDER BY c, b DESC, b DESC
----
NULL  NULL  NULL  2
1     10    100   2
NULL  NULL  100   2

It seems like there is a bug in the execution engine that is hidden by the SimplifyRootOrdering rule. As far as I know, there is no danger of this bug ocurring in a production cluster because this rule should always fire for queries like the second select, making the query plans identical.

Jira issue: CRDB-17517

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!