BUG #8676: Bug Money JSON
The following bug has been logged on the website:
Bug reference: 8676
Logged by: Anderson Cristian da Silva
Email address: email@andersonloyola.com.br
PostgreSQL version: 9.3.2
Operating system: Centos 6.3 (Final)
Description:
Failed to convert money to json EX.:
postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)
postgres=# SELECT to_json(a)->'salario' FROM (VALUES(1000::money))
a(salario);
ERROR: invalid input syntax for type json
DETAIL: Token "$" is invalid.
CONTEXT: JSON data, line 1: {"salario":$...
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Wed, Dec 11, 2013 at 02:30:04PM +0000, email@andersonloyola.com.br wrote:
The following bug has been logged on the website:
Bug reference: 8676
Logged by: Anderson Cristian da Silva
Email address: email@andersonloyola.com.br
PostgreSQL version: 9.3.2
Operating system: Centos 6.3 (Final)
Description:Failed to convert money to json EX.:
postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)postgres=# SELECT to_json(a)->'salario' FROM (VALUES(1000::money))
a(salario);
ERROR: invalid input syntax for type json
DETAIL: Token "$" is invalid.
CONTEXT: JSON data, line 1: {"salario":$...
Well, that is very interesting. It works for integer and text, and it
works if you ask for the entire relation, but if you ask just for that
field _and_ it is money, it fails. Sure looks like a bug to me.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 12/17/2013 09:10 AM, Bruce Momjian wrote:
On Wed, Dec 11, 2013 at 02:30:04PM +0000, email@andersonloyola.com.br wrote:
The following bug has been logged on the website:
Bug reference: 8676
Logged by: Anderson Cristian da Silva
Email address: email@andersonloyola.com.br
PostgreSQL version: 9.3.2
Operating system: Centos 6.3 (Final)
Description:Failed to convert money to json EX.:
postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)postgres=# SELECT to_json(a)->'salario' FROM (VALUES(1000::money))
a(salario);
ERROR: invalid input syntax for type json
DETAIL: Token "$" is invalid.
CONTEXT: JSON data, line 1: {"salario":$...Well, that is very interesting. It works for integer and text, and it
works if you ask for the entire relation, but if you ask just for that
field _and_ it is money, it fails. Sure looks like a bug to me.
Yeah. I'll have a look. In fact this looks like it's possibly a couple
of bugs. The JSON produced by the first query is not valid. It looks
like we might need to force money to text unconditionally.
cheers
andrew
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Andrew Dunstan <andrew@dunslane.net> writes:
On Wed, Dec 11, 2013 at 02:30:04PM +0000, email@andersonloyola.com.br wrote:
postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)
Yeah. I'll have a look. In fact this looks like it's possibly a couple
of bugs. The JSON produced by the first query is not valid. It looks
like we might need to force money to text unconditionally.
Isn't this simply failure to quote the string properly? What drives
to_json's choice of whether to quote or not, anyway?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 12/17/2013 10:31 AM, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On Wed, Dec 11, 2013 at 02:30:04PM +0000, email@andersonloyola.com.br wrote:
postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)Yeah. I'll have a look. In fact this looks like it's possibly a couple
of bugs. The JSON produced by the first query is not valid. It looks
like we might need to force money to text unconditionally.Isn't this simply failure to quote the string properly? What drives
to_json's choice of whether to quote or not, anyway?
If it's numeric, it only quotes if it sees a non-numeric character,
defined thus:
/* letters appearing in numeric output that aren't valid in a JSON
number */
#define NON_NUMERIC_LETTER "NnAaIiFfTtYy"
I forgot about money when I did that - some of this dates back to 9.2.
I'm about to test the attached patch which should force money to be
quoted always.
cheers
andrew
Attachments:
json_money.patchtext/x-patch; name=json_money.patchDownload
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index d8ebf4e..f1c7195 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -1381,6 +1381,8 @@ array_to_json_internal(Datum array, StringInfo result, bool use_line_feeds)
tcategory = TYPCATEGORY_COMPOSITE;
else if (element_type == JSONOID)
tcategory = TYPCATEGORY_JSON;
+ else if (element_type == CASHOID)
+ tcategory = TYPCATEGORY_STRING;
else
tcategory = TypeCategory(element_type);
@@ -1476,6 +1478,8 @@ composite_to_json(Datum composite, StringInfo result, bool use_line_feeds)
tcategory = TYPCATEGORY_COMPOSITE;
else if (tupdesc->attrs[i]->atttypid == JSONOID)
tcategory = TYPCATEGORY_JSON;
+ else if (tupdesc->attrs[i]->atttypid == CASHOID)
+ tcategory = TYPCATEGORY_STRING;
else
tcategory = TypeCategory(tupdesc->attrs[i]->atttypid);
@@ -1602,6 +1606,8 @@ to_json(PG_FUNCTION_ARGS)
tcategory = TYPCATEGORY_COMPOSITE;
else if (val_type == JSONOID)
tcategory = TYPCATEGORY_JSON;
+ else if (val_type == CASHOID)
+ tcategory = TYPCATEGORY_STRING;
else
tcategory = TypeCategory(val_type);
On 12/17/2013 11:16 AM, Andrew Dunstan wrote:
On 12/17/2013 10:31 AM, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On Wed, Dec 11, 2013 at 02:30:04PM +0000,
email@andersonloyola.com.br wrote:postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)Yeah. I'll have a look. In fact this looks like it's possibly a couple
of bugs. The JSON produced by the first query is not valid. It looks
like we might need to force money to text unconditionally.Isn't this simply failure to quote the string properly? What drives
to_json's choice of whether to quote or not, anyway?If it's numeric, it only quotes if it sees a non-numeric character,
defined thus:/* letters appearing in numeric output that aren't valid in a JSON
number */
#define NON_NUMERIC_LETTER "NnAaIiFfTtYy"I forgot about money when I did that - some of this dates back to 9.2.
I'm about to test the attached patch which should force money to be
quoted always.
This turned out to be not such a good idea. Quite apart from anything
else it doesn't handle domains over money at all well.
The attached patch abandons the test described above, and instead passes
the string from the output function to the json number lexer to see if
it's a valid json number. A small adjustment to the API of that
function was required to make it suitable for this use. This seems like
a much more robust approach.
cheers
andrew
Attachments:
json_money2.patchtext/x-patch; name=json_money2.patchDownload
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 1486eda..af8ddc6 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -50,7 +50,7 @@ typedef enum /* contexts of JSON parser */
static inline void json_lex(JsonLexContext *lex);
static inline void json_lex_string(JsonLexContext *lex);
-static inline void json_lex_number(JsonLexContext *lex, char *s);
+static inline void json_lex_number(JsonLexContext *lex, char *s, bool *num_err);
static inline void parse_scalar(JsonLexContext *lex, JsonSemAction *sem);
static void parse_object_field(JsonLexContext *lex, JsonSemAction *sem);
static void parse_object(JsonLexContext *lex, JsonSemAction *sem);
@@ -147,8 +147,6 @@ lex_expect(JsonParseContext ctx, JsonLexContext *lex, JsonTokenType token)
#define TYPCATEGORY_JSON 'j'
/* fake category for types that have a cast to json */
#define TYPCATEGORY_JSON_CAST 'c'
-/* letters appearing in numeric output that aren't valid in a JSON number */
-#define NON_NUMERIC_LETTER "NnAaIiFfTtYy"
/* chars to consider as part of an alphanumeric token */
#define JSON_ALPHANUMERIC_CHAR(c) \
(((c) >= 'a' && (c) <= 'z') || \
@@ -567,7 +565,7 @@ json_lex(JsonLexContext *lex)
break;
case '-':
/* Negative number. */
- json_lex_number(lex, s + 1);
+ json_lex_number(lex, s + 1, NULL);
lex->token_type = JSON_TOKEN_NUMBER;
break;
case '0':
@@ -581,7 +579,7 @@ json_lex(JsonLexContext *lex)
case '8':
case '9':
/* Positive number. */
- json_lex_number(lex, s);
+ json_lex_number(lex, s, NULL);
lex->token_type = JSON_TOKEN_NUMBER;
break;
default:
@@ -903,7 +901,7 @@ json_lex_string(JsonLexContext *lex)
*-------------------------------------------------------------------------
*/
static inline void
-json_lex_number(JsonLexContext *lex, char *s)
+json_lex_number(JsonLexContext *lex, char *s, bool *num_err)
{
bool error = false;
char *p;
@@ -976,10 +974,19 @@ json_lex_number(JsonLexContext *lex, char *s)
*/
for (p = s; len < lex->input_length && JSON_ALPHANUMERIC_CHAR(*p); p++, len++)
error = true;
- lex->prev_token_terminator = lex->token_terminator;
- lex->token_terminator = p;
- if (error)
- report_invalid_token(lex);
+
+ if (num_err != NULL)
+ {
+ /* let the caller handle the error */
+ *num_err = error;
+ }
+ else
+ {
+ lex->prev_token_terminator = lex->token_terminator;
+ lex->token_terminator = p;
+ if (error)
+ report_invalid_token(lex);
+ }
}
/*
@@ -1214,6 +1221,8 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
{
char *outputstr;
text *jsontext;
+ bool numeric_error;
+ JsonLexContext dummy_lex;
if (is_null)
{
@@ -1237,14 +1246,13 @@ datum_to_json(Datum val, bool is_null, StringInfo result,
break;
case TYPCATEGORY_NUMERIC:
outputstr = OidOutputFunctionCall(typoutputfunc, val);
-
/*
* Don't call escape_json here if it's a valid JSON number.
- * Numeric output should usually be a valid JSON number and JSON
- * numbers shouldn't be quoted. Quote cases like "Nan" and
- * "Infinity", however.
*/
- if (strpbrk(outputstr, NON_NUMERIC_LETTER) == NULL)
+ dummy_lex.input = *outputstr == '-' ? outputstr + 1 : outputstr;
+ dummy_lex.input_length = strlen(dummy_lex.input);
+ json_lex_number(&dummy_lex, dummy_lex.input, &numeric_error);
+ if (! numeric_error)
appendStringInfoString(result, outputstr);
else
escape_json(result, outputstr);
On 12/26/2013 01:17 PM, Andrew Dunstan wrote:
On 12/17/2013 11:16 AM, Andrew Dunstan wrote:
On 12/17/2013 10:31 AM, Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On Wed, Dec 11, 2013 at 02:30:04PM +0000,
email@andersonloyola.com.br wrote:postgres=# SELECT to_json(a) FROM (VALUES(1000::money)) a(salario);
to_json
-----------------------
{"salario":$1,000.00}
(1 row)Yeah. I'll have a look. In fact this looks like it's possibly a couple
of bugs. The JSON produced by the first query is not valid. It looks
like we might need to force money to text unconditionally.Isn't this simply failure to quote the string properly? What drives
to_json's choice of whether to quote or not, anyway?If it's numeric, it only quotes if it sees a non-numeric character,
defined thus:/* letters appearing in numeric output that aren't valid in a JSON
number */
#define NON_NUMERIC_LETTER "NnAaIiFfTtYy"I forgot about money when I did that - some of this dates back to 9.2.
I'm about to test the attached patch which should force money to be
quoted always.This turned out to be not such a good idea. Quite apart from anything
else it doesn't handle domains over money at all well.The attached patch abandons the test described above, and instead
passes the string from the output function to the json number lexer to
see if it's a valid json number. A small adjustment to the API of
that function was required to make it suitable for this use. This
seems like a much more robust approach.
Applied.
cheers
andrew
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs