[DRILL-6975] TO_CHAR does not seems work well depends on LOCALE

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.14.0, 1.15.0, 1.16.0, 1.17.0
• Component/s: Functions - Drill
• Assignee: Unassigned
• Reporter: benj
• Created: 14/Jan/19 14:30
• Updated: 30/Mar/20 14:15

Description

Strange results from TO_CHAR function when using different LOCALE.

SELECT TO_CHAR((CAST('2008-2-23' AS DATE)), 'yyyy-MMM-dd') FROM (VALUES(1));
2008-Feb-23 (in documentation (en_US.UTF-8)) 
2008-févr.-2 (fr_FR.UTF-8)

surprisingly by adding a space ('yyyy-MMM-dd ') (or any character) at the end of the format  the result becomes correct (so there is no problem when format a timestamp with 'yyyy MMM dd HH:mm:ss')

SELECT TO_CHAR(1256.789383, '#,###.###') FROM (VALUES(1));
1,256.789 (in documentation (en_US.UTF-8)) 
1 256,78 (fr_FR.UTF-8)

Even worse results can be achieved

SELECT TO_CHAR(12567,'#,###.###');
12,567 (en_US.UTF-8)
12 56 (fr_FR.UTF-8)

Again, with the add of a space/char at the end we get a better result.

I don't have tested all the locale, but for the last example, the result is right with de_DE.UTF-8 : 12.567

The situation is identical in 1.14