MySQL SELECT col, * FROM table

Question

I was wondering why this query returns error:

SELECT column_name, *
FROM INFORMATION_SCHEMA.COLUMNS;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM INFORMATION_SCHEMA.COLUMNS' at line 1

And other variations are working properly:

SELECT column_name, c.*
FROM INFORMATION_SCHEMA.COLUMNS c;

SELECT *, column_name
FROM INFORMATION_SCHEMA.COLUMNS;

SELECT c.*, column_name
FROM INFORMATION_SCHEMA.COLUMNS c;

db<>fiddle demo - MySQL

db<>fiddle demo - PostgreSQL

db<>fiddle demo - SQL Server

Answer 1

The duplicate link is basically the same question being asked here, for MySQL only. It does not provide an explanation for why SELECT *, col1 is allowed and SELECT col1, * is not. I was curious, and setup the following demo in MySQL 8:

WITH tb1 AS (
    SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3
),
tbl2 AS (
    SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3
)

SELECT tbl1.id, *
FROM tbl1
INNER JOIN tbl2
    ON tbl1.id = tbl2.id;

Look closely, I incorrectly refer to the first CTE as tbl1 in the query, when in fact is called tb1. It doesn't even get this far, and instead generates the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*

When I fix the CTE and query names to match, then I get the same error. In other words, the query is failing at the parsing step of the SELECT clause, and never even gets as far as worrying about table names, or to what the * is referring.

I am posting this answer because it adds information about how MySQL's parser is handling this case. The SELECT col1, * syntax is not failing because of any ambiguity in *, which makes sense because SELECT *, col1 works. Instead, it just seems that the parsing rules don't like this syntax, for whatever reason.