Re: WIP: to_char, support for EEEE format

Brendan Jurd escreveu:
> Please find attached version 4 of the patch, and incremental diff from
> version 3. It fixes the "eeee" bug ("eeee" is now accepted as a valid
> form of "EEEE"), and lifts the restriction on only having one digit
> before the decimal point.
>
Looks better but I did some tests and caught some strange behaviors.

SQL> SELECT to_char(1234.56789, '8.999EEEE') FROM DUAL;
SELECT to_char(1234.56789, '8.999EEEE') FROM DUAL

ERROR at line 1:
ORA-01481: invalid number format model

SQL> SELECT to_char(1234.56789, '9.080EEEE') FROM DUAL;
SELECT to_char(1234.56789, '9.080EEEE') FROM DUAL

ERROR at line 1:
ORA-01481: invalid number format model

This is not a problem with your patch but something that needs to be fixed in
PostgreSQL to match Oracle behavior. The following example should emit an
error. IMHO, filling the string with # is very strange. TODO?

euler=# SELECT to_char(1234.56789, '9.080');
to_char
---------
#.#8#
(1 row)

Couldn't the following code be put inside switch clause? If not, you should
add a comment why the validation is outside switch.

+ if (IS_EEEE(num) && n->key->id != NUM_E)
+ {
+ NUM_cache_remove(last_NUMCacheEntry);
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("\"EEEE\" must be the last pattern used")));
+ }
+
switch (n->key->id)
{
case NUM_9:

Oracle has a diferent overflow limit [1] but I think we could stay with the
PostgreSQL one. But the #.#### is not the intended behavior. IIRC you're
limited to 99 exponent.

SQL> SELECT to_char(1.234567E+308, '9.999EEEE');
SELECT to_char(1.234567E+308, '9.999EEEE')

ERROR at line 1:
ORA-01426: numeric overflow

euler=# SELECT to_char(1.234567E+308, '9.999EEEE');
to_char
-----------
#.#######
(1 row)

The problem is in numeric_to_char() and float8_to_char(). You could fix it
with the following code. Besides that I think you should comment why '5' or
'6' in the other *_to_char() functions.

+ /* 6 means '.' (decimal point), 'E', '+', and 3 exponent digits */
+ if (isnan(value) || is_infinite(value) ||
+ len > Num.pre + Num.post + 6)
+ {
+ numstr = (char *) palloc(Num.pre + Num.post + 7);
+ fill_str(numstr, '#', Num.pre + Num.post + 6);
+ *(numstr + Num.pre) = '.';
+ }

I can't see more problems in your patch. When you fix it, it'll be ready for a
committer.

[1]
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref80

--
Euler Taveira de Oliveira
http://www.timbira.com/