Join with query broken

[UglyBlueCat]

Describe the bug A query containing a join with a query on one side return 'table does not exist' The same query works in other SQL clients as well as embedded in Java

To Reproduce Execute a query with a join where one side is a query, similar to this one:

SELECT jb.*, ja.ToCustomerID
FROM aajobdetails jb 
LEFT JOIN (
            SELECT * FROM aajoballocation 
            WHERE ID IN (
                        SELECT (CASE WHEN ToCustomerID = ? THEN MIN(ID) ELSE MAX(ID) END) AS ID 
                        FROM aajoballocation 
                        WHERE ((FromCustomerID = ?) OR (SubcontractorID = ?) OR (ToCustomerID = ?))  
                        AND Allocated != 'C' 
                        GROUP BY JobID
                        ) 
            ORDER BY JobID Desc
            ) ja ON jb.ID = ja.JobID 
WHERE jb.ID>0 
AND jb.UJID = ? 

Expected behavior The results of the query are returned. dbeaver-ce returns one line of data, which is correct

Screenshots

Application (please complete the following information):

• Version 0.5.0 (2022-03-12)
• Distribution: Source distribution 10.2.40 (x86_64 Linux) - installed from Ubuntu software

Desktop (please complete the following information):

Linux HP-Laptop-Ubuntu 5.13.0-35-generic #40~20.04.1-Ubuntu SMP Mon Mar 7 09:18:32 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

Additional context This is not urgent as I have another tool I can use, but I like Antares so I'm reporting it :)

[Fabio286]

Hi Robin, thanks for this report. I'm trying to reproduce this bug with no luck. Is the tab selected schema right? The issue occurs on MySQL or PostgreSQL? Could you provide me an sql dump with which I can reproduce the same conditions?

[raliqala]

Can you maybe post screenshots of the same query in antares and dbeaver. and what is ja

[UglyBlueCat]

I'm sorry; I'm unable to provide you with a dump.

ja is the results of the inner query

When joining tables you give them short codes to identify the fields in the first part of the select.

In this case jb.* is referring to all fields from aajobdetails and ja.ToCustomerID is referring to the CustomerID in the results returned by the inner query

I only logged this to help you, as I have other DB clients this works in, so you can close the case any time you like.

[Fabio286]

No problem Robin.

I made different tests with nested queries, also using alias for tables but I can't reproduce. I keep this issue open in case other people face this issue and can provide more clues.