Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

After a series of tests it seems like the patch is not working.

I used
mariadb 10.6-enterprise
columnstore stable-23.10
DB was filled with 10gb dataset using burza.

Queries:
query without subselect:
SELECT l_orderkey
FROM LINEITEM
ORDER BY
RAND(),
EXP(SIN(l_quantity) * LOG(l_extendedprice + 1) * COS(l_discount + 1) + TAN(l_discount)),
POW(l_extendedprice, 7) / (l_discount + 1) + SQRT(ABS(l_quantity - l_discount)) + LOG(POW(l_quantity, 3) + 1)
LIMIT 10;

query with subselect:
SELECT l_orderkey
FROM (
SELECT l_orderkey
FROM LINEITEM
ORDER BY
RAND(),
EXP(SIN(l_quantity) * LOG(l_extendedprice + 1) * COS(l_discount + 1) + TAN(l_discount)),
POW(l_extendedprice, 7) / (l_discount + 1) + SQRT(ABS(l_quantity - l_discount)) + LOG(POW(l_quantity, 3) + 1)
) AS subquery
LIMIT 10;

I tried to use different values of columnstore_orderby_threads (e.g. SET SESSION columnstore_orderby_threads = 4), but strangely, did not see any effect of it. In htop all 16 cores are loaded and that does not change during query execution.

• results without patch:
  query without subselect:
  there is an even load on all cores the whole time until query finishes execution
  10 rows in set (6.310 sec)
  10 rows in set (6.282 sec)
  10 rows in set (6.207 sec)

query with subselect:
with subselect, for several(1-3) seconds there is an even load on all cores, but after that
there is a stable 100 load on only one core until query finishes execution,
all the other cores are between 0 and 2 percent load.
10 rows in set (3 min 7.105 sec)
10 rows in set (3 min 5.934 sec)
10 rows in set (2 min 58.059 sec)

• with patch: (I cherry picked the commit and rebuilt the code)

query without subselect:
there is an even load on all cores the whole time until query finishes execution
10 rows in set (7.110 sec)
10 rows in set (6.152 sec)
10 rows in set (6.201 sec)

query with subselect:
cores load looks the same as before patch
10 rows in set (3 min 3.735 sec)
10 rows in set (3 min 18.246 sec)

To avoid potential partial sort optimisation and maximize resource consumption of ORDER BY, LIMIT keyword was removed:

Queries:
query without subselect:
SELECT l_orderkey
FROM LINEITEM
ORDER BY
RAND(),
EXP(SIN(l_quantity) * LOG(l_extendedprice + 1) * COS(l_discount + 1) + TAN(l_discount)),
POW(l_extendedprice, 7) / (l_discount + 1) + SQRT(ABS(l_quantity - l_discount)) + LOG(POW(l_quantity, 3) + 1);

query with subselect:
SELECT l_orderkey
FROM (
SELECT l_orderkey
FROM LINEITEM
ORDER BY
RAND(),
EXP(SIN(l_quantity) * LOG(l_extendedprice + 1) * COS(l_discount + 1) + TAN(l_discount)),
POW(l_extendedprice, 7) / (l_discount + 1) + SQRT(ABS(l_quantity - l_discount)) + LOG(POW(l_quantity, 3) + 1)
) AS subquery;

10gb dataset was used.
To change columnstore_orderby_threads value I used
SET GLOBAL command, (SET GLOBAL columnstore_orderby_threads = 1; SET GLOBAL columnstore_orderby_threads = 16)

no subselect, with patch and columnstore_orderby_threads == 16
59986052 rows in set (1 min 4.815 sec)
59986052 rows in set (1 min 4.975 sec)

no subselect, with patch and columnstore_orderby_threads == 1
59986052 rows in set (3 min 32.506 sec)
59986052 rows in set (3 min 29.330 sec)

subselect, with patch and columnstore_orderby_threads == 16
59986052 rows in set (3 min 29.684 sec)
59986052 rows in set (3 min 35.516 sec)

subselect, with patch and columnstore_orderby_threads == 1
59986052 rows in set (3 min 33.364 sec)
59986052 rows in set (3 min 27.356 sec)

Change in columnstore_orderby_threads from 16 to 1 only affects processing time of request without subquery, so I am assuming that sorting in requests with subquery is still single threaded always

Fixed.

Tested on 10gb dataset:

set columnstore_orderby_threads=1

SELECT l_orderkey
FROM (
SELECT l_orderkey
FROM LINEITEM
ORDER BY 1 LIMIT 100
) AS subquery;

100 rows in set (1.431 sec)
100 rows in set (1.396 sec)
100 rows in set (1.438 sec)

After
set columnstore_orderby_threads=16

The same request performs as follows:

100 rows in set (0.347 sec)
100 rows in set (0.344 sec)
100 rows in set (0.349 sec)

Previously used test request was not showing much difference between orderby_threads == 1 and orderby_threads == 16 apparently due to math functions not being split to threads. Math functions were taking the most of processing time and difference in orderby was not apparent.