Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25452

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

Details

    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

          Task - A task that needs to be done. MDEV-17399 Add support for JSON_TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              rucha174 Rucha Deodhar
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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