| Bug #99141 | Result mismatches when length of JSON binary exceeds max_allowed_packet | ||
|---|---|---|---|
| Submitted: | 1 Apr 2020 7:32 | Modified: | 11 Aug 2020 20:02 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.16, 8.0.19 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[1 Apr 2020 8:39]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[11 Aug 2020 7:49]
Tor Didriksen
Posted by developer:
The inconsistencies for rollup groups were fixed by the patch for:
commit 40233a68a3f449951ceebd28a5cc477f1358f01c
Author: Steinar H. Gunderson <steinar.gunderson@oracle.com>
Date: Thu Feb 6 14:53:54 2020 +0100
Bug #30969045: IMPLEMENT ROLLUP WITHOUT SLICES
Bug #30921780: ROLLUP OUTPUT IS NOT REASONABLE WHEN SAME FIELDS EXIST IN THE GROUP BY SYNTAX
Bug #26227613: REPEATING COLUMN IN GROUP BY WITH ROLLUP YIELDS WRONG RESULT
Bug #29134467: REFACTOR RECURSION IN RESOLVE_ROLLUP_ITEM TO USE ITEM_TRANSFORM
Bug #30967158: THE ROLLUP SUPER-AGGREGATE ROW OUTPUT IS NOT CORRECT
mtr test case:
=============================================
--source include/count_sessions.inc
CREATE TABLE `tt` (
`i` int(11) DEFAULT NULL,
`j` json DEFAULT NULL,
`si` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO tt(j) VALUES (JSON_ARRAY(REPEAT('abc', 100)));
UPDATE tt SET j = JSON_ARRAY(j,j,j,j);
SET GLOBAL net_buffer_length = 1024;
SET GLOBAL max_allowed_packet = 1024;
connect (con1, localhost, root);
--echo # connection 1
connection con1;
SELECT REPEAT(j, 2) FROM tt;
SELECT REPEAT(j, 2), COUNT(*) FROM tt
GROUP BY j, i;
SELECT REPEAT(j, 2), COUNT(*) FROM tt
GROUP BY j, i WITH ROLLUP;
SELECT REPEAT(j, 2) as a, COUNT(*) FROM
tt GROUP BY j, i WITH ROLLUP ORDER BY a;
--echo # connection default
connection default;
disconnect con1;
--source include/wait_until_count_sessions.inc
DROP TABLE tt;
======================================
result:
CREATE TABLE `tt` (
`i` int(11) DEFAULT NULL,
`j` json DEFAULT NULL,
`si` int(11) DEFAULT NULL
) ENGINE=InnoDB;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO tt(j) VALUES (JSON_ARRAY(REPEAT('abc', 100)));
UPDATE tt SET j = JSON_ARRAY(j,j,j,j);
SET GLOBAL net_buffer_length = 1024;
SET GLOBAL max_allowed_packet = 1024;
# connection 1
SELECT REPEAT(j, 2) FROM tt;
REPEAT(j, 2)
Warnings:
Warning 1301 Result of j() was larger than max_allowed_packet (1024) - truncated
SELECT REPEAT(j, 2), COUNT(*) FROM tt
GROUP BY j, i;
REPEAT(j, 2) COUNT(*)
1
Warnings:
Warning 1301 Result of j() was larger than max_allowed_packet (1024) - truncated
SELECT REPEAT(j, 2), COUNT(*) FROM tt
GROUP BY j, i WITH ROLLUP;
REPEAT(j, 2) COUNT(*)
1
1
NULL 1
Warnings:
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
Warning 1301 Result of j() was larger than max_allowed_packet (1024) - truncated
SELECT REPEAT(j, 2) as a, COUNT(*) FROM
tt GROUP BY j, i WITH ROLLUP ORDER BY a;
a COUNT(*)
NULL 1
1
1
Warnings:
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
Warning 1301 Result of j() was larger than max_allowed_packet (1024) - truncated
# connection default
DROP TABLE tt;
[11 Aug 2020 20:06]
Jon Stephens
This is fixed in MySQL 8.0.21 by the fix for BUG#98768, see same for docs info. Closed.
Description: When the length of json value is larger than max_allowed_packet. The server will push a warning and return the NULL value. But if I add the ORDER BY syntax to the query, the result will display EMPTY instead of NULL, which is strange. How to repeat: CREATE TABLE `tt` ( `i` int(11) DEFAULT NULL, `j` json DEFAULT NULL, `si` int(11) DEFAULT NULL ) ENGINE=InnoDB; INSERT INTO tt(j) VALUES (JSON_ARRAY(REPEAT('abc', 100))); UPDATE tt SET j = JSON_ARRAY(j,j,j,j); SET GLOBAL net_buffer_length = 1024; SET GLOBAL max_allowed_packet = 1024; Quit the MySQL client and reconnect. root@localhost:test 8.0.16-rds-dev> SELECT REPEAT(j, 2), COUNT(*) FROM tt GROUP BY j, i WITH ROLLUP; +--------------+----------+ | REPEAT(j, 2) | COUNT(*) | +--------------+----------+ | NULL | 1 | | NULL | 1 | | NULL | 1 | +--------------+----------+ 3 rows in set, 2 warnings (0.00 sec) root@localhost:test 8.0.16-rds-dev> SELECT REPEAT(j, 2) as a, COUNT(*) FROM tt GROUP BY j, i WITH ROLLUP ORDER BY a; +------+----------+ | a | COUNT(*) | +------+----------+ | NULL | 1 | | | 1 | | | 1 | +------+----------+ 3 rows in set, 2 warnings (0.00 sec) The above results of the two queries are inconsistent.