fix(expr): jsonb_populate_record shall support more data types

[xiangjinwu]

https://www.postgresql.org/docs/17/functions-json.html#id-1.5.8.22.8.13.2.2.9.1.2.1

To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence:

• A JSON null value is converted to an SQL null in all cases.
• If the output column is of type json or jsonb, the JSON value is just reproduced exactly.
• If the output column is a composite (row) type, and the JSON value is a JSON object, the fields of the object are converted to columns of the output row type by recursive application of these rules.
• Likewise, if the output column is an array type and the JSON value is a JSON array, the elements of the JSON array are converted to elements of the output array by recursive application of these rules.
• Otherwise, if the JSON value is a string, the contents of the string are fed to the input conversion function for the column's data type.
• Otherwise, the ordinary text representation of the JSON value is fed to the input conversion function for the column's data type.

However the current implementation is:

risingwave/src/common/src/types/jsonb.rs

Line 523 in c658340

_ => Self(v).to_datum(ty)?,

risingwave/src/common/src/types/jsonb.rs

Lines 409 to 425 in c658340

	/// Convert the jsonb value to a datum.
	pub fn to_datum(self, ty: &DataType) -> Result<Datum, String> {
	if !matches!(
	ty,
	DataType::Jsonb
	| DataType::Boolean
	| DataType::Int16
	| DataType::Int32
	| DataType::Int64
	| DataType::Float32
	| DataType::Float64
	| DataType::Varchar
	| DataType::List(_)
	| DataType::Struct(_)
	) {
	return Err(format!("cannot cast jsonb to {ty}"));
	}

The error message actually confuses between cast and populate. Similarly the their inverses (cast and to_json) are not the same. Some examples in PostgreSQL:

test=# select to_jsonb(now());
              to_jsonb              
------------------------------------
 "2024-11-08T12:47:05.473043+00:00"
(1 row)

test=# select now()::jsonb;
ERROR:  cannot cast type timestamp with time zone to jsonb
LINE 1: select now()::jsonb;
                    ^
test=# select '"2024-11-08T12:47:05.473043+00:00"'::jsonb::timestamptz;
ERROR:  cannot cast type jsonb to timestamp with time zone
LINE 1: select '"2024-11-08T12:47:05.473043+00:00"'::jsonb::timestam...
                                                          ^
test=# select * from jsonb_to_record('{"ts": "2024-11-08T12:47:05.473043+00:00"}'::jsonb) as (ts timestamptz);
              ts               
-------------------------------
 2024-11-08 12:47:05.473043+00
(1 row)

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄

[xiangjinwu]

Done by #19937