[MCOL-5092] group by (moda) on FLOAT result in Internal error: TupleAnnexStep::executeNoOrderBy() IDB-2035: An internal error occurred Created: 2022-05-16  Updated: 2022-10-26  Resolved: 2022-08-17

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 6.2.3
Fix Version/s: 22.08.1

Type: Bug Priority: Critical
Reporter: Massimo Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: XML File Columnstorexml_qberg.xml    
Issue Links:
PartOf
includes MCOL-4423 idbExtentMax()/MODA() support for lon... Closed
includes MCOL-5161 MODA() to support char and varchar Closed
Sprint: 2021-17
Assigned for Review: Andrey Piskunov Andrey Piskunov (Inactive)
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

In a simple query like:

MariaDB [(none)]> sELECT 'AAA' MONDO, ri_mo_id OTHIS_RIMOID, MODA(ri_prezzo) OTHISPRMODA, COUNT(DISTINCT ri_id) OTHISNRI FROM dms_dg_ita_4570.rilevazione WHERE ri_data BETWEEN "2021-09-10" AND "2021-09-17" AND ri_in_id !=3 AND ri_prezzo IS NOT NULL GROUP BY MONDO, OTHIS_RIMOID;

results in a error:
ERROR 1815 (HY000): Internal error: TupleAnnexStep::executeNoOrderBy() IDB-2035: An internal error occurred. Check the error log file & contact support.

Customer cant go ahead

Issue appears to be related to the alias defined on a constant and then the alias is used for the GROUP BY,

 Comments   
Comment by Ricardo Galvan [ 2022-05-16 ]

NOTE: if the first fixed field is removed, then it works:

SELECT ri_mo_id OTHIS_RIMOID, MODA(ri_prezzo) OTHISPRMODA, COUNT(DISTINCT ri_id) OTHISNRI
 
FROM dms_dg_ita_4570.rilevazione
 
WHERE ri_data BETWEEN "2021-09-10" AND "2021-09-17"  
AND ri_in_id !=3 
AND ri_prezzo IS NOT NULL
 
GROUP BY  OTHIS_RIMOID

Comment by David Hall (Inactive) [ 2022-07-01 ]

I have proven that data is required for the error to occur. If the WHERE filter returns empty set, no error.
Here is a simplified example:

