Bug #85624 SQL syntax checking is not strict
Submitted: 25 Mar 2017 16:31 Modified: 25 Apr 2017 17:05
Reporter: qiu wenhui Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.17 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: SQL syntax checking is not strict

[25 Mar 2017 16:31] qiu wenhui
Description:

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6121
Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(test)root@localhost [(none)]> use employees
Database changed
(test)root@localhost [employees]> begin;
Query OK, 0 rows affected (0.00 sec)

(test)root@localhost [employees]>  delete from employees.employees where emp_no ;
Query OK, 300024 rows affected (4.40 sec)

(test)root@localhost [employees]> rollback;
Query OK, 0 rows affected (1.76 sec)

(test)root@localhost [employees]>  select * from employees.employees where emp_no between 1001 and gender='F' limit 2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set, 1 warning (0.00 sec)

(test)root@localhost [employees]> 

###
These sql statements should't be executed and return the error message to clients;

How to repeat:
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

##
INSERT INTO `employees`.`employees`  VALUES ('10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26');
INSERT INTO `employees`.`employees`  VALUES ('10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21');
INSERT INTO `employees`.`employees`  VALUES ('10003', '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28');
INSERT INTO `employees`.`employees`  VALUES ('10004', '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01');
INSERT INTO `employees`.`employees`  VALUES ('10005', '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12');
INSERT INTO `employees`.`employees`  VALUES ('10006', '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02');
INSERT INTO `employees`.`employees`  VALUES ('10007', '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1989-02-10');
INSERT INTO `employees`.`employees`  VALUES ('10008', '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1994-09-15');
INSERT INTO `employees`.`employees`  VALUES ('10009', '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18');
INSERT INTO `employees`.`employees`  VALUES ('10010', '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1989-08-24');
INSERT INTO `employees`.`employees`  VALUES ('10011', '1953-11-07', 'Mary', 'Sluis', 'F', '1990-01-22');
INSERT INTO `employees`.`employees`  VALUES ('10012', '1960-10-04', 'Patricio', 'Bridgland', 'M', '1992-12-18');
INSERT INTO `employees`.`employees`  VALUES ('10013', '1963-06-07', 'Eberhardt', 'Terkki', 'M', '1985-10-20');
INSERT INTO `employees`.`employees`  VALUES ('10014', '1956-02-12', 'Berni', 'Genin', 'M', '1987-03-11');
INSERT INTO `employees`.`employees`  VALUES ('10015', '1959-08-19', 'Guoxiang', 'Nooteboom', 'M', '1987-07-02');
INSERT INTO `employees`.`employees`  VALUES ('10016', '1961-05-02', 'Kazuhito', 'Cappelletti', 'M', '1995-01-27');
INSERT INTO `employees`.`employees`  VALUES ('10017', '1958-07-06', 'Cristinel', 'Bouloucos', 'F', '1993-08-03');
INSERT INTO `employees`.`employees`  VALUES ('10018', '1954-06-19', 'Kazuhide', 'Peha', 'F', '1987-04-03');
INSERT INTO `employees`.`employees`  VALUES ('10019', '1953-01-23', 'Lillian', 'Haddadi', 'M', '1999-04-30');
INSERT INTO `employees`.`employees`  VALUES ('10020', '1952-12-24', 'Mayuko', 'Warwick', 'M', '1991-01-26');

###

(test)root@localhost [employees]>  select * from employees.employees where emp_no between 1001 and gender='F' limit 2;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set, 1 warning (0.00 sec)

(test)root@localhost [employees]> 
(test)root@localhost [employees]> delete from employees where emp_no;
Query OK, 20 rows affected (0.00 sec)
(test)root@localhost [employees]> select * from employees;
Empty set (0.00 sec)
(test)root@localhost [employees]> 

Suggested fix:
These sql statements should't be executed and return the error message to clients;
[25 Mar 2017 17:05] MySQL Verification Team
Thank you for the bug report looks duplicate/related to bug https://bugs.mysql.com/bug.php?id=85418?. Thanks.
[26 Apr 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".