SqlOptimiser.optimiseOrderBy() Not working as expected

To reproduce

1. create table y ( x int, ts timestamp) timestamp(ts)
2. create table y1 ( x int, ts timestamp) timestamp(ts)
3. create table y2 ( x int, ts timestamp) timestamp(ts)
4. select * from y left join y1 on y1.x = y.x INNER join (select LAST(ts) from y2) as y2 on y2.LAST = y1.ts

Current Query plan

SelectedRecord 
                            Hash Join Light 
                            condition: y2.LAST=y1.ts
                                    Hash Outer Join Light
                                      condition: y1.x=y.x
                                        DataFrame
                                            Row forward scan
                                            Frame forward scan on: y
                                        Hash
                                            DataFrame
                                                Row forward scan
                                                Frame forward scan on: y1
                                    Hash 
                                        Sort light lo: 1
                                          keys: [LAST desc]
                                            SelectedRecord
                                                DataFrame
                                                    Row forward scan
                                                    Frame forward scan on: y2

Expected Query Plan

SelectedRecord 
                            Hash Join Light 
                            condition: y2.LAST=y1.ts
                                    Hash Outer Join Light
                                      condition: y1.x=y.x
                                        DataFrame
                                            Row forward scan
                                            Frame forward scan on: y
                                        Hash
                                            DataFrame
                                                Row forward scan
                                                Frame forward scan on: y1
                                    Hash 
                                        Sort light lo: 1
                                          keys: [LAST desc]
                                            SelectedRecord
                                                DataFrame
                                                    Row backward scan
                                                    Frame backward scan on: y2

It should be a row and frame backward scan on y2
Ref test case -: https://github.com/questdb/questdb/blob/master/core/src/test/java/io/questdb/test/griffin/SqlOptimiserTest.java#L767

QuestDB version:

8

OS, in case of Docker specify Docker and the Host OS:

All

File System, in case of Docker specify Host File System:

NA

Full Name:

Siddharth Agarwal

Affiliation:

Zeta Suite

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response

Start with SqlOptimiser.optimiseOrderBy() and the referenced test in core/src/test/java/io/questdb/test/griffin/SqlOptimiserTest.java around line 767. Reproduce the query plan and compare the y2 scan direction with the expected plan; done means the test observes a row and frame backward scan on y2.