func_analyse.test

#
# Test of procedure analyse
#
-- source include/have_innodb.inc

--disable_warnings
drop table if exists t1,t2;
--enable_warnings
create table t1 (i int, j int, empty_string char(10), bool char(1), d date);
insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6,"","Y","2002-03-04"), (7,8,"","N","2002-03-05");
select count(*) from t1 procedure analyse();
select * from t1 procedure analyse();
select * from t1 procedure analyse(2);
--error ER_WRONG_USAGE
create table t2 select * from t1 procedure analyse();
drop table t1;

SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
EXPLAIN SELECT 1 FROM (SELECT 1) a;

#
# Bug#2813 - analyse does not quote string values in enums from string
#

create table t1 (v varchar(128));
insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd');
select * from t1 procedure analyse();
drop table t1;

#decimal-related test

create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
select * from t1 procedure analyse();
drop table t1;

#
# Bug#10716 - Procedure Analyse results in wrong values for optimal field type
#

create table t1 (d double);
insert into t1 values (100000);
select * from t1 procedure analyse (1,1);
drop table t1;

#
# Bug #14138 ROLLUP and PROCEDURE ANALYSE() hang server
#
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');
select product, sum(profit),avg(profit) from t1 group by product with rollup;
select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse();
drop table t1,t2;

#
# Bug #20305 PROCEDURE ANALYSE() returns wrong M for FLOAT(M, D) and DOUBLE(M, D)
#

create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5));
insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555);
select f1 from t1 procedure analyse(1, 1);
select f2 from t1 procedure analyse(1, 1);
select f3 from t1 procedure analyse(1, 1);
drop table t1;

#
# Bug#46184 Crash, SELECT ... FROM derived table procedure analyze
#
set @optimizer_switch_saved=@@session.optimizer_switch;
set optimizer_switch='derived_merge=off';

CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT);
INSERT INTO t1 VALUES ();
SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE();
DROP TABLE t1;

set @@session.optimizer_switch=@optimizer_switch_saved;

--echo End of 4.1 tests

--echo #
--echo # Bug #48293: crash with procedure analyse, view with > 10 columns, 
--echo #  having clause...
--echo #

CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
                f INT, g INT, h INT, i INT, j INT,k INT);
INSERT INTO t1 VALUES (),();

-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
--echo #should have a derived table
EXPLAIN SELECT * FROM v1;
--echo #should not crash
SELECT * FROM v1 PROCEDURE analyse();
--echo #should not crash
SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse();
--echo #should not crash
SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse();
--echo #should not crash
SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse();
--echo #should not crash
SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse();

DROP VIEW v1;
DROP TABLE t1;

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1),(2);

--echo # should not crash
--error ER_WRONG_USAGE
CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE();

DROP TABLE t1;


--echo End of 5.0 tests

--echo #
--echo # Bug#11765202: Dbug_violation_helper::~Dbug_violation_helper(): Assertion `!_entered' failed.
--echo #

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(2) CHARSET UTF8 NOT NULL);
INSERT INTO t1 VALUES ('e'),('e'),('e-');
SELECT * FROM t1 PROCEDURE ANALYSE();
DROP TABLE t1;

--echo #
--echo # Bug#11756242  48137: PROCEDURE ANALYSE() LEAKS MEMORY WHEN RETURNING NULL
--echo #

CREATE TABLE t1(f1 INT) ENGINE=MYISAM;
CREATE TABLE t2(f2 INT) ENGINE=INNODB;
INSERT INTO t2 VALUES (1);
SELECT DISTINCTROW f1 FROM t1 NATURAL RIGHT OUTER JOIN t2 PROCEDURE ANALYSE();
SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE();
DROP TABLE t1, t2;

--echo End of 5.1 tests

--echo #
--echo # Bug #47338 assertion in handler::ha_external_lock
--echo #

--disable_warnings
drop table if exists t1;
--enable_warnings

CREATE TEMPORARY TABLE t1 (f2 INT, f1 INT, PRIMARY KEY (f1)) ENGINE = MyISAM;

INSERT t1 ( f1 ) VALUES ( 5 );
INSERT t1 ( f1 ) VALUES ( 6 );

ALTER TABLE t1 ENGINE = MyISAM;
ANALYZE TABLE t1;

# Bug #48859 nor reproducible here (was in 6.0)
# Keeping this test case anyways.

SELECT f1,f2 FROM t1 A WHERE f1 BETWEEN 0 AND 1; 

drop table t1;

--echo #
--echo # Bug #13358379   EXPLAIN SELECT ... PROCEDURE ANALYZE CRASHES THE SERVER
--echo #

