[7.0.132 beta] Formatting glitch in Toad for MySQL

[7.0.132 beta] Formatting glitch in Toad for MySQL

Toad for MySQL (and I guess any editor marked for MySQL) suffers the same glitch mentioned in Formatting ":=" in Notepad++, namely, ":=" breaks apart by a space put between ":" and "=".

Thank you. I have logged this issue.

Could you please provide a code sample which does get formatted correctly?

Sure. Here it is. This simple query returns the routine with fourth longest definition.

	Code:
	SELECT * FROM ( SELECT r.ROUTINE_CATALOG ,r.ROUTINE_SCHEMA ,r.ROUTINE_NAME ,r.ROUTINE_TYPE ,r.ROUTINE_BODY ,LENGTH(r.ROUTINE_DEFINITION) ,@rownum:=@rownum+1 AS rownum FROM ( SELECT @rownum:= 0 ) AS rn ,information_schema.ROUTINES AS r WHERE 1 = 1 ORDER BY LENGTH(r.ROUTINE_DEFINITION) DESC ) AS sq WHERE 1 = 1 AND sq.rownum = 4

Pushing this through the formatting process returns the following:

Code:

SELECT
*
FROM
(
   SELECT
      r.ROUTINE_CATALOG
      ,r.ROUTINE_SCHEMA
      ,r.ROUTINE_NAME
      ,r.ROUTINE_TYPE
      ,r.ROUTINE_BODY
      ,LENGTH(r.ROUTINE_DEFINITION)
      ,@rownum : = @rownum+1 AS rownum
   FROM
      (
         SELECT
            @rownum : = 0
      ) AS rn
         ,information_schema.ROUTINES AS r
   WHERE 1 = 1
   ORDER BY
      LENGTH(r.ROUTINE_DEFINITION) DESC
) AS sq
WHERE 1 = 1
AND sq.rownum = 4

which in turn results in syntax error when run. The correct format would be:

Code:

SELECT
*
FROM
(
   SELECT
      r.ROUTINE_CATALOG
      ,r.ROUTINE_SCHEMA
      ,r.ROUTINE_NAME
      ,r.ROUTINE_TYPE
      ,r.ROUTINE_BODY
      ,LENGTH(r.ROUTINE_DEFINITION)
      ,@rownum := @rownum + 1 AS rownum
   FROM
      (
         SELECT
            @rownum := 0
      ) AS rn
         ,information_schema.ROUTINES AS r
   WHERE 1 = 1
   ORDER BY
      LENGTH(r.ROUTINE_DEFINITION) DESC
) AS sq
WHERE 1 = 1
AND sq.rownum = 4

where the operand is formatted as ' := ' (there is a space on both sides of ':=' but none between ':' and '=').

Thank you

Hi. We are unable to reproduce this issue.

Could you please try it with factory-default formatting settings?

I works. I compared the default formatting setting for select to mine and it turned out that they differed here and there:

	default wrote:
	SELECT ... AS ..., ... INTO ..., ... FROM ... AS ..., ... JOIN ... ON ... AND ... WHERE ... = ... AND ... OR ... GROUP BY ..., ... HAVING ..., ... ORDER BY ..., ... LIMIT ...;

	mine wrote:
	SELECT ... AS ... ,... = ... INTO ... ,... FROM ... AS ... ,... JOIN ... ON ... = ... AND ... OR ... WHERE ... = ... AND ... OR ... GROUP BY ... ,... HAVING ... ,... ORDER BY ... ,...

Merged the two (removed the '= ...' from the SELECT part) and now it seems fine, thanks for the hint.