SQL query wrong result (optimization does not see that queries are different in ORDER BY section) #8333
Description
select ( select number from numbers(100) order by number limit 1),
( select number from numbers(100) order by number desc limit 1)
┌─_subquery7─┬─_subquery8─┐
│ 0 │ 0 │
└────────────┴────────────┘
select ( select number from numbers(100) order by number desc limit 1),
( select number from numbers(100) order by number limit 1)
┌─_subquery11─┬─_subquery12─┐
│ 99 │ 99 │
└─────────────┴─────────────┘
Workarounds:
select ( select number from numbers(100) order by number limit 1),
( select number+0 from numbers(100) order by number desc limit 1)
┌─_subquery9─┬─_subquery10─┐
│ 0 │ 99 │
└────────────┴─────────────┘
select ( select number from numbers(100) order by number limit 1),
( select number from numbers(100) order by number desc limit 1+0)
┌─_subquery19─┬─_subquery20─┐
│ 0 │ 99 │
└─────────────┴─────────────┘
select ( select number from numbers(100) order by number limit 1),
( select number from numbers(100) order by -number limit 1)
┌─_subquery23─┬─_subquery24─┐
│ 0 │ 99 │
└─────────────┴─────────────┘
19.17.5.18, 19.19.1.1929 Not sure what optimization spoil it.
19.16.7.24
┌─_subquery7455046─┬─_subquery7455047─┐
│ 0 │ 99 │
└──────────────────┴──────────────────┘