CREATE TABLE t1 (i INT);
--disable_result_log
EXPLAIN SELECT * FROM t1 PROCEDURE ANALYSE();
--enable_result_log
DROP TABLE t1;

--echo #
--echo # WL#6242: Move "PROCEDURE ANALYSE" implementation
--echo #          to select_send-like class
--echo #
--echo # Coverage tests
--echo #
--echo
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (a INT);

--echo # Trivial query:

SELECT * FROM t1 PROCEDURE ANALYSE();

--echo # PROCEDURE ANALYSE in PS:

INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
PREPARE stmt1 FROM "SELECT * FROM t1 PROCEDURE ANALYSE()";
EXECUTE stmt1;
EXECUTE stmt1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

--echo # PROCEDURE ANALYSE in SP:

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  SELECT * FROM t1 PROCEDURE ANALYSE();
END;|
delimiter ;|
CALL p1();
DROP PROCEDURE p1;

--echo # PROCEDURE ANALYSE in SP cursor:

delimiter |;
CREATE PROCEDURE p1()
BEGIN
  DECLARE c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 CHAR(20);
  DECLARE done INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT * FROM t1 PROCEDURE ANALYSE();
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO c1, c2, c3, c4, c5, c6, c7, c8, c9, c10;
    IF done THEN
      LEAVE read_loop;
    END IF;
  END LOOP;
  CLOSE cur1;
END;|
delimiter ;|

CALL p1();

DROP PROCEDURE p1;

--echo # EXPLAIN with or wihtout PROCEDURE ANALYSE should be same:

-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log

let $query=SELECT * FROM t1, (SELECT * FROM t1) tt1 WHERE t1.a = tt1.a;
eval EXPLAIN $query;
eval EXPLAIN $query PROCEDURE ANALYSE();

CREATE TABLE t2 (i INT, j INT);

--echo # PROCEDURE ANALYSE ignores ROLLUP rows:

INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4);
SELECT i, AVG(j) FROM t2 GROUP BY i WITH ROLLUP;
SELECT i, AVG(j) FROM t2 GROUP BY i WITH ROLLUP PROCEDURE ANALYSE();
SELECT i, AVG(j) FROM t2 GROUP BY i PROCEDURE ANALYSE();

--echo # LIMIT affects original SELECT output, not PROCEDURE ANALYSE output:

SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE();

--echo # Wrong usage: CREATE TABLE/VIEW and INSERT from SELECT:

--error ER_WRONG_USAGE
CREATE TABLE t SELECT * FROM t1 PROCEDURE ANALYSE();
--error ER_VIEW_SELECT_CLAUSE
CREATE VIEW v AS SELECT * FROM t1 PROCEDURE ANALYSE();
--error ER_WRONG_USAGE
INSERT INTO t1 SELECT * FROM t1 PROCEDURE ANALYSE();

--echo # Wrong usage: subquery with PROCEDURE ANALYSE():

--error ER_WRONG_USAGE
SELECT (SELECT * FROM t1 PROCEDURE ANALYSE());
--error ER_WRONG_USAGE
SELECT (SELECT * FROM t1 PROCEDURE ANALYSE()) PROCEDURE ANALYSE;
--error ER_WRONG_USAGE
SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE()) tt1;
--error ER_WRONG_USAGE
SELECT * FROM t1 WHERE a IN (SELECT * FROM t1 PROCEDURE ANALYSE());

--echo # Wrong usage: SELECT ... INTO

--error ER_WRONG_USAGE
SELECT i INTO @a FROM t1 LIMIT 1 PROCEDURE ANALYSE();

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--error ER_WRONG_USAGE
eval SELECT * INTO OUTFILE "$MYSQLTEST_VARDIR/wl6242.outfile" FROM t1 PROCEDURE ANALYSE();

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--error ER_WRONG_USAGE
eval SELECT * INTO DUMPFILE "$MYSQLTEST_VARDIR/wl6242.dumpfile" FROM t1 PROCEDURE ANALYSE();

--echo # Wrong usage: UNION:

--error ER_WRONG_USAGE
SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE ANALYSE();
--error ER_WRONG_USAGE
SELECT * FROM t1 PROCEDURE ANALYSE() UNION SELECT * FROM t1;

--echo # Wrong 1st parameter:

--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE((SELECT 1));
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE('test');
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(-100);
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(3.1415);

--echo # Wrong 2nd parameter:

--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(100, (SELECT 1));
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(100, 'test');
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(100, -100);
--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(100, 3.1415);

--echo # Wrong parameter count:

--error ER_PARSE_ERROR
SELECT * FROM t1 PROCEDURE ANALYSE(100, 200, 300);

DROP TABLE t1, t2;
SET sql_mode = default;
--echo # End of 5.6 tests