MySQL Bugs: #82090: South Asian / Indian / Vedic number system with FORMAT()

Bug #82090	South Asian / Indian / Vedic number system with FORMAT()
Submitted:	3 Jul 2016 18:21	Modified:	4 Jul 2016 7:54
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.31, 5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
(can't find a good category for this report).

The Indian (originally Vedian) number system is still used in South Asia (India, Pakistan, Bangla Desh and Sri Lanka, maybe also Maldives) as described here https://en.wikipedia.org/wiki/Indian_numbering_system

This is not at all old-fashioned nor outdated. It is contemporary.  In both daily speak, official/government documents and the press there is nothing like "one houndred thousand" in those countries.  There is "one lakh" instead. 

I wonder if MySQL FORMAT() function supports it at all? In other contexts I can specify the locale 'hi_IN', but not with MySQL. Also 'en_IN' should work, I think, but doesn't). OS's (at least Windows, I think also Linux) can use this number format natively.

How to repeat:
SELECT FORMAT(123456789,2,hi_IN);
-- should return "12,34,56,789" (pronounced "12 crores 34 lakhs 56 thousands and 789" in English): 
-- but instead returns Error Code: 1054: Unknown column 'hi_IN' in 'field list'

Suggested fix:
Maybe I am missing something but then I also think that docs does. How can I specify a locale and make MySQL format numbers in Indian number system? I can't find any hint in thedocs.

Either add support for those locales (if does not exist) or document how it is supported and may be called (if exists).

corrected synopsis.

Hi Peter,

Thank you for the report.
Imho, locale is expected to be string and hence needs to be enclosed in quotes. Verifying this bug for the wrong format i.e a comma divides every two rather than every three digits to the left of the last three which is correct in 'en_IN' but not in 'hi_IN'.

-- 5.6.31, 5.7.13

mysql> SELECT FORMAT(123456789,2,hi_IN);
ERROR 1054 (42S22): Unknown column 'hi_IN' in 'field list'
mysql>
mysql> SELECT FORMAT(123456789,2,en_US);
ERROR 1054 (42S22): Unknown column 'en_US' in 'field list'

mysql> -- in quotes
mysql> SELECT FORMAT(123456789,2,'hi_IN');
+-----------------------------+
| FORMAT(123456789,2,'hi_IN') |
+-----------------------------+
| 123,456,789.00              |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(123456789,2,'en_IN');
+-----------------------------+
| FORMAT(123456789,2,'en_IN') |
+-----------------------------+
| 12,34,56,789.00             |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(123456789,2,'en_US');
+-----------------------------+
| FORMAT(123456789,2,'en_US') |
+-----------------------------+
| 123,456,789.00              |
+-----------------------------+
1 row in set (0.00 sec)

Thanks,
Umesh

You are right, of course!