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

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: