Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 542 insertions, 0 deletions

diff --git a/mysql-test/main/olap.test b/mysql-test/main/olap.test
new file mode 100644
index 00000000..9e4f15d5
--- /dev/null
+++ b/mysql-test/main/olap.test
@@ -0,0 +1,542 @@
+set @sav_dpi= @@div_precision_increment;
+set div_precision_increment= 5;
+show variables like 'div_precision_increment';
+
+create table t1 (product varchar(32), country_id int not null, year int, profit int);
+insert into t1  values ( 'Computer', 2,2000, 1200),
+( 'TV', 1, 1999, 150),
+( 'Calculator', 1, 1999,50),
+( 'Computer', 1, 1999,1500),
+( 'Computer', 1, 2000,1500),
+( 'TV', 1, 2000, 150),
+( 'TV', 2, 2000, 100),
+( 'TV', 2, 2000, 100),
+( 'Calculator', 1, 2000,75),
+( 'Calculator', 2, 2000,75),
+( 'TV', 1, 1999, 100),
+( 'Computer', 1, 1999,1200),
+( 'Computer', 2, 2000,1500),
+( 'Calculator', 2, 2000,75),
+( 'Phone', 3, 2003,10)
+;
+
+create table t2 (country_id int primary key, country char(20) not null);
+insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
+
+# First simple rollups, with just grand total
+select product, sum(profit) from t1 group by product;
+select product, sum(profit) from t1 group by product with rollup;
+select product, sum(profit) from t1 group by 1 with rollup;
+select product, sum(profit),avg(profit) from t1 group by product with rollup;
+
+# Sub totals
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
+explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
+select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
+
+# limit
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
+
+select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
+select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
+
+# Test of having
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
+
+# Functions
+select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
+select product, sum(profit)/count(*) from t1 group by product with rollup;
+select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
+#check after fix MDEV-29601
+--disable_service_connection
+select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
+--enable_service_connection
+
+# Joins
+select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
+
+# Derived tables and sub selects
+select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
+select product from t1 where exists (select product, country_id , sum(profit) from t1 as t2 where t1.product=t2.product group by product, country_id with rollup having sum(profit) > 6000);
+
+# The following doesn't return the expected answer, but this is a limitation
+# in the implementation so we should just document it
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
+select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
+
+# Error handling
+
+# Cube is not yet implemented
+--error 1235
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
+--error 1235
+explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
+--error 1235
+select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube union all select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
+
+drop table t1,t2;
+
+#
+# Test bug with const tables
+#
+
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES(100);
+CREATE TABLE t2 (i int);
+INSERT INTO t2 VALUES (100),(200);
+SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
+SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
+drop table t1,t2;
+
+#bug #4767: ROLLUP with LEFT JOIN 
+
+CREATE TABLE user_day(
+  user_id INT NOT NULL,
+  date DATE NOT NULL,
+  UNIQUE INDEX user_date (user_id, date)
+);
+
+INSERT INTO user_day VALUES
+  (1, '2004-06-06' ),
+  (1, '2004-06-07' ),
+  (2, '2004-06-06' );
+
+SELECT
+       d.date AS day,
+       COUNT(d.user_id) as sample,
+       COUNT(next_day.user_id) AS not_cancelled
+  FROM user_day d
+       LEFT JOIN user_day next_day 
+       ON next_day.user_id=d.user_id AND 
+          next_day.date= DATE_ADD( d.date, interval 1 day )
+  GROUP BY day;
+
+SELECT
+       d.date AS day,
+       COUNT(d.user_id) as sample,
+       COUNT(next_day.user_id) AS not_cancelled
+  FROM user_day d
+       LEFT JOIN user_day next_day 
+       ON next_day.user_id=d.user_id AND 
+          next_day.date= DATE_ADD( d.date, interval 1 day )
+  GROUP BY day
+    WITH ROLLUP;
+
+DROP TABLE user_day;
+
+#
+# Tests for bugs #8616, #8615: distinct sum with rollup
+#
+
+CREATE TABLE t1 (a int, b int);
+
+INSERT INTO t1 VALUES
+  (1,4),
+  (2,2), (2,2),
+  (4,1), (4,1), (4,1), (4,1),
+  (2,1), (2,1);
+
+SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+
+SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
+  GROUP BY a WITH ROLLUP;
+
+SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+
+SELECT b, a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+SELECT DISTINCT b,a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+
+ALTER TABLE t1 ADD COLUMN c INT;
+SELECT a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
+SELECT distinct a,b,sum(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
+
+DROP TABLE t1;
+
+#
+# Tests for bugs #8617: SQL_CACL_FOUND_ROWS with rollup and limit 
+#
+
+CREATE TABLE t1 (a int, b int);
+
+INSERT INTO t1 VALUES
+  (1,4),
+  (2,2), (2,2),
+  (4,1), (4,1), (4,1), (4,1),
+  (2,1), (2,1);
+
+SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
+SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
+
+DROP TABLE t1;
+
+#
+# Tests for bug #9681: ROLLUP in subquery for derived table wiht 
+#                      a group by field declared as NOT NULL
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
+SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
+
+DROP TABLE t1;
+set div_precision_increment= @sav_dpi;
+
+#
+# Tests for bug #7914: ROLLUP over expressions on temporary table
+#
+
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
+  GROUP BY a;
+SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
+  GROUP BY a WITH ROLLUP;
+
+SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
+  GROUP BY a;
+SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
+  GROUP BY a WITH ROLLUP;
+
+#enable view protocol after fix MDEV-28535
+--disable_view_protocol
+
+SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
+  FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
+    GROUP BY a WITH ROLLUP;
+
+--enable_view_protocol
+
+DROP TABLE t1;
+
+#
+# Tests for bug #7894: ROLLUP over expressions on group by attributes
+#
+
+CREATE TABLE t1 (a int(11));
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
+SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
+SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+  (1,4),
+  (2,2), (2,2),
+  (4,1), (4,1), (4,1), (4,1),
+  (2,1), (2,1);
+
+SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP; 
+SELECT a,b,SUM(b), a+b as c FROM t2
+  GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
+SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
+  GROUP BY a, b WITH ROLLUP; 
+
+DROP TABLE t1,t2;
+
+#
+# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY 
+#
+
+#enable view protocol after fix MDEV-28536
+--disable_view_protocol
+
+CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, 2);
+
+SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
+
+DROP TABLE t1;
+
+--enable_view_protocol
+
+# Bug #12885(1): derived table specified by a subquery with
+#                ROLLUP over expressions on not nullable group by attributes 
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
+
+DROP TABLE t1;
+
+#
+# Bug #12887 Distinct is not always applied after rollup
+#
+create table t1 ( a varchar(9), b int );
+insert into t1 values('a',1),(null,2);
+select a, max(b) from t1 group by a with rollup;
+select distinct a, max(b) from t1 group by a with rollup;
+drop table t1;
+
+#
+# Bug #20825: rollup puts non-equal values together
+#
+create table t1 (a varchar(22) not null , b int);
+insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
+select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
+select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
+drop table t1;
+
+#
+# Bug #24856: ROLLUP by const item in a query with DISTINCT
+#
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 
+  VALUES (2,10),(3,30),(2,40),(1,10),(2,30),(1,20),(2,10);
+
+SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+SELECT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+SELECT DISTINCT a, b, COUNT(*) FROM t1 GROUP BY a,b WITH ROLLUP;
+
+SELECT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+SELECT DISTINCT 'x', a, SUM(b) FROM t1 GROUP BY 1,2 WITH ROLLUP;
+
+DROP TABLE t1;
+
+# End of 4.1 tests
+
+#
+# Tests for bug #11639: ROLLUP over view executed through filesort
+#
+
+CREATE TABLE t1(id int, type char(1));
+INSERT INTO t1 VALUES
+  (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
+  (6,"B"),(7,"A"),(8,"C"),(9,"A"),(10,"C");
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT type FROM t1 GROUP BY type WITH ROLLUP;
+SELECT type FROM v1 GROUP BY type WITH ROLLUP;
+EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+#
+# Bug #12885(2): view specified by a subquery with
+#                ROLLUP over expressions on not nullable group by attributes 
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE VIEW v1 AS
+  SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+
+DESC v1;
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+#
+# Bug #26830: derived table with ROLLUP 
+#
+
+CREATE TABLE t1 (a int, KEY (a));
+INSERT INTO t1 VALUES (3), (1), (4), (1), (3), (1), (1);
+
+SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#31095: Unexpected NULL constant caused server crash.
+--echo #
+create table t1(a int);
+insert into t1 values (1),(2),(3);
+select count(a) from t1 group by null with rollup;
+drop table t1;
+--echo ##############################################################
+
+#
+# Bug #32558: group by null-returning expression with rollup causes crash
+#
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(0);
+SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #48131: crash group by with rollup, distinct,
+--echo #             filesort, with temporary tables
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (100);
+
+SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP
+--echo #             and only const tables
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+
+SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP;
+
+DROP TABLE t1, t2;
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-16190 Server crashes in Item_null_result::field_type on SELECT with time field, ROLLUP and HAVING
+--echo #
+CREATE TABLE t1 (t TIME) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('12:12:12');
+SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00';
+DROP TABLE t1;
+
+CREATE TABLE t1 (t TIME) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('12:12:12'),('12:12:13');
+SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00';
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17830 Server crashes in Item_null_result::field_type upon SELECT with CHARSET(date) and ROLLUP
+--echo #
+
+--echo # Note, different MariaDB versions can return different results
+--echo # in the two rows (such as "latin1" vs "binary"). This is wrong.
+--echo # Both lines should return equal values.
+--echo # The point in this test is to make sure it does not crash.
+--echo # As this is a minor issue, bad result will be fixed
+--echo # in a later version, presumably in 10.4.
+
+CREATE TABLE t (d DATE) ENGINE=MyISAM;
+INSERT INTO t VALUES ('2018-12-12');
+SELECT CHARSET(d) AS f FROM t GROUP BY d WITH ROLLUP;
+DROP TABLE t;
+
+
+--echo #
+--echo # MDEV-14041 Server crashes in String::length on queries with functions and ROLLUP
+--echo #
+
+--disable_ps2_protocol 
+#enable view protocol after fix MDEV-28538
+--disable_view_protocol
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT GET_LOCK( 'foo', 0 );
+SELECT HEX( RELEASE_LOCK( 'foo' ) ) AS f FROM t1 GROUP BY f WITH ROLLUP;
+DROP TABLE t1;
+
+--enable_view_protocol
+--enable_ps2_protocol
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT i FROM t1 GROUP BY i WITH ROLLUP
+UNION ALL
+SELECT ELT( FOUND_ROWS(), 1 ) f FROM t1 GROUP BY f WITH ROLLUP;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY NULLIF( CONVERT('', DATE), '2015-10-15' ) WITH ROLLUP;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
+
+--echo #
+--echo # MDEV-12886 Different default for INT and BIGINT column in a VIEW for a SELECT with ROLLUP
+--echo #
+
+CREATE TABLE t1 (a int(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+DESCRIBE v1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a bigint(11) NOT NULL);
+INSERT INTO t1 VALUES (1),(2);
+CREATE OR REPLACE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
+DESCRIBE v1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-9410 VIEW over a ROLLUP query reports too large columns
+--echo #
+
+CREATE TABLE t1 (a int(10) NOT NULL, b int(20) NOT NULL);
+INSERT INTO t1 VALUES (1,1),(2,2);
+
+CREATE VIEW v1 AS SELECT a,b FROM t1;
+DESC v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT a, b FROM t1 GROUP BY a,b;
+DESC v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS SELECT a, b FROM t1 GROUP BY a,b WITH ROLLUP;
+DESC v1;
+DROP VIEW v1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-15576: Server crashed in Cached_item_str::cmp / sortcmp or
+--echo # Assertion `item->null_value' failed in
+--echo # Type_handler_temporal_result::make_sort_key upon SELECT with NULLIF
+--echo # and ROLLUP
+--echo #
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+--disable_warnings
+SELECT NULLIF( CAST( 'foo' AS DATE ), NULL & 'bar' ) AS f FROM t1 GROUP BY f WITH ROLLUP;
+--enable_warnings
+DROP TABLE t1;
+
+--echo # End of 10.3 Tests