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.
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
AS product_count FROM products GROUP BY category;
AS product_count FROM products GROUP BY category ORDER BY product_id, product_name;
AS product_count FROM products GROUP BY category ORDER BY product_id, product_name, category, unit_price, stock_quantity;
AS product_count
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
SELECT category, COUNT
SELECT category, COUNT
– 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
FROM products
GROUP BY category, stock_status;
SELECT
AS product_count
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
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.