Bug #20253 Syntax error with the native function FORMAT

• Submitted: 3 Jun 2006 23:04
• Modified: 10 Nov 2006 0:45
• Reporter: Alex Zimnitski
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.23-BK, 5.0.18
• OS: Linux (Linux, win32)
• Assigned to: Marc ALFF
• CPU Architecture: Any

[3 Jun 2006 23:04] Alex Zimnitski

Description:
ERROR 1064 (42000) at line 3: 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 'i_N );
        SET iLen = LENGTH( sRetval );

nothing in manual found.
replace "FORMAT( fValue, i_N )" with " '1.0101000';
remove "IF LOCATE( '.', sRetval ) = 0
		THEN SET sRetval = CONCAT( sRetval, '.' )
	END IF;" and it will work :)

How to repeat:
DELIMITER ;;
DROP FUNCTION IF EXISTS `FFMTD`;;
CREATE FUNCTION `FFMTD`( fValue FLOAT, i_N INT ) RETURNS CHAR(20)
BEGIN
	DECLARE sRetval VARCHAR(20);
	DECLARE iLen INT;
	DECLARE i_I INT DEFAULT 0;

	SET sRetval = FORMAT( fValue, i_N );
	SET iLen = LENGTH( sRetval );

	IF LOCATE( '.', sRetval ) = 0
		THEN SET sRetval = CONCAT( sRetval, '.' )
	END IF;

	label1: LOOP
		SET i_I = i_I + 1;
		IF MID( sRetval, iLen-i_I, 1 ) = '0'
			THEN ITERATE label1;
		END IF;
		LEAVE label1;
	END LOOP label1;
	
	IF MID( sRetval, iLen-i_I, 1 ) = '.'
		THEN SET i_I = i_I + 1;
	END IF;

	RETURN  CONCAT( LEFT( sRetval, iLen-i_I ), REPEAT( ' ', i_I ) );
END ;;
DELIMITER ;

[5 Jun 2006 12:49] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.23-BK on Linux:

mysql> delimiter //
mysql> CREATE FUNCTION `FFMTD`( fValue FLOAT, i_N INT ) RETURNS CHAR(20)
    -> BEGIN
    ->   DECLARE sRetval VARCHAR(20);
    ->   DECLARE iLen INT;
    ->   DECLARE i_I INT DEFAULT 0;
    ->
    ->   SET sRetval = FORMAT( fValue, i_N );
    ->   SET iLen = LENGTH( sRetval );
    ->
    ->   IF LOCATE( '.', sRetval ) = 0
    ->     THEN SET sRetval = CONCAT( sRetval, '.' )
    ->   END IF;
    ->
    ->   label1: LOOP
    ->     SET i_I = i_I + 1;
    ->     IF MID( sRetval, iLen-i_I, 1 ) = '0'
    ->       THEN ITERATE label1;
    ->     END IF;
    ->     LEAVE label1;
    ->   END LOOP label1;
    ->
    ->   IF MID( sRetval, iLen-i_I, 1 ) = '.'
    ->     THEN SET i_I = i_I + 1;
    ->   END IF;
    ->   RETURN  CONCAT( LEFT( sRetval, iLen-i_I ), REPEAT( ' ', i_I ) );
    -> END//
ERROR 1064 (42000): 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 'i_N )
;
  SET iLen = LENGTH( sRetval );
  IF LOCATE( '.', sRetval ) = 0
    THEN ' at line 6
mysql> select version();
    -> //
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.00 sec)

[10 Nov 2006 0:45] Marc ALFF

Closing as a duplicate of Bug#22684.

Normally, we keep the oldest report open and close newer ones as duplicates,
but in this case Bug#22684 is already fixed and under review.

Thank you for the report.