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

ORDER BY clause if not included in the GROUP BY clause fails

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 6.3.1
    • 23.10.2
    • None
    • None
    • Server version: 10.5.5-MariaDB MariaDB Server
      Columnstore: 1.5.4-1
    • 2023-11, 2023-12, 2024-1

    Description

      ORDER BY clause if not included in the GROUP BY clause fails on Columnstore.

      Repro -
      DROP TABLE IF EXISTS t1, t2;

      CREATE TABLE t1 (
      spID int(10) unsigned,
      userID int(10) unsigned,
      score smallint(5) unsigned,
      date date
      ) ENGINE=Columnstore;

      INSERT INTO t1 VALUES (1,1,1,'','0000-00-00');

      CREATE TABLE t2 (
      userID int(10) unsigned NOT NULL,
      niName char(15)
      ) ENGINE=Columnstore;

      INSERT INTO t2 VALUES (1,'name');

      SELECT t1.spID, t2.userid, MIN(t1.score)
      FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date;
      ERROR 1815 (HY000): Internal error: IDB-2021: 'test.t1.date' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.

      On Innodb
      ------------
      SELECT t1.spID, t2.userid, MIN(t1.score)
      -> FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date;
      Empty set (0.001 sec)

      Attachments

        Issue Links

          Activity

            susil.behera Susil Behera added a comment -

            Performed the following test cases as part the verification
            Build info:
            Columnstore_commit_hash = 1122b64
            Columnstore_version = 23.10.0
            RockyLinux 8 Multi-node

            Testcases executed:
            Original problem testcase>
            DROP TABLE IF EXISTS t1, t2;

            CREATE TABLE t1 (
            spID int(10) unsigned,
            userID int(10) unsigned,
            score smallint(5) unsigned,
            date date
            ) ENGINE=Columnstore;

            INSERT INTO t1 VALUES (1,1,1,'','0000-00-00');

            CREATE TABLE t2 (
            userID int(10) unsigned NOT NULL,
            niName char(15)
            ) ENGINE=Columnstore;

            INSERT INTO t2 VALUES (1,'name');

            SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date; – problem repro'd on older build
            SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; – problem repro'd on older build
            SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID; – problem repro'd on older build
            SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;

            New Test cases>
            CREATE TABLE products (
            product_id INT,
            product_name VARCHAR(100),
            category VARCHAR(50),
            unit_price DECIMAL(10, 2),
            stock_quantity INT
            ) ENGINE=Columnstore;

            INSERT INTO products VALUES
            (1, 'Laptop', 'Electronics', 1200.00, 50),
            (2, 'Smartphone', 'Electronics', 800.00, 100),
            (3, 'Coffee Maker', 'Appliances', 50.00, 30),
            (4, 'Backpack', 'Fashion', 40.00, 80),
            (5, 'Desk Chair', 'Furniture', 150.00, 20);

            – Using Aggregate Function SUM
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category;
            – With ROLL UP
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category WITH ROLLUP;
            – Using Aggregate Function SUM with ORDER BY
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY category;
            – Using Aggregate Function SUM with ORDER BY aggregate function
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY total_stock;
            – Using Aggregate Function SUM with ORDER BY non-select/non-group-by column
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id;
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id ASC;
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id DESC;
            – Grouping by Multiple Columns
            SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            SELECT category, unit_price, AVG(stock_quantity) AS avg_quantity FROM products GROUP BY category, unit_price;
            SELECT category, unit_price, AVG(stock_quantity) AS avg_quantity FROM products GROUP BY category, unit_price ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            – Grouping by a Single Column Number
            SELECT category, COUNT AS product_count FROM products GROUP BY category;
            SELECT category, COUNT AS product_count FROM products GROUP BY category ORDER BY product_id, product_name;
            SELECT category, COUNT AS product_count FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            – Using Aggregate Function MAX
            SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category;
            SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category ORDER BY product_id, product_name;
            SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            – Using Aggregate Function SUM with math expression
            SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category;
            SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category ORDER BY product_id, product_name;
            SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            – Filtering Groups with HAVING Clause
            SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100;
            SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100 ORDER BY product_id, product_name;
            SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100 ORDER BY product_id, product_name, category, unit_price, stock_quantity;
            – Grouping by Expression
            SELECT
            category,
            CASE
            WHEN stock_quantity > 50 THEN 'High Stock'
            WHEN stock_quantity <= 50 AND stock_quantity > 0 THEN 'Low Stock'
            ELSE 'Out of Stock'
            END AS stock_status,
            COUNT AS product_count
            FROM products
            GROUP BY category, stock_status;

            SELECT
            category,
            CASE
            WHEN stock_quantity > 50 THEN 'High Stock'
            WHEN stock_quantity <= 50 AND stock_quantity > 0 THEN 'Low Stock'
            ELSE 'Out of Stock'
            END AS stock_status,
            COUNT AS product_count
            FROM products
            GROUP BY category, stock_status ORDER BY product_id, product_name;

            Everything looks good except MCOL-5772 which might not be caused by this fix.

            susil.behera Susil Behera added a comment - Performed the following test cases as part the verification Build info: Columnstore_commit_hash = 1122b64 Columnstore_version = 23.10.0 RockyLinux 8 Multi-node Testcases executed: Original problem testcase> DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 ( spID int(10) unsigned, userID int(10) unsigned, score smallint(5) unsigned, date date ) ENGINE=Columnstore; INSERT INTO t1 VALUES (1,1,1,'','0000-00-00'); CREATE TABLE t2 ( userID int(10) unsigned NOT NULL, niName char(15) ) ENGINE=Columnstore; INSERT INTO t2 VALUES (1,'name'); SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid, t1.spID ORDER BY t1.date; – problem repro'd on older build SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; – problem repro'd on older build SELECT t1.spID, t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID; – problem repro'd on older build SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; New Test cases> CREATE TABLE products ( product_id INT, product_name VARCHAR(100), category VARCHAR(50), unit_price DECIMAL(10, 2), stock_quantity INT ) ENGINE=Columnstore; INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 1200.00, 50), (2, 'Smartphone', 'Electronics', 800.00, 100), (3, 'Coffee Maker', 'Appliances', 50.00, 30), (4, 'Backpack', 'Fashion', 40.00, 80), (5, 'Desk Chair', 'Furniture', 150.00, 20); – Using Aggregate Function SUM SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category; – With ROLL UP SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category WITH ROLLUP; – Using Aggregate Function SUM with ORDER BY SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY category; – Using Aggregate Function SUM with ORDER BY aggregate function SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY total_stock; – Using Aggregate Function SUM with ORDER BY non-select/non-group-by column SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id; SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id ASC; SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id DESC; – Grouping by Multiple Columns SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity; SELECT category, unit_price, AVG(stock_quantity) AS avg_quantity FROM products GROUP BY category, unit_price; SELECT category, unit_price, AVG(stock_quantity) AS avg_quantity FROM products GROUP BY category, unit_price ORDER BY product_id, product_name, category, unit_price, stock_quantity; – Grouping by a Single Column Number SELECT category, COUNT AS product_count FROM products GROUP BY category; SELECT category, COUNT AS product_count FROM products GROUP BY category ORDER BY product_id, product_name; SELECT category, COUNT AS product_count FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity; – Using Aggregate Function MAX SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category; SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category ORDER BY product_id, product_name; SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity; – Using Aggregate Function SUM with math expression SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category; SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category ORDER BY product_id, product_name; SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity; – Filtering Groups with HAVING Clause SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100; SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100 ORDER BY product_id, product_name; SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING AVG(unit_price) > 100 ORDER BY product_id, product_name, category, unit_price, stock_quantity; – Grouping by Expression SELECT category, CASE WHEN stock_quantity > 50 THEN 'High Stock' WHEN stock_quantity <= 50 AND stock_quantity > 0 THEN 'Low Stock' ELSE 'Out of Stock' END AS stock_status, COUNT AS product_count FROM products GROUP BY category, stock_status; SELECT category, CASE WHEN stock_quantity > 50 THEN 'High Stock' WHEN stock_quantity <= 50 AND stock_quantity > 0 THEN 'Low Stock' ELSE 'Out of Stock' END AS stock_status, COUNT AS product_count FROM products GROUP BY category, stock_status ORDER BY product_id, product_name; Everything looks good except MCOL-5772 which might not be caused by this fix.
            susil.behera Susil Behera added a comment -

            Closing this ticket.

            susil.behera Susil Behera added a comment - Closing this ticket.

            People

              sergey.zefirov Sergey Zefirov
              susil.behera Susil Behera
              Susil Behera Susil Behera
              Votes:
              1 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.