Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
sqlglot corner case:
An unqualified column inside a subquery fails on the exasolrouter (3311) when an earlier select-list alias has the same name. It works on MariaDB (3306). The preprocessor appears to rewrite SUM(total_fare) to SUM(LOCAL.total_fare), which Exasol rejects.
Expected: same result as MariaDB.
Actual: ERROR 3968: [Exasol Driver]invalid select-list in subselect
Workaround: qualify the column (SUM(f.total_fare)) or rename the alias. Both run correctly.
Potential cause: sqlglot’s Exasol generator (_add_local_prefix_for_aliases) walks into subqueries.
Reproduction:
USE <your_test_db>;
|
|
|
CREATE TABLE qa_local_orders (order_id INT PRIMARY KEY, amount DECIMAL(10,2)); |
CREATE TABLE qa_local_fares (order_id INT, total_fare DECIMAL(10,2)); |
|
|
INSERT INTO qa_local_orders VALUES (1, 80.00), (2, 70.00), (3, NULL); |
INSERT INTO qa_local_fares VALUES (1, 50.00), (1, 30.00), (2, 70.00); |
|
|
-- compare port 3306 vs 3311 |
SELECT
|
o.order_id,
|
o.amount AS total_fare, -- alias defined first
|
(SELECT SUM(total_fare) -- unqualified, means f.total_fare in MariaDB
|
FROM qa_local_fares f
|
WHERE f.order_id = o.order_id) AS fares_sum
|
FROM qa_local_orders o
|
ORDER BY o.order_id;
|
|
|
--3311 error |
ERROR 3968 (42000): [Exasol][Exasol Driver]invalid select-list in subselect [line 1, column 154] (Session: 1879490192293232640) |