Bug #96405 Syntax error with || when using PIPES_AS_CONCAT after upgrading to 8.0.17
Submitted: 1 Aug 2019 20:04 Modified: 26 Aug 2019 16:14
Reporter: Viktar Basharymau Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: parse error, PIPES_AS_CONCAT, regression

[1 Aug 2019 20:04] Viktar Basharymau
After upgrading from 8.0.16 to 8.0.17, some queries containing `||` stopped compiling even though PIPES_AS_CONCAT mode is set.

Here is an example:

SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'))
SELECT 'ab' LIKE 'a' || '%'

It works fine on 8.0.16, but fails with parse error on 8.0.17.

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

Note that adding brackets around `'a' || '%'` fixes the problem, but this is not an ideal solution because it requires software upgrade of the apps that use mysql, which can be costly and time-consuming.

Related issue: https://github.com/diesel-rs/diesel/issues/2133 

How to repeat:
Run the script below.

SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'))
SELECT 'ab' LIKE 'a' || '%'

Works in 8.0.16, broken in 8.0.17.
[2 Aug 2019 5:18] MySQL Verification Team
Hello Viktar,

Thank you for the report.

[26 Aug 2019 16:14] Paul DuBois
Posted by developer:
Fixed in 8.0.18.

Some statements containing || produced a parse error even with the
PIPES_AS_CONCAT SQL mode enabled.