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/