#1487 MySQL – Cannot execute query with bitwise OR expression

[Stanimir Stamenkov]

Using a MySQL session, trying the various bitwise operators:

SELECT 1 | 2;  -- 3

SELECT 3 & 1;  -- 1

SELECT 3 ^ 1;  -- 2

SELECT 1 << 1; -- 2

SELECT 2 >> 1; -- 1

SELECT 7 & ~3; -- 4

All except the first (bitwise OR) work. Trying the bitwise OR reports:

Error: 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 '|' at line 1
SQLState: 42000
ErrorCode: 1064
Error occurred in:
|

Executing it in Java program with the same driver doesn't manifest such problem.

Occurs with the latest snapshot and with the 4.2.0 release.

[Stanimir Stamenkov]

As far as I've traced the MysqlQueryTokenizer.breakApartNewLines() breaks the original SELECT 1 | 2; into three queries:

• SELECT 1
• |
• 2

Then _prefs.getProcedureSeparator() appears to be |.

[Stanimir Stamenkov]

Then _prefs.getProcedureSeparator() appears to be |.

O.k. found I could change that in the MySQL plugin preferences. Not sure if the given default is most appropriate.

[Gerd Wagner]

O.k. found I could change that in the MySQL plugin preferences. Not sure
if the given default is most appropriate.

The default
procedureSeparator = "|";
is defined in class MysqlPreferenceBean.

This resource
https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/
suggests that when writing stored procedures in MySQL it is common to redefine the delimiter to some rather arbitrary string. As far as I see there is no established default.

Although I see your point for now I'm not convinced to change the MySQL-Plugins default which exists for more than 10 years.