Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Error 1066: 'Not unique table/alias' in a join or HANDLER statement

ErrorERROR 1066 (42000): Not unique table/alias: 'orders'

Short answer

Error 1066 means a table name or alias appears twice in the same statement and the server cannot tell which one you mean. In a SELECT, the same table is listed twice without distinct aliases. With HANDLER, a second table of the same name is opened. Give each one its own alias.

  • Applies to MySQL 8.0, MySQL 8.4 LTS, MariaDB 11
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Take the name in the error message and count how many times it appears after FROM, JOIN and in HANDLER ... OPEN. Two or more is the cause.

    -- the name in the message is 'orders': find every use of it
    grep -n -i "orders" query.sql
  2. 2Match what you see, then jump to its fix

    1. 1The name in the message appears in two FROM or JOIN items with no distinct alias.The same table, or the same alias, is listed twice in a SELECTFix
    2. 2The failing statement is HANDLER ... OPEN, often for a table of the same name in another database.HANDLER opens a second table that has the same nameFix

What does the error look like?

  • ERROR 1066 (42000): Not unique table/alias: 'orders'
  • ERROR 1066 (42000): Not unique table/alias: 't1'

Cause 1 of 2

The same table, or the same alias, is listed twice in a SELECT

1Check

A table name or alias must identify one object in the statement. A self-join, or a table joined twice for two different roles, therefore needs an alias on each side. The manual's form is tbl_name AS alias_name, or tbl_name alias_name.

-- fails with 1066: 'orders' is listed twice, no aliases
SELECT orders.id
FROM orders
JOIN orders ON orders.id = orders.parent_id;

2Fix

Alias each occurrence once, with a different name, and refer to the column through that alias.

SELECT child.id, parent.id AS parent_id
FROM orders AS child
JOIN orders AS parent ON parent.id = child.parent_id;

3Verify

The statement runs and returns the pairs you expect.

EXPLAIN SELECT child.id FROM orders AS child JOIN orders AS parent ON parent.id = child.parent_id;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

HANDLER opens a second table that has the same name

1Check

HANDLER statements refer to an open table by its unqualified name. Opening db_new.t1 while another t1 is open collides; MariaDB's reference for this error shows exactly that case.

HANDLER db_old.t1 OPEN;
HANDLER db_new.t1 OPEN;

2Fix

Open the second table with an alias and use that alias in later HANDLER statements. The manual says references must use the alias, not the table name.

HANDLER db_new.t1 OPEN AS db_new_t1;
HANDLER db_new_t1 READ NEXT LIMIT 3;
HANDLER db_new_t1 CLOSE;

3Verify

READ returns rows and no 1066 appears.

HANDLER db_new_t1 READ FIRST;

Confirmed by mariadb.com

Did this fix it?

How do I stop it coming back?

  • Give every table in a multi-table query a short alias from the start, and use only the alias after that.
  • Keep aliases in one query distinct from every real table name used in it.
  • Close HANDLER tables when you finish with them.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.