unexpected union result

[XuHuaiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1),(2);

SELECT * FROM t1 UNION SELECT * FROM t1
  ORDER BY (SELECT a FROM t2 WHERE b = 12);

2. What did you expect to see? (Required)

mysql> SELECT * FROM t1 UNION SELECT * FROM t1
    ->   ORDER BY (SELECT a FROM t2 WHERE b = 12);
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

The result is unstable.

tidb> SELECT * FROM t1 UNION SELECT * FROM t1   ORDER BY (SELECT a FROM t2 WHERE b = 12);
+------+
| a    |
+------+
| 2    |
| 1    |
+------+
2 rows in set (0.00 sec)

tidb> SELECT * FROM t1 UNION SELECT * FROM t1   ORDER BY (SELECT a FROM t2 WHERE b = 12);
+------+
| a    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

7e15333 in master

[winoros]

This is a very rare corner case, so move the severity down.

[sylzd]

/assign

[sylzd]

This is a very rare corner case, so move the severity down.

It can be reproduced by common sql like this:

SELECT * FROM t1 union (select * from t1);

[sylzd]

concurrency of Hash Aggregation bring on this, should I make it sequential? @XuHuaiyu @winoros
when I set variable like this, it worked.

# >4.0
set global tidb_hashagg_partial_concurrency=1
set global tidb_hashagg_final_concurrency=1
# >=5.0
set global tidb_executor_concurrency=1

[sylzd]

For this issue, order does not work because field a not exits in t2, which makes it equals SELECT * FROM t1 UNION SELECT * FROM t1;

> SELECT a FROM t2 WHERE b = 12;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

[winoros]

The apply operator ruins the meaning of ORDER BY constant.