MySQL Bugs: #46986: SQL formatter fails for SELECT ... WHERE .. NOT IN ( SELECT ... ) GROUP BY

Bug #46986	SQL formatter fails for SELECT ... WHERE .. NOT IN ( SELECT ... ) GROUP BY
Submitted:	28 Aug 2009 22:08	Modified:	18 Dec 2009 12:24
Reporter:	Carsten Segieth	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Enterprise Monitor: Web	Severity:	S3 (Non-critical)
Version:	2.0.6.7159	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The SQL formatter fails with this query:

SELECT * FROM merlintest . agentlist WHERE id NOT IN ( SELECT SUBSTR( `o` . value , ? , ? ) AS agentID FROM mem . dc_ng_string_now AS `o` JOIN mem . inventory_instance_attributes AS iia USING ( instance_attribute_id ) JOIN mem . inventory_instances USING ( instance_id ) JOIN ( SELECT inventory_attributes . attribute_id , attribute_name , instance_attribute_id , MAX( end_time ) AS _end_time FROM mem . dc_ng_string_now JOIN mem . inventory_instance_attributes USING ( instance_attribute_id ) JOIN mem . inventory_instances USING ( instance_id ) JOIN mem . inventory_attributes USING ( attribute_id ) WHERE attribute_name = ? GROUP BY instance_attribute_id ) AS `i` ON `o` . instance_attribute_id = `i` . instance_attribute_id AND `o` . end_time = `i` . _end_time ) ORDER BY id

It shows only this "formatted" query:

SELECT
*
FROM
merlintest.agentlist
WHERE
id NOT 
GROUP BY 
instance_attribute_id ASC

How to repeat:
use query shown above and choose 'formatted' on the QUAN page popup

Kay Roepke writes: 
Both SUBSTR (short version with multiple arguments) and IN recognition have some problems.
The SUBSTR failure is fixed, working on the subselect issue.

Kay Roepke writes: 
IN clauses are fixed, one issue was a mishandling of the unary NOT operator, confusing the parser to reject IN clauses where present

Carsten Segieth writes: 
the following query "without a SUBSTR()" could not be formatted in 2.2.0.1633 - not sure the "not in" problem here is the same as described above as it's not for a subselect but a value list:

select group_concat('\\\\\n* ', t.table_schema, '.', t.table_name) table_list 
  from information_schema.tables t 
       left join information_schema.table_constraints c 
        on (t.table_schema = c.table_schema 
        and t.table_name = c.table_name 
        and c.constraint_type in ('primary key','unique')) 
  where t.table_schema not in ('mysql','information_schema') 
    and t.engine not in ('archive','federated') 
    and c.table_name is null

full message from the SQL logs:
mismatch: select group_concat('\\\\\n* ', t.table_schema, '.', t.table_name) table_list from information_schema.tables t left join information_schema.table_constraints c on (t.table_schema = c.table_schema and t.table_name = c.table_name and c.constraint_type in ('primary key','unique')) where t.table_schema not in ('mysql','information_schema') and t.engine not in ('archive','federated') and c.table_name is null vs. select group_concat( '\\\\\n* ', t.table_schema, '.', t.table_name ) table_list from information_schema.tables t left join information_schema.table_constraints c on (t.table_schema = c.table_schema and t.table_name = c.table_name and c.constraint_type in ('primary key', 'unique')) where in ('mysql', 'information_schema') and in ('archive', 'federated') and c.table_name is null