MySQL Bugs: #87062: "SELECT NULL,* FROM table;" is syntax error

Bug #87062	"SELECT NULL,* FROM table;" is syntax error
Submitted:	13 Jul 2017 13:05	Modified:	13 Jul 2017 15:53
Reporter:	Duncan Ferguson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.54-0+deb8u1	OS:	Debian (Debian GNU/Linux 8.7 (jessie))
Assigned to:		CPU Architecture:	Any

Description:
"SELECT NULL FROM table" works

"SELECT NULL,* FROM table;" produces a syntax error

===
ERROR 1064 (42000) at line 22: 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 one' at line 1
===

However, "SELECT *,NULL FROM table;" works as expected

I am trying to do this as I am creating a new table based on an old one but adding a new auto-increment column as FIRST

How to repeat:
DROP DATABASE bugtest;
CREATE DATABASE bugtest;
USE bugtest;

CREATE TABLE one (
    something TEXT
) ENGINE MyISAM;

INSERT INTO one VALUES ('some text');
INSERT INTO one VALUES ('some text');
INSERT INTO one VALUES ('some text');
INSERT INTO one VALUES ('some text');
INSERT INTO one VALUES ('some text');
INSERT INTO one VALUES ('some text');

SELECT NULL FROM one;

# this one works:
SELECT *,NULL FROM one;

# this fails with a syntax error:
SELECT NULL,* FROM one;

# Need to continue to complete the following:

CREATE TABLE two LIKE one;

ALTER TABLE two ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

INSERT INTO two SELECT NULL,* FROM one;

Suggested fix:
"SELECT NULL,* FROM one;" should return all columns from the table with an empty first column

NOTE: the following does work:

"select *,NULL from one;"

so I have a potential workaround of having the auto-increment column as last in the table, but the syntax is inconsistent.

Hi!

This syntax is not supported as it is fully explained in the chapter 13.2.9 of our manual.

That chapter also contains a correct syntax that does exactly the same job as "SELECT NULL,* FROM ..".

Not a bug.

After hunting through the page several times and trying a few things, I can only guess you are referring to 

> Use of an unqualified * with other items in the select list may produce 
> a parse error. To avoid this problem, use a qualified tbl_name.* reference

Changing my sql to be 

SELECT NULL,table.* FROM table;

does work, but this was not particularly clear in the docs for a 'standard use case'.

I hope this report helps anyone else in a similar situation.

Thanks.