On Thu, Jul 18, 2024 at 3:04 PM jian he <jian.universal...@gmail.com> wrote: > we still have problem in transformJsonBehavior > > currently transformJsonBehavior: > SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON > ERROR); > ERROR: cannot cast behavior expression of type text to bit > LINE 1: ...VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON ... > > here, 010111 will default to int4, so "cannot cast behavior expression > of type text to bit" > is wrong? > also int4/int8 can be explicitly cast to bit(3), in this case, it > should return 111.
I think we shouldn't try too hard in the code to "automatically" cast the DEFAULT expression, especially if that means having to add special case code for all sorts of source-target-type combinations. I'm inclined to just give a HINT to the user to cast the DEFAULT expression by hand, because they *can* do that with the syntax that exists. On the other hand, transformJsonBehavior() should handle other "internal" expressions for which the cast cannot be specified by hand. > Also, do we want to deal with bit data type's typmod like we did for > string type in transformJsonBehavior? > like: > SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(3) default '1111' on > error); > should return error: > ERROR: bit string length 2 does not match type bit(3) > or success > > The attached patch makes it return an error, similar to what we did > for the fixed length string type. Yeah, that makes sense. I'm planning to push the attached 2 patches. 0001 is to fix transformJsonBehavior() for these cases and 0002 to adjust the behavior of casting the result of JSON_EXISTS() and EXISTS columns to integer type. I've included the tests in your patch in 0001. I noticed using cast expression to coerce the boolean constants to fixed-length types would produce unexpected errors when the planner's const-simplification calls the cast functions. So in 0001, I've made that case also use runtime coercion using json_populate_type(). -- Thanks, Amit Langote
0002-SQL-JSON-Fix-casting-for-integer-EXISTS-columns-in-J.patch
Description: Binary data
0001-SQL-JSON-Some-fixes-to-JsonBehavior-expression-casti.patch
Description: Binary data
