Gecko [ www.jdcpatala.org ]

Name	Size	Permission
bugs_no_prot_all.test	610 B	-rw-r--r--
bugs_no_prot_none.test	1.79 KB	-rw-r--r--
bugs_ps_prot_all.test	607 B	-rw-r--r--
bugs_ps_prot_none.test	718 B	-rw-r--r--
charset.test	2.19 KB	-rw-r--r--
debug.test	1010 B	-rw-r--r--
eq_range_statistics.test	3.28 KB	-rw-r--r--
filesort_pack-master.opt	21 B	-rw-r--r--
filesort_pack.test	14.06 KB	-rw-r--r--
filesort_pq-master.opt	21 B	-rw-r--r--
filesort_pq.test	242 B	-rw-r--r--
fulltext.test	875 B	-rw-r--r--
general2_no_prot.test	271 B	-rw-r--r--
general2_ps_prot.test	268 B	-rw-r--r--
general_no_prot_all-master.opt	21 B	-rw-r--r--
general_no_prot_all.test	638 B	-rw-r--r--
general_no_prot_none-master.op...	21 B	-rw-r--r--
general_no_prot_none.test	749 B	-rw-r--r--
general_ps_prot_all.test	635 B	-rw-r--r--
general_ps_prot_none.test	746 B	-rw-r--r--
range_no_prot.test	1.51 KB	-rw-r--r--
range_ps_prot.test	264 B	-rw-r--r--
security_no_prot.test	202 B	-rw-r--r--
security_ps_prot.test	199 B	-rw-r--r--
subquery_no_prot-master.opt	21 B	-rw-r--r--
subquery_no_prot.test	271 B	-rw-r--r--
subquery_ps_prot.test	268 B	-rw-r--r--
temp_table.test	2.89 KB	-rw-r--r--

Code Editor : temp_table.test

--source include/have_optimizer_trace.inc
--source include/have_64bit.inc

if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
            + $VIEW_PROTOCOL > 0`)
{
   --skip Need normal protocol
}

SET optimizer_trace_max_mem_size=1048576; # 1MB
SET optimizer_trace="enabled=on,one_line=off";
SET end_markers_in_json="on";

CREATE TABLE tmp (
  pk INT PRIMARY KEY AUTO_INCREMENT,
  col1 CHAR (1)
);

INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');

CREATE TABLE t1 (
  uniq VARCHAR(10),
  col1 VARCHAR(10),
  col2 VARCHAR(1024)
);

INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;

--source include/turn_off_only_full_group_by.inc

# Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause. 
SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 
SELECT * FROM information_schema.OPTIMIZER_TRACE;

# Uses HEAP temporary table
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;   
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 
SELECT * FROM information_schema.OPTIMIZER_TRACE;

select @@tmp_table_size;
SET @old_size= @@tmp_table_size;
SET SESSION tmp_table_size= 1024;

INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;

# Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;   
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 
SELECT * FROM information_schema.OPTIMIZER_TRACE;

SET GLOBAL tmp_table_size= @old_size;

# Temp tables for I_S tables. Uses HEAP temporary table. 
# Converts it to MyISAM packed record format due to heap size limitation
SELECT pool_id FROM information_schema.innodb_buffer_page LIMIT 1;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 
SELECT * FROM information_schema.OPTIMIZER_TRACE;

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
DROP TABLE t1, tmp;

--echo #
--echo # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES
--echo #               ON DISK
--echo #
CREATE TABLE t1 (
  c1 INT AUTO_INCREMENT PRIMARY KEY,
  c2 VARCHAR(250)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t1(c2) VALUES ('b'),('b');
INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;

SET @@max_heap_table_size=1;
SET @@group_concat_max_len= 500;

# This query creates 4 tmp tables, 3 of them aren''t packed without this
# fix. 
# Purpose        Format without fix / with fix
# union result               fixed      packed
# derived tbl result         fixed      packed
# join materialization       fixed      packed
# group_concat internal tbl  packed     packed (not shown in the opt trace)
SELECT c1,GROUP_CONCAT(c2) cc FROM
  (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
  GROUP BY c1
  ORDER BY cc;
--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 
SELECT * FROM information_schema.OPTIMIZER_TRACE;
DROP TABLE t1;
--echo #

${this.title}

Code Editor : temp_table.test