MySQL Bugs: #64135: Delimiter strips the next new line in a SQL statement

Bug #64135	Delimiter strips the next new line in a SQL statement
Submitted:	26 Jan 2012 15:09	Modified:	13 Dec 2012 16:06
Reporter:	Simon Martin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.15, 5.6.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	client, DELIMITER, new line

Description:
If you use "delimiter" at the beginning of a new line within the quotes of a SQL statement the client will remove the next new line:

mysql> CREATE TABLE test (t VARCHAR(255));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test VALUES ('A
    '> delimiter
    '> B');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------+
| t            |
+--------------+
| A
delimiterB |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT 'A
    '> delimiter Some Other Text
    '> B' AS bug;
+------------------------------+
| bug                          |
+------------------------------+
| A
delimiter Some Other TextB |
+------------------------------+
1 row in set (0.00 sec)

Exists in all 5.5 and 5.1 distributed clients on linux and windows which I have tested on.

It also exists if you try to load a SQL file through the client, for example:
$ mysql < my_file.sql

How to repeat:
CREATE TABLE test (t VARCHAR(255));

INSERT INTO test VALUES ('A
delimiter
B');

SELECT * FROM test;

SELECT 'A
delimiter Some Other Text
B' AS bug;

Thank you for the problem report. Verified with mysql-trunk on Mac OS X also:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, 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.

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test.test'
mysql> CREATE TABLE test (t VARCHAR(255));
Query OK, 0 rows affected (0.40 sec)

mysql> 
mysql> INSERT INTO test VALUES ('A
    '> delimiter
    '> B');
Query OK, 1 row affected (0.10 sec)

mysql> 
mysql> SELECT * FROM test;
+--------------+
| t            |
+--------------+
| A
delimiterB |
+--------------+
1 row in set (0.01 sec)

mysql> SELECT 'A
    '> delimiter Some Other Text
    '> B' AS bug;
+------------------------------+
| bug                          |
+------------------------------+
| A
delimiter Some Other TextB |
+------------------------------+
1 row in set (0.01 sec)

mysql> exit
macbook-pro:trunk openxs$ bin/mysql --version
bin/mysql  Ver 14.14 Distrib 5.6.5-m8, for osx10.5 (i386) using readline 5.1

Noted in 5.5.30, 5.6.10, 5.7.1 changelogs.

The mysql client could mishandle the delimiter command if it occurred
on a line during which mysql was looking for the end of a quoted
string.