MySQL Bugs: #41009: Simple select query cannot be formatted

Bug #41009	Simple select query cannot be formatted
Submitted:	25 Nov 2008 13:10
Reporter:	Kay Roepke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Enterprise Monitor: Web	Severity:	S3 (Non-critical)
Version:	2.0.0.7102	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	formatter, sqlparser

Description:
A simple select using SQL_NO_CACHE and IN() cannot be formatted properly.

How to repeat:
SELECT SQL_NO_CACHE room_id , rate_id , price1ok , release * ? , max_advance_res * ? , starts , ends , default_policy_group_id FROM B_Rate_Room_Directory WHERE room_id IN ( ? /* , ... */ ) AND active

Suggested fix:
.

Mark Matthews writes: 
Could we do some sort of graceful fail here? Strip whitespace/html from the formatted query, compare to unformatted, if lengths differ by "too much", assume formatting isn't faithful?

Mar 29, 2010 12:04:20 AM com.mysql.sqlparser.MySQLParser originalEmitErrorMessage
WARNING: line 1:1554 no viable alternative at input [@648,1554:1557='CASE',<20>,1:1554]
MySQL51Highlighter.g: node from line 0:0 required (...)+ loop did not match anything at input 'shop_rating = CASE WHEN ( p1 . flag > ? AND p2 . flag > ? AND pf . flag > ? ) THEN ? WHEN ( p1 . flag > ? AND p2 
. flag > ? ) THEN ? WHEN ( p2 . flag > ? AND pf . flag > ? ) THEN ? WHEN ( p1 . flag > ? AND pf . flag > ? ) THEN ? WHEN ( p2 . flag > ? ) THEN ? WHEN ( p1 . flag > ? ) THEN ? WHEN ( pf . flag > ? ) THEN ? EL
SE ? end'
Mar 29, 2010 12:04:21 AM com.mysql.sqlparser.MySQLParser originalEmitErrorMessage
WARNING: line 1:1454 no viable alternative at input [@516,1454:1457='case',<20>,1:1454]
MySQL51Highlighter.g: node from line 0:0 required (...)+ loop did not match anything at input 'shop_rating =  case when (p1.flag > 0 and p2.flag > 0 and pf.flag > 0) then 0 when (p1.flag > 0 and p2.flag > 0) 
then 1 when (p2.flag > 0 and pf.flag > 0) then 2 when (p1.flag > 0 and pf.flag > 0) then 3 when (p2.flag > 0) then 4 when (p1.flag > 0) then 5 when (pf.flag > 0) then 6 else 7 end'
Mar 29, 2010 2:31:25 PM com.mysql.sqlparser.MySQLParser originalEmitErrorMessage
WARNING: line 1:4 mismatched input [@2,4:13='autocommit',<607>,1:4] expecting TRANSACTION
MySQL51Highlighter.g: node from line 0:0 no viable alternative at input 'SET autocommit = ?'
Mar 29, 2010 2:31:26 PM com.mysql.sqlparser.MySQLParser originalEmitErrorMessage
WARNING: line 1:4 mismatched input [@2,4:13='autocommit',<607>,1:4] expecting TRANSACTION
MySQL51Highlighter.g: node from line 0:0 no viable alternative at input 'SET autocommit=1'