Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
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
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-