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

JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such

    XMLWordPrintable

Details

    Description

      ORDINALITY columns in some ways behave as auto-increment, and as auto-increment they can cause a non-deterministic result, so they should be marked as unsafe for statement binary log (produce a warning with binlog_format=statement and make binary logging switch to row with binlog_format=mixed). If it's easier, I suppose it can be extended onto any statements involving JSON_TABLE.

      Below is an example I have made up to demonstrate the point within the current MariaDB implementation; but in general, nothing in the standard guarantees any particular order in which values are returned and hence ordinality is assigned, and it's already clear that the order is different between implementations (e.g. between MariaDB and MySQL), and will likely variate between versions.

      bb-10.6-mdev17399-psergey2 8b533cc1d5

      MariaDB [test]> create table t (a int, key(a));
      Query OK, 0 rows affected (0.037 sec)
       
      MariaDB [test]> insert into t values (30),(20),(10);
      Query OK, 3 rows affected (0.013 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      

      MariaDB [test]> create or replace table tt as select * from json_table((select json_arrayagg(a) from t), '$[*]' columns (o for ordinality, b int path '$')) t;
      Query OK, 3 rows affected (0.032 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from tt;
      +------+------+
      | o    | b    |
      +------+------+
      |    1 |   10 |
      |    2 |   20 |
      |    3 |   30 |
      +------+------+
      3 rows in set (0.001 sec)
      

      MariaDB [test]> create or replace table tt as select * from json_table((select json_arrayagg(a) from t ignore index(a)), '$[*]' columns (o for ordinality, b int path '$')) t;
      Query OK, 3 rows affected (0.042 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from tt;
      +------+------+
      | o    | b    |
      +------+------+
      |    1 |   30 |
      |    2 |   20 |
      |    3 |   10 |
      +------+------+
      3 rows in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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