`Query Error: ambiguous column name` in `ORDER BY` clause

[JanJakes]

Given the following tables and data:

CREATE TABLE t1 (id INT, name TEXT);
CREATE TABLE t2 (t1_id INT, name TEXT);

INSERT INTO t1 (id, name) VALUES (1, "T1 A");
INSERT INTO t1 (id, name) VALUES (2, "T1 B");
INSERT INTO t2 (t1_id, name) VALUES (1, "T2 B");
INSERT INTO t2 (t1_id, name) VALUES (2, "T2 A");

The following queries error in both MySQL and SQLite:

SELECT t1.id  -- name column not specified here
FROM t1
JOIN t2 ON t2.t1_id = t1.id
ORDER BY name;
-- [MySQL]  Query Error: Column 'name' in order clause is ambiguous
-- [SQLite] Query Error: ambiguous column name: name

SELECT t1.name, t2.name  -- name column ambiguous for ORDER BY even in MySQL
FROM t1
JOIN t2 ON t2.t1_id = t1.id
ORDER BY name;
-- [MySQL]  Query Error: Column 'name' in order clause is ambiguous
-- [SQLite] Query Error: ambiguous column name: name

However, the following queries error in SQLite but not in MySQL:

SELECT t1.name  -- name column used for ORDER BY in MySQL
FROM t1
JOIN t2 ON t2.t1_id = t1.id
ORDER BY name;
-- [MySQL]  T1 A, T1 B
-- [SQLite] Query Error: ambiguous column name: name

SELECT t2.name  -- name column used for ORDER BY in MySQL
FROM t1
JOIN t2 ON t2.t1_id = t1.id
ORDER BY name;
-- [MySQL]  T2 A, T2 B
-- [SQLite] Query Error: ambiguous column name: name

Fix:
When we see a non-fully qualified column in an ORDER BY clause and the same column is fully qualified and non-ambiguous in the SELECT list, we can use the same full qualifier in the ORDER BY clause, and it should fix this issue.