group_min_max.test

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· TEST 代码 · 共 895 行 · 第 1/4 页

drop table t1;

#
# Bug #16203: Analysis for possible min/max optimization erroneously
#             returns impossible range
#

CREATE TABLE t1 (a varchar(5), b int(11), PRIMARY KEY (a,b));
INSERT INTO t1 VALUES ('AA',1), ('AA',2), ('AA',3), ('BB',1), ('AA',4);
OPTIMIZE TABLE t1;

SELECT a FROM t1 WHERE a='AA' GROUP BY a;
SELECT a FROM t1 WHERE a='BB' GROUP BY a;

EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a;
EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a;

SELECT DISTINCT a FROM t1 WHERE a='BB';
SELECT DISTINCT a FROM t1 WHERE a LIKE 'B%';
SELECT a FROM t1 WHERE a LIKE 'B%' GROUP BY a;

DROP TABLE t1;


#
# Bug #15102: select distinct returns empty result, select count 
#             distinct > 0 (correct)
#

CREATE TABLE t1 (
   a int(11) NOT NULL DEFAULT '0',
   b varchar(16) COLLATE latin1_general_ci NOT NULL DEFAULT '',
   PRIMARY KEY  (a,b)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

delimiter |;

CREATE PROCEDURE a(x INT)
BEGIN
  DECLARE rnd INT;
  DECLARE cnt INT;

  WHILE x > 0 DO
    SET rnd= x % 100;
    SET cnt = (SELECT COUNT(*) FROM t1 WHERE a = rnd);
    INSERT INTO t1(a,b) VALUES (rnd, CAST(cnt AS CHAR));
    SET x= x - 1;
  END WHILE;
END|

DELIMITER ;|

CALL a(1000);

SELECT a FROM t1 WHERE a=0;
SELECT DISTINCT a FROM t1 WHERE a=0;
SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0;

DROP TABLE t1;
DROP PROCEDURE a;

#
# Bug #18068: SELECT DISTINCT
#

CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));

INSERT INTO t1 (a) VALUES 
  (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
  ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
  ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');

EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;  
SELECT DISTINCT a,a FROM t1 ORDER BY a;  

DROP TABLE t1;

#
# Bug #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
#

CREATE TABLE t1 (id1 INT, id2 INT);
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
CREATE TABLE t3 (id3 INT, id4 INT);
CREATE TABLE t4 (id4 INT);
CREATE TABLE t5 (id5 INT, id6 INT);
CREATE TABLE t6 (id6 INT);

INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1,1);
INSERT INTO t3 VALUES(1,1);
INSERT INTO t4 VALUES(1);
INSERT INTO t5 VALUES(1,1);
INSERT INTO t6 VALUES(1);

# original bug query
SELECT * FROM
t1
  NATURAL JOIN
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));

# inner join swapped
SELECT * FROM
t1
  NATURAL JOIN
(((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6) on t3.id4 = t5.id5) JOIN t2
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5));

# one join less, no ON cond
SELECT * FROM t1 NATURAL JOIN ((t3 join (t5 NATURAL JOIN t6)) JOIN t2);

# wrong error message: 'id2' - ambiguous column
SELECT * FROM
(t2 JOIN (t3 NATURAL JOIN t4, t5 NATURAL JOIN t6)
    ON (t3.id3 = t2.id3 AND t5.id5 = t2.id5))
  NATURAL JOIN
t1;
SELECT * FROM
(t2 JOIN ((t3 NATURAL JOIN t4) join (t5 NATURAL JOIN t6)))
  NATURAL JOIN
t1;

DROP TABLE t1,t2,t3,t4,t5,t6;

#
# Bug#22342: No results returned for query using max and group by
#
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);

explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
INSERT INTO t2 SELECT a,b,b FROM t1;
explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;

DROP TABLE t1,t2;

#
# Bug#24156: Loose index scan not used with CREATE TABLE ...SELECT and similar statements
#

CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
       (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
SELECT max(b), a FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
EXPLAIN SELECT max(b), a FROM t1 GROUP BY a;
FLUSH STATUS;
CREATE TABLE t2 SELECT max(b), a FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
(SELECT max(b), a FROM t1 GROUP BY a) UNION 
 (SELECT max(b), a FROM t1 GROUP BY a);
SHOW STATUS LIKE 'handler_read__e%';
EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION 
 (SELECT max(b), a FROM t1 GROUP BY a);

EXPLAIN SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
  FROM t1 AS t1_outer;
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE EXISTS 
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
  (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
  a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
  a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 
   ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) 
   AND t1_outer1.b = t1_outer2.b;
EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
  FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;

CREATE TABLE t3 LIKE t1;
FLUSH STATUS;
INSERT INTO t3 SELECT a,MAX(b) FROM t1 GROUP BY a;
SHOW STATUS LIKE 'handler_read__e%';
DELETE FROM t3;
FLUSH STATUS;
INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) 
  FROM t1 LIMIT 1;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000;
SHOW STATUS LIKE 'handler_read__e%';
FLUSH STATUS;
DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
                      FROM t1) > 10000;
SHOW STATUS LIKE 'handler_read__e%';

DROP TABLE t1,t2,t3;

#
# Bug#25602: queries with DISTINCT and SQL_BIG_RESULT hint 
#            for which loose scan optimization is applied
#

CREATE TABLE t1 (a int, INDEX idx(a));
INSERT INTO t1 VALUES
  (4), (2), (1), (2), (4), (2), (1), (4),
  (4), (2), (1), (2), (2), (4), (1), (4);

EXPLAIN SELECT DISTINCT(a) FROM t1;
SELECT DISTINCT(a) FROM t1;
EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;
SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1;

DROP TABLE t1;