Something is wrong with ODRER BY clause in JOIN

[AnastasiiaSergienko]

Problem

I run across a strange issue - the same query returns the result rows in a different order when executing against Exasol Schema and Virtual schema. Looks like something is wrong with GROUP BY mapping. We need to investigate it.

How to reproduce

OPEN SCHEMA SOURCE_SCHEMA; CREATE OR REPLACE TABLE TL("L1" VARCHAR(5), "L2" VARCHAR(5)); CREATE OR REPLACE TABLE TR("R1" VARCHAR(5), "R2" VARCHAR(5), "R3" VARCHAR(5)); INSERT INTO TL VALUES('ON', 'L2_1'); INSERT INTO TL VALUES('ON', 'L2_2');

INSERT INTO TR VALUES('ON', 'R2_1', 'R3_1'); INSERT INTO TR VALUES('ON', 'R2_2', 'R3_2');

SELECT FROM TR JOIN TL ON TL.L1 = TR.R1 ORDER BY R2; SELECT FROM TL JOIN TR ON TL.L1 = TR.R1 ORDER BY L2; SELECT FROM (SELECT FROM TR JOIN TL ON TL.L1 = TR.R1 ORDER BY L2) nested JOIN TM ON TM.M1 = nested.R1 ORDER BY R2;

-- Virtual Schema

CREATE VIRTUAL SCHEMA MY_VIRTUAL_SCHEMA USING ADAPTER_SCHEMA.EXASOL_ADAPTER WITH CONNECTION_NAME = 'JDBC_CONNECTION' SCHEMA_NAME = 'SOURCE_SCHEMA';

OPEN SCHEMA VIRTUAL_SCHEMA_WITHOUT_SELECT_LIST_PROJECTION_CAPABILITY; SELECT FROM (SELECT FROM TR JOIN TL ON TL.L1 = TR.R1 ORDER BY L2) nested JOIN TM ON TM.M1 = nested.R1 ORDER BY R2; EXPLAIN VIRTUAL SELECT FROM (SELECT FROM TR INNER JOIN TL ON TL.L1 = TR.R1 ORDER BY L2) nested INNER JOIN TM ON TM.M1 = nested.R1 ORDER BY R2;

Result against Exasol Schema:

Result against Virtual SChema

EXPLAIN VIRTUAL:

• IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8, c3 VARCHAR(5) UTF8, c4 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TM"."M1", "TM"."M2", "TM"."M3", "TM"."M4" FROM "SOURCE_SCHEMA"."TM"'
• IMPORT INTO (c1 VARCHAR(5) UTF8, c2 VARCHAR(5) UTF8, c3 VARCHAR(5) UTF8, c4 VARCHAR(5) UTF8, c5 VARCHAR(5) UTF8) FROM JDBC AT JDBC_CONNECTION STATEMENT 'SELECT "TR"."R1", "TR"."R2", "TR"."R3", "TL"."L1", "TL"."L2" FROM "SOURCE_SCHEMA"."TR" INNER JOIN "SOURCE_SCHEMA"."TL" ON "TL"."L1" = "TR"."R1" ORDER BY "TL"."L2"'

[AnastasiiaSergienko]

Inner ORDER BY case is dropped intentionally according to the SQL standards, so this is expected behavior.