Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Alias scope issue #5138

Closed
PavelSafronov opened this issue Jan 12, 2023 · 0 comments · Fixed by dolthub/go-mysql-server#1595
Closed

Alias scope issue #5138

PavelSafronov opened this issue Jan 12, 2023 · 0 comments · Fixed by dolthub/go-mysql-server#1595
Labels
bug Something isn't working sql Issue with SQL

Comments

@PavelSafronov
Copy link
Contributor

The following SQL works in MySQL, but dolt returns the error Not unique table/alias: TableAlias.

use db;

CREATE TABLE a (id int, name varchar(10));
CREATE TABLE b (id int, name varchar(10));

EXPLAIN
SELECT *
FROM a
WHERE (EXISTS
         (SELECT *
          FROM b TableAlias
          WHERE (TableAlias.name = 'homer'
                 AND TableAlias.id = a.id)
          LIMIT 1)
       AND EXISTS
         (SELECT *
          FROM b TableAlias
          WHERE (TableAlias.name = 'marge'
                 AND TableAlias.id = a.id)
          LIMIT 1));

MySQL output:

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                              |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                                               |
|  1 | SIMPLE      | TableAlias | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; FirstMatch(a); Using join buffer (hash join)          |
|  1 | SIMPLE      | TableAlias | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; FirstMatch(TableAlias); Using join buffer (hash join) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+

Dolt error output:

error on line 6 for query 

EXPLAIN
SELECT *
FROM a
WHERE (EXISTS
         (SELECT *
          FROM b TableAlias
          WHERE (TableAlias.name = 'homer'
                 AND TableAlias.id = a.id)
          LIMIT 1)
       AND EXISTS
         (SELECT *
          FROM b TableAlias
          WHERE (TableAlias.name = 'marge'
                 AND TableAlias.id = a.id)
          LIMIT 1)): Not unique table/alias: TableAlias
Not unique table/alias: TableAlias
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working sql Issue with SQL
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants