Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-7651

exasolrouter: unqualified subquery column matching an earlier alias fails with “invalid select-list”

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • None
    • exapreprocessor
    • 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)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            allen.herrera Allen Herrera
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.