MySQL Bugs: #87051: Count query without key FROM keyword, executes successfully, returns 1 as count

Bug #87051	Count query without key FROM keyword, executes successfully, returns 1 as count
Submitted:	13 Jul 2017 7:26	Modified:	13 Jul 2017 8:00
Reporter:	Harsh Sharma	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	count, SELECT

Description:
Create a table questions having following fields,

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| question | varchar(100) | YES  |     | NULL    |       |
| userid   | int(11)      | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Inserted 5 records in it with Insert Query
Applied count query as,
     select count(*) from questions;
It resulted showing count as 5.

Now applied the count query as,
     select count(*) questions;
It resulted showing count as 1.

Expected result should not be generated as 1, and it must throw an error as invalid query.

How to repeat:
create database demo;

use demo;

create table questions (id integer, question varchar(100), userid integer);

insert into questions values (1,'What is your name ?', 111);

insert into questions values (2,'Where do you live ?', 222);

insert into questions values (3,'How much experience do you have ?', 222);

insert into questions values (4,'In which company are you working for ?', 222);

insert into questions values (5,'Who is your team lead ?', 111);

select count(*) questions;

Suggested fix:
Should return an Invalid query message something like below,

Invalid Query : select count(*) questions;
FROM Keyword Missing :         ^

Hello Harsh,

Thank you for the report.
This is duplicate of Bug #37305, please see Bug #37305

Thanks,
Umesh

MySQL allows the extension that if the FROM clause is omitted, the query behaves as if FROM DUAL is specified. DUAL is an embedded table with 1 row and no columns.

Thus we allow this statement:

  SELECT 1 questions;

as a synonym for

  SELECT 1 questions FROM DUAL;

Likewise, any set functions like COUNT are also allowed:

  SELECT COUNT(*) questions;

is equivalent to

  SELECT COUNT(*) questions FROM DUAL;

However, SELECT * is not allowed because there are no tables to expand the column list from:

  SELECT *;

and

  SELECT * FROM DUAL;

are both invalid queries.