[#MDEV-25452] JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on

With explicit_defaults_on_timestamp=OFF, which is still default in MariaDB, a JSON_TABLE column of type TIMESTAMP is always not null with default value '0000-00-00 00:00:00', and there seems to be no way to override it:

bb-10.6-mdev17399-hf c3cb41111
MariaDB [test]> select * from json_table('{}', '$' columns(a timestamp path '$', b datetime path '$')) jt;
+---------------------+------+
\| a \| b \|
+---------------------+------+
\| 0000-00-00 00:00:00 \| NULL \|
+---------------------+------+
1 row in set (0.001 sec)

Column type info
Field 1: `a`
Catalog: `def`
Database: ``
Table: `jt`
Org_table: ``
Type: TIMESTAMP
Collation: binary (63)
Length: 19
Max_length: 19
Decimals: 0
Flags: NOT_NULL UNSIGNED BINARY

Field 2: `b`
Catalog: `def`
Database: ``
Table: `jt`
Org_table: ``
Type: DATETIME
Collation: binary (63)
Length: 19
Max_length: 0
Decimals: 0
Flags: BINARY

Even when the value is an explicit null, it's still converted to zero time:

select * from json_table('[null]', '$' columns(a timestamp path '$[0]' error on error error on empty, b datetime path '$[0]' error on error error on empty)) jt;

+---------------------+------+

| a                   | b    |

+---------------------+------+

| 0000-00-00 00:00:00 | NULL |

+---------------------+------+

1 row in set (0.001 sec)

The result is the same in MySQL with explicit_defaults_on_timestamp=OFF, but the big difference is that in MySQL explicit_defaults_on_timestamp is ON by default, the variable is deprecated, and it's changeable at runtime. In MariaDB it's all the opposite.

It causes further issues when JSON_TABLE is used in a table-changing context with SQL_MODE=NO_ZERO_DATE (which is a part of some group SQL modes, e.g. TRADITIONAL):

MariaDB [test]> set sql_mode='NO_ZERO_DATE';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> create or replace table t as select * from json_table('{}', '$' columns (a timestamp path '$')) jt;

ERROR 1067 (42000): Invalid default value for 'a'

[MDEV-25452] JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF Created: 2021-04-19 Updated: 2022-06-01
Status:	Open
Project:	MariaDB Server
Component/s:	JSON, Temporal Types
Affects Version/s:	N/A
Fix Version/s:	10.6

[MDEV-25452] JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF Created: 2021-04-19 Updated: 2022-06-01