From f18c2b6c8a92475f685ce60c495faddc5aaab011 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Mon, 23 Jan 2023 13:10:24 +0200 Subject: MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | (Initial patch by Varun Gupta. Amended and added comments). When the query has both 1. Aggregate functions that require sorting data by group, and 2. Window functions we need to use two temporary tables. The first temp.table will hold the join output. Then it is passed to filesort(). Reading it in sorted order allows to compute the aggregate functions. Then, we need to write their values into the second temp. table. Then, Window Function computation step can pass that to filesort() and read them in the order it needs. Failure to create the second temp. table would cause an assertion failure: window function could would not find where to get the values of the aggregate functions. --- .../suite/encryption/r/tempfiles_encrypted.result | 46 +++++++++++++++++++++- 1 file changed, 44 insertions(+), 2 deletions(-) (limited to 'mysql-test/suite/encryption') diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index 8600a23a0d8..b71e5315bd0 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -4278,11 +4278,13 @@ GROUP BY LEFT((SYSDATE()), 'foo') WITH ROLLUP; SUM(b) OVER (PARTITION BY a) ROW_NUMBER() OVER (PARTITION BY b) -NULL 1 -NULL 1 +0 1 +0 2 Warnings: Warning 1292 Truncated incorrect INTEGER value: 'foo' Warning 1292 Truncated incorrect INTEGER value: 'foo' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' +Warning 1292 Truncated incorrect DOUBLE value: 'bar' drop table t1; # # @@ -4341,6 +4343,46 @@ pk a bit_or DROP TABLE t2; DROP TABLE t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); +SELECT +a, +RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM +t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY +a; +a RANK() OVER (ORDER BY SUM(DISTINCT i1)) +1 1 +2 2 +3 3 +DROP TABLE t1, t2; +# +# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +# +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, +t2 TT2, +t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +1 +1 +DROP TABLE t1, t2, t3; +# # End of 10.3 tests # # -- cgit v1.2.1