union by name doesn't seem to be working correctly

[Omega359]

Describe the bug

union by name seems to not be working as expected:

> create table t1 (x varchar(255), y varchar(255), z varchar(255));
0 row(s) fetched.
Elapsed 0.005 seconds.

> insert into t1 values ('a', 'b', 'c');
+-------+
| count |
+-------+
| 1     |
+-------+
1 row(s) fetched.
Elapsed 0.004 seconds.

> select t1.x, t1.y, t1.z from t1 union by name select t1.z, t1.y, t1.x, 'd' as zz from t1;
+---+---+---+------+
| x | y | z | zz   |
+---+---+---+------+
| c | b | a | d    |
| a | b | c | NULL |
+---+---+---+------+
2 row(s) fetched.
Elapsed 0.011 seconds.

> select x, y, z from t1 union all by name select z, y, x, 'd' as zz from t1;
+---+---+---+------+
| x | y | z | zz   |
+---+---+---+------+
| a | b | c | NULL |
| c | b | a | d    |
+---+---+---+------+
2 row(s) fetched.
Elapsed 0.007 seconds.

The same result if using 2 tables:

> create table t2 (x varchar(255), y varchar(255), z varchar(255));
0 row(s) fetched.
Elapsed 0.004 seconds.

> insert into t2 values ('a', 'b', 'c');
+-------+
| count |
+-------+
| 1     |
+-------+
1 row(s) fetched.
Elapsed 0.003 seconds.

> select x, y, z from t1 union all by name select z, y, x, 'd' as zz from t2;
+---+---+---+------+
| x | y | z | zz   |
+---+---+---+------+
| a | b | c | NULL |
| c | b | a | d    |
+---+---+---+------+
2 row(s) fetched.
Elapsed 0.007 seconds.

> select x, y, z from t1 union all by name select z, y, x from t2;
+---+---+---+
| x | y | z |
+---+---+---+
| a | b | c |
| c | b | a |
+---+---+---+
2 row(s) fetched.
Elapsed 0.005 seconds.

> select x, y, z from t1 union all by name select z, y, x from t2 order by x;
+---+---+---+
| x | y | z |
+---+---+---+
| a | b | c |
| c | b | a |
+---+---+---+
2 row(s) fetched.
Elapsed 0.007 seconds.

To Reproduce

See sql above.

Expected behavior

No response

Additional context

Original ticket for the feature: #14508

[Omega359]

fyi @rkrishn7

[rkrishn7]

take