Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5092

group by (moda) on FLOAT result in Internal error: TupleAnnexStep::executeNoOrderBy() IDB-2035: An internal error occurred

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 6.2.3
    • 22.08.1
    • None
    • None
    • 2021-17

    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,

      Attachments

        Issue Links

          Activity

            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
            

            ricardo.galvan Ricardo Galvan added a comment - 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

            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]> 
            
            

            David.Hall David Hall (Inactive) added a comment - 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]>

            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;
            

            dleeyh Daniel Lee (Inactive) added a comment - 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;
            dleeyh Daniel Lee (Inactive) added a comment - - edited

            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;
            

            dleeyh Daniel Lee (Inactive) added a comment - - edited 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;

            People

              dleeyh Daniel Lee (Inactive)
              massimo.disaro Massimo
              Andrey Piskunov Andrey Piskunov (Inactive)
              Daniel Lee Daniel Lee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.