olap.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:9k
源码类别:

MySQL数据库

开发平台:

Visual C++

  1. --disable_warnings
  2. drop table if exists t1,t2;
  3. --enable_warnings
  4. create table t1 (product varchar(32), country_id int not null, year int, profit int);
  5. insert into t1  values ( 'Computer', 2,2000, 1200),
  6. ( 'TV', 1, 1999, 150),
  7. ( 'Calculator', 1, 1999,50),
  8. ( 'Computer', 1, 1999,1500),
  9. ( 'Computer', 1, 2000,1500),
  10. ( 'TV', 1, 2000, 150),
  11. ( 'TV', 2, 2000, 100),
  12. ( 'TV', 2, 2000, 100),
  13. ( 'Calculator', 1, 2000,75),
  14. ( 'Calculator', 2, 2000,75),
  15. ( 'TV', 1, 1999, 100),
  16. ( 'Computer', 1, 1999,1200),
  17. ( 'Computer', 2, 2000,1500),
  18. ( 'Calculator', 2, 2000,75),
  19. ( 'Phone', 3, 2003,10)
  20. ;
  21. create table t2 (country_id int primary key, country char(20) not null);
  22. insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
  23. # First simple rollups, with just grand total
  24. select product, sum(profit) from t1 group by product;
  25. select product, sum(profit) from t1 group by product with rollup;
  26. select product, sum(profit) from t1 group by 1 with rollup;
  27. select product, sum(profit),avg(profit) from t1 group by product with rollup;
  28. # Sub totals
  29. select product, country_id , year, sum(profit) from t1 group by product, country_id, year;
  30. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
  31. explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
  32. select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
  33. # limit
  34. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 5;
  35. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup limit 3,3;
  36. select product, country_id, count(*), count(distinct year) from t1 group by product, country_id;
  37. select product, country_id, count(*), count(distinct year) from t1 group by product, country_id with rollup;
  38. # Test of having
  39. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having country_id = 1;
  40. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 200;
  41. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup having sum(profit) > 7000;
  42. # Functions
  43. select concat(product,':',country_id) as 'prod', concat(":",year,":") as 'year',1+1, sum(profit)/count(*) from t1 group by 1,2 with rollup;
  44. select product, sum(profit)/count(*) from t1 group by product with rollup;
  45. select left(product,4) as prod, sum(profit)/count(*) from t1 group by prod with rollup;
  46. select concat(product,':',country_id), 1+1, sum(profit)/count(*) from t1 group by concat(product,':',country_id) with rollup;
  47. # Joins
  48. select product, country , year, sum(profit) from t1,t2 where t1.country_id=t2.country_id group by product, country, year with rollup;
  49. # Derived tables and sub selects
  50. select product, `sum` from (select product, sum(profit) as 'sum' from t1 group by product with rollup) as tmp where product is null;
  51. 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);
  52. # The following doesn't return the expected answer, but this is a limitation
  53. # in the implementation so we should just document it
  54. select product, country_id , year, sum(profit) from t1 group by product, country_id, year having country_id is NULL;
  55. select concat(':',product,':'), sum(profit),avg(profit) from t1 group by product with rollup;
  56. # Error handling
  57. # Cube is not yet implemented
  58. --error 1235
  59. select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
  60. --error 1235
  61. explain select product, country_id , year, sum(profit) from t1 group by product, country_id, year with cube;
  62. --error 1235
  63. 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;
  64. drop table t1,t2;
  65. #
  66. # Test bug with const tables
  67. #
  68. CREATE TABLE t1 (i int);
  69. INSERT INTO t1 VALUES(100);
  70. CREATE TABLE t2 (i int);
  71. INSERT INTO t2 VALUES (100),(200);
  72. SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
  73. SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
  74. drop table t1,t2;
  75. #bug #4767: ROLLUP with LEFT JOIN 
  76. CREATE TABLE user_day(
  77.   user_id INT NOT NULL,
  78.   date DATE NOT NULL,
  79.   UNIQUE INDEX user_date (user_id, date)
  80. );
  81. INSERT INTO user_day VALUES
  82.   (1, '2004-06-06' ),
  83.   (1, '2004-06-07' ),
  84.   (2, '2004-06-06' );
  85. SELECT
  86.        d.date AS day,
  87.        COUNT(d.user_id) as sample,
  88.        COUNT(next_day.user_id) AS not_cancelled
  89.   FROM user_day d
  90.        LEFT JOIN user_day next_day 
  91.        ON next_day.user_id=d.user_id AND 
  92.           next_day.date= DATE_ADD( d.date, interval 1 day )
  93.   GROUP BY day;
  94. SELECT
  95.        d.date AS day,
  96.        COUNT(d.user_id) as sample,
  97.        COUNT(next_day.user_id) AS not_cancelled
  98.   FROM user_day d
  99.        LEFT JOIN user_day next_day 
  100.        ON next_day.user_id=d.user_id AND 
  101.           next_day.date= DATE_ADD( d.date, interval 1 day )
  102.   GROUP BY day
  103.     WITH ROLLUP;
  104. DROP TABLE user_day;
  105. #
  106. # Tests for bugs #8616, #8615: distinct sum with rollup
  107. #
  108. CREATE TABLE t1 (a int, b int);
  109. INSERT INTO t1 VALUES
  110.   (1,4),
  111.   (2,2), (2,2),
  112.   (4,1), (4,1), (4,1), (4,1),
  113.   (2,1), (2,1);
  114. SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
  115. SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
  116. SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
  117. SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
  118. SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
  119. SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
  120. SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
  121. SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
  122.   GROUP BY a WITH ROLLUP;
  123. SELECT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
  124. SELECT DISTINCT a, sum(b) FROM t1 GROUP BY a,b WITH ROLLUP;
  125. DROP TABLE t1;
  126. #
  127. # Tests for bugs #8617: SQL_CACL_FOUND_ROWS with rollup and limit 
  128. #
  129. CREATE TABLE t1 (a int, b int);
  130. INSERT INTO t1 VALUES
  131.   (1,4),
  132.   (2,2), (2,2),
  133.   (4,1), (4,1), (4,1), (4,1),
  134.   (2,1), (2,1);
  135. SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
  136. SELECT SQL_CALC_FOUND_ROWS a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP LIMIT 1;
  137. DROP TABLE t1;
  138. #
  139. # Tests for bug #9681: ROLLUP in subquery for derived table wiht 
  140. #                      a group by field declared as NOT NULL
  141. #
  142. CREATE TABLE t1 (a int(11) NOT NULL);
  143. INSERT INTO t1 VALUES (1),(2);
  144. SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP;
  145. SELECT * FROM ( SELECT a, SUM(a) m FROM  t1 GROUP BY a WITH ROLLUP ) t2;
  146. DROP TABLE t1;
  147. #
  148. # Tests for bug #7914: ROLLUP over expressions on temporary table
  149. #
  150. CREATE TABLE t1 (a int(11));
  151. INSERT INTO t1 VALUES (1),(2);
  152. SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
  153.   GROUP BY a;
  154. SELECT a, SUM(a), SUM(a)+1 FROM (SELECT a FROM t1 UNION select 2) d 
  155.   GROUP BY a WITH ROLLUP;
  156. SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
  157.   GROUP BY a;
  158. SELECT a, SUM(a), SUM(a)+1 FROM (SELECT 1 a UNION select 2) d 
  159.   GROUP BY a WITH ROLLUP;
  160. SELECT a, SUM(a), SUM(a)+1, CONCAT(SUM(a),'x'), SUM(a)+SUM(a), SUM(a)
  161.   FROM (SELECT 1 a, 2 b UNION SELECT 2,3 UNION SELECT 5,6 ) d
  162.     GROUP BY a WITH ROLLUP;
  163. DROP TABLE t1;
  164. #
  165. # Tests for bug #7894: ROLLUP over expressions on group by attributes
  166. #
  167. CREATE TABLE t1 (a int(11));
  168. INSERT INTO t1 VALUES (1),(2);
  169. SELECT a, a+1, SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
  170. SELECT a+1 FROM t1 GROUP BY a WITH ROLLUP;
  171. SELECT a+SUM(a) FROM t1 GROUP BY a WITH ROLLUP;
  172. SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b > 2;
  173. SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING a IS NULL;
  174. SELECT a, a+1 as b FROM t1 GROUP BY a WITH ROLLUP HAVING b IS NULL;
  175. SELECT IFNULL(a, 'TEST') FROM t1 GROUP BY a WITH ROLLUP;
  176. CREATE TABLE t2 (a int, b int);
  177. INSERT INTO t2 VALUES
  178.   (1,4),
  179.   (2,2), (2,2),
  180.   (4,1), (4,1), (4,1), (4,1),
  181.   (2,1), (2,1);
  182. SELECT a,b,SUM(b) FROM t2 GROUP BY a,b WITH ROLLUP; 
  183. SELECT a,b,SUM(b), a+b as c FROM t2
  184.   GROUP BY a,b WITH ROLLUP HAVING c IS NULL;
  185. SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 
  186.   GROUP BY a, b WITH ROLLUP; 
  187. DROP TABLE t1,t2;
  188. #
  189. # Test for bug #11543: ROLLUP query with a repeated column in GROUP BY 
  190. #
  191. CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL);
  192. INSERT INTO t1 VALUES (1, 1);
  193. INSERT INTO t1 VALUES (1, 2);
  194. SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP;
  195. DROP TABLE t1;
  196. #
  197. # Bug #11885:  derived table specified by a subquery with
  198. #              ROLLUP over expressions on not nullable group by attributes 
  199. #
  200. CREATE TABLE t1 (a int(11) NOT NULL);
  201. INSERT INTO t1 VALUES (1),(2);
  202. SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
  203. SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t;
  204. DROP TABLE t1;
  205. #
  206. # Bug #12887 Distinct is not always applied after rollup
  207. #
  208. create table t1 ( a varchar(9), b int );
  209. insert into t1 values('a',1),(null,2);
  210. select a, max(b) from t1 group by a with rollup;
  211. select distinct a, max(b) from t1 group by a with rollup;
  212. drop table t1;
  213. # End of 4.1 tests