[MDEV-25452] JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: N/A
Fix Version/s: 10.6
Component/s: JSON, Temporal Types
Labels:
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

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2021-04-19 14:48

Elena Stepanova made changes - 2021-04-19 14:48

Field	Original Value	New Value
Link		This issue relates to TODO-2305 [ TODO-2305 ]

Elena Stepanova made changes - 2021-04-19 14:48

Link

This issue relates to ~~MDEV-17399~~ [ ~~MDEV-17399~~ ]

Elena Stepanova made changes - 2021-04-19 14:48

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:

{code:sql|title=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)
{code}
{noformat:title=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
{noformat}

Even when the value is an explicit null, it's still converted to zero time:
{code:sql}
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)
{code}

The result is the same in MySQL, 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}}):

{code:sql}
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'
{code}

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:

{code:sql|title=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)
{code}
{noformat:title=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
{noformat}

Even when the value is an explicit null, it's still converted to zero time:
{code:sql}
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)
{code}

The result is the same in MySQL with {{explicit_defaults_on_timestamp}}, 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}}):

{code:sql}
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'
{code}

Elena Stepanova made changes - 2021-04-19 14:48

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:

{code:sql|title=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)
{code}
{noformat:title=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
{noformat}

Even when the value is an explicit null, it's still converted to zero time:
{code:sql}
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)
{code}

The result is the same in MySQL with {{explicit_defaults_on_timestamp}}, 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}}):

{code:sql}
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'
{code}

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:

{code:sql|title=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)
{code}
{noformat:title=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
{noformat}

Even when the value is an explicit null, it's still converted to zero time:
{code:sql}
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)
{code}

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}}):

{code:sql}
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'
{code}

Elena Stepanova made changes - 2021-04-19 14:48

Summary

JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp

JSON_TABLE: TIMESTAMP column is always created as NOT NULL with explicit_defaults_on_timestamp=OFF

Elena Stepanova made changes - 2021-04-19 14:49

Assignee

Sergei Petrunia [ psergey ]

Alexey Botchkov [ holyfoot ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 121161 ]

MariaDB v4 [ 142783 ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-01 08:08

Assignee

Alexey Botchkov [ holyfoot ]

Rucha Deodhar [ rucha174 ]

People

Assignee:: Rucha Deodhar

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2021-04-19 14:48

Updated:: 2022-06-01 08:08

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration