Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
N/A
-
None
Description
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' |
Attachments
Issue Links
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-