1--source include/have_innodb.inc
2--source include/big_test.inc
3
4--echo #
5--echo # Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE
6--echo # CALLED FROM A FUNCTION
7--echo #
8
9call mtr.add_suppression("MySQL is trying to drop table");
10
11DELIMITER $$;
12CREATE PROCEDURE cachedata(
13  IN obj_id BIGINT UNSIGNED,
14  IN start  DATETIME,
15  IN end    DATETIME
16)
17
18cachedata:BEGIN
19  DECLARE cache_count BIGINT;
20
21  SET @timestamp := NOW();
22
23  CREATE TEMPORARY TABLE IF NOT EXISTS cachedata (
24    timestamp    DATETIME,
25    object_id    BIGINT UNSIGNED NOT NULL,
26    start        DATETIME,
27    end          DATETIME,
28    seqno        BIGINT AUTO_INCREMENT,
29    value        FLOAT,
30    PRIMARY KEY (seqno),
31    INDEX (timestamp),
32    INDEX (object_id, start, end)
33  ) ENGINE=INNODB;
34
35  DELETE FROM cachedata WHERE
36    timestamp < DATE_SUB(@timestamp, INTERVAL 15 SECOND);
37
38  SELECT count(*) INTO cache_count FROM cachedata WHERE
39    object_id = obj_id
40    AND start = start
41    AND end = end;
42
43  IF cache_count > 0 THEN LEAVE cachedata;
44  END IF;
45
46  INSERT INTO cachedata (timestamp, object_id, start, end, value) VALUES
47    (@timestamp, obj_id, start, end, 1234),
48    (@timestamp, obj_id, start, end, 4567),
49    (@timestamp, obj_id, start, end, 8901),
50    (@timestamp, obj_id, start, end, 1234),
51    (@timestamp, obj_id, start, end, 4567),
52    (@timestamp, obj_id, start, end, 8901),
53    (@timestamp, obj_id, start, end, 1234),
54    (@timestamp, obj_id, start, end, 4567),
55    (@timestamp, obj_id, start, end, 8901),
56    (@timestamp, obj_id, start, end, 1234),
57    (@timestamp, obj_id, start, end, 4567),
58    (@timestamp, obj_id, start, end, 8901),
59    (@timestamp, obj_id, start, end, 2345),
60    (@timestamp, obj_id, start, end, 1234),
61    (@timestamp, obj_id, start, end, 4567),
62    (@timestamp, obj_id, start, end, 8901),
63    (@timestamp, obj_id, start, end, 2345),
64    (@timestamp, obj_id, start, end, 1234),
65    (@timestamp, obj_id, start, end, 4567),
66    (@timestamp, obj_id, start, end, 8901),
67    (@timestamp, obj_id, start, end, 2345);
68
69END$$
70
71
72CREATE FUNCTION get_cache(
73  obj_id   BIGINT UNSIGNED,
74  start    DATETIME,
75  end      DATETIME
76)
77  RETURNS FLOAT
78  READS SQL DATA
79BEGIN
80  DECLARE result FLOAT;
81
82  CALL cachedata(obj_id, start, end);
83
84  SELECT SUM(value) INTO result FROM cachedata WHERE
85    object_id = obj_id
86    AND start = start
87    AND end = end;
88
89  RETURN result;
90END$$
91
92DELIMITER ;$$
93
94let $i = 30;
95while ($i)
96{
97 SELECT get_cache(1, '2014-01-01', '2014-02-01');
98 select sleep(1);
99 dec $i;
100}
101
102DROP FUNCTION get_cache;
103DROP PROCEDURE cachedata;
104