CREATE TABLE `rile` (
 
  `id` int(11) DEFAULT NULL,
 
  `mo_id` int(11) DEFAULT NULL,
 
  `prezzo` double DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3;
 
 
 
INSERT INTO rile values
 
(185727093, 2352674,    99),
 
(185727094, 2891223,  74.9),
 
(185727095, 2591332,  69.9),
 
(185727096, 2630674, 159.9);
 
 
 
MariaDB [dhall]> SELECT 'AAA' MONDO, mo_id, AVG(prezzo) OTHISPRMODA, COUNT(DISTINCT id) OTHISNRI FROM rile GROUP BY mo_id, MONDO;
 
+-------+---------+-------------+----------+
 
| MONDO | mo_id   | OTHISPRMODA | OTHISNRI |
 
+-------+---------+-------------+----------+
 
| AAA   | 2630674 |       159.9 |        1 |
 
| AAA   | 2352674 |          99 |        1 |
 
| AAA   | 2591332 |        69.9 |        1 |
 
| AAA   | 2891223 |        74.9 |        1 |
 
+-------+---------+-------------+----------+
 
4 rows in set (0.115 sec)
 
 
 
MariaDB [dhall]> SELECT 'AAA' MONDO, mo_id, MODA(prezzo) OTHISPRMODA, COUNT(DISTINCT id) OTHISNRI FROM rile GROUP BY mo_id, MONDO;
 
ERROR 1815 (HY000): Internal error: TupleAnnexStep::executeNoOrderBy() MCS-2035: An internal error occurred.  Check the error log file & contact support.
 
MariaDB [dhall]>

Comment by Daniel Lee (Inactive) [ 2022-08-15 ]

MCOL-5092

Build tested: 22.08-1 (#5290)

Reproduced the issue in 6.4.2-1
Verified the fix in 22.08-1

MariaDB [mytest]>  
MariaDB [mytest]> SELECT 'AAA' MONDO, mo_id, AVG(prezzo) OTHISPRMODA, COUNT(DISTINCT id) OTHISNRI FROM rile GROUP BY mo_id, MONDO;
 
+-------+---------+-------------+----------+
 
| MONDO | mo_id   | OTHISPRMODA | OTHISNRI |
 
+-------+---------+-------------+----------+
 
| AAA   | 2630674 |       159.9 |        1 |
 
| AAA   | 2591332 |        69.9 |        1 |
 
| AAA   | 2352674 |          99 |        1 |
 
| AAA   | 2891223 |        74.9 |        1 |
 
+-------+---------+-------------+----------+
 
4 rows in set (0.029 sec)
 
 
 
MariaDB [mytest]> SELECT 'AAA' MONDO, mo_id, MODA(prezzo) OTHISPRMODA, COUNT(DISTINCT id) OTHISNRI FROM rile GROUP BY mo_id, MONDO;
 
+-------+---------+-------------+----------+
 
| MONDO | mo_id   | OTHISPRMODA | OTHISNRI |
 
+-------+---------+-------------+----------+
 
| AAA   | 2630674 | 159.9       |        1 |
 
| AAA   | 2591332 | 69.9        |        1 |
 
| AAA   | 2352674 | 99          |        1 |
 
| AAA   | 2891223 | 74.9        |        1 |
 
+-------+---------+-------------+----------+
 
4 rows in set (0.008 sec)

MTR columnstore/basic.mcs98_moda_function failed

columnstore/basic.mcs98_moda_function [ fail ]
Test ended at 2022-08-15 14:44:04

A test case in the basic MTR suite failed. It looks like the MTR test case just need to be updated

--- /usr/share/mysql-test/suite/columnstore/basic/r/mcs98_moda_function.result	2022-08-15 14:09:00.425925004 +0000
 
+++ /usr/share/mysql-test/suite/columnstore/basic/r/mcs98_moda_function.reject	2022-08-15 14:44:04.404083813 +0000
 
@@ -142,9 +142,9 @@
 
 d2
 
 SELECT tu, moda(i) FROM t2 GROUP BY tu;
 
 tu	moda(i)
 
-10	-74836460
 
 1	7483646
 
 NULL	0
 
+10	-74836460
 
 SELECT floor(moda(rl)) FROM t2;
 
 floor(moda(rl))
 
 234
 
@@ -156,6 +156,6 @@
 
 234
 
 SELECT t, moda(tu) 'q1' FROM t2 GROUP BY t HAVING moda(tu) > 5;
 
 t	q1
 
-10	10
 
 -10	10
 
+10	10
 
 DROP DATABASE mcs98_db;

Comment by Daniel Lee (Inactive) [ 2022-08-17 ]

Build verified: 22.08-1 (#5304)

The MTR issue is in the test case itself. The return result is correct, but in a different order.
I will update the test case along with other test cases that need to be updated.

Reference result

   141	SELECT d2 FROM t2 WHERE d2 < (SELECT moda(d2) FROM t2);
 
   142	d2
 
   143	SELECT tu, moda(i) FROM t2 GROUP BY tu;
 
   144	tu	moda(i)
 
   145	10	-74836460
 
   146	1	7483646
 
   147	NULL	0
 
   148	SELECT floor(moda(rl)) FROM t2;
 
   149	floor(moda(rl))
 
   150	234
 
 
 
   157	SELECT t, moda(tu) 'q1' FROM t2 GROUP BY t HAVING moda(tu) > 5;
 
   158	t	q1
 
   159	10	10
 
   160	-10	10
 
   161	DROP DATABASE mcs98_db;

rejected result

141	SELECT d2 FROM t2 WHERE d2 < (SELECT moda(d2) FROM t2);
 
   142	d2
 
   143	SELECT tu, moda(i) FROM t2 GROUP BY tu;
 
   144	tu	moda(i)
 
   145	1	7483646
 
   146	NULL	0
 
   147	10	-74836460
 
   148	SELECT floor(moda(rl)) FROM t2;
 
   149	floor(moda(rl))
 
   150	234
 
 
 
   157	SELECT t, moda(tu) 'q1' FROM t2 GROUP BY t HAVING moda(tu) > 5;
 
   158	t	q1
 
   159	-10	10
 
   160	10	10
 
   161	DROP DATABASE mcs98_db;

Generated at Thu Feb 08 02:55:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.