Fix for union with duplicate rows is:
=== modified file 'sql/sql_union.cc'
— sql/sql_union.cc 2013-06-19 11:32:14 +0000
+++ sql/sql_union.cc 2013-06-26 08:43:55 +0000
@@ -73,20 +73,12 @@ bool select_union::send_data(List<Item>
return 0;
}

• if ((write_err= table->file->ha_write_tmp_row(table->record[0])))
  + if ((write_err= table->file->ha_write_row(table->record[0])))
  {
• if (write_err == HA_ERR_FOUND_DUPP_KEY)
• { - /* - Inform upper level that we found a duplicate key, that should not - be counted as part of limit - */ - return -1; - }

  /* create_internal_tmp_table_from_heap will generate error if needed */
  if (table->file->is_fatal_error(write_err, HA_CHECK_DUP) &&
  create_internal_tmp_table_from_heap(thd, table,

• tmp_table_param.start_recinfo,
  + tmp_table_param.start_recinfo,
  &tmp_table_param.recinfo,
  write_err, 1))
  return 1;

Test suite of the patch which added removed lines passes. Now testing why it works and if it is correct fix.

I was wrong, only part of changed test suit passes.

the bug fixed here is present in 5.6 so need to revert everything to our code.

Above problem with unions fixed.

Started crash in query_cache.test in:
void Query_cache::invalidate_table(THD *thd, TABLE *table)
{
invalidate_table(thd, (uchar*) table->s->table_cache_key.str,
table->s->table_cache_key.length);
}

It looks like invalidating of TABLE_LIST which refers to TABLE wich was freed already... (drop table t1, t2, t3 command).

Problem is with invalidating temporary table:

– source include/have_query_cache.inc
set GLOBAL query_cache_size=1048576;
create temporary table t3 (i int not null);
drop table t3;

in 5.6 table_list->table is NULL

The problem is that this patch was partially pulled:
bzr log c 2876.430.11-----------------------------------------------------------
revno: 2876.430.11
committer: Dmitry Lenev <Dmitry.Lenev@oracle.com>
branch nick: mysql-trunk-bug27480-2
timestamp: Sat 2011-03-26 13:56:27 +0300
message:
Patch for Bug#11746602 (27480: Extend CREATE TEMPORARY TABLES
privilege to allow temp table operations).

The problem was that user with CREATE TEMPORARY TABLES
privilege which was able to create temporary table, needed
additional privileges which also applied to non-temporary
tables to do any useful work on it. As result it was
impossible to allow user to work with temporary tables
without also granting extra privileges on normal tables.

The idea of this patch is to allow any relevant operation
on a temporary table which already exists. Creation of
temporary table still requires CREATE TEMPORARY TABLES
privilege on database in which this table to be created.

This appeared to be partially taken also (code which preopen temporary tables just skipped) but all tags put to the bitmap of commands

revno: 2876.430.1
committer: Alexander Nozdrin <alexander.nozdrin@oracle.com>
branch nick: mysql-trunk-bug27480
timestamp: Mon 2011-01-17 19:27:07 +0300
message:
Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege
to allow temp table operations):

• Move opening of temporary tables (in normal case) to the stage of
  statement execution which precedes privilege checking. In the upcoming
  patches we will use this fact during privilege checks to easily and
  cheaply determine if table is temporary;

• make open_table() to work with base tables and views only.
  It will be renamed to open_base_table_or_view()
  in a follow-up patch.

• introduce open_temporary_table() to open temporary tables
  (similar to open_table());

• introduce open_temporary_tables() to open temporary tables
  corresponding to table list elements;

• introduce a new "command flag" (CF_PREOPEN_TMP_TABLES) to mark
  statements that work with temporary tables, thus temporary tables
  should be opened for those statements;

• open temporary tables in a unified way in the beginning of
  the statements marked with CF_PREOPEN_TMP_TABLES flag;

• introduce a new "command flag" (CF_HA_CLOSE) to mark statements
  for which open handlers (by HANDLER OPEN) should be closed;

• close open handlers in a unified way in the beginning of
  the statements marked with CF_HA_CLOSE flag.

the revision above merged

I will check failure of ps.test.

the result was correct

I will check crash of sp-dynamic.test

Test suite:
delimiter |;

create table t1 (id integer primary key auto_increment,
stmt_text char(35), status varchar(20))|
insert into t1 (stmt_text) values
("select 1"), ("flush tables"), ("handler t1 open as ha"),
("analyze table t1"), ("check table t1"), ("checksum table t1"),
("check table t1"), ("optimize table t1"), ("repair table t1"),
("describe extended select * from t1"),
("help help"), ("show databases"), ("show tables"),
("show table status"), ("show open tables"), ("show storage engines"),
("insert into t1 (id) values (1)"), ("update t1 set status=''"),
("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"),
("create view v1 as select 1"), ("alter view v1 as select 2"),
("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"),
("drop table t2")|
create procedure p1()
begin
declare v_stmt_text varchar(255);
declare v_id integer;
declare done int default 0;
declare c cursor for select id, stmt_text from t1;
declare continue handler for 1295 – ER_UNSUPPORTED_PS
set @status='not supported';
declare continue handler for 1064 – ER_SYNTAX_ERROR
set @status='syntax error';
declare continue handler for sqlstate '02000' set done = 1;

prepare update_stmt from "update t1 set status=? where id=?";
open c;
repeat
if not done then
fetch c into v_id, v_stmt_text;
set @id=v_id, @stmt_text=v_stmt_text;
set @status="supported";
prepare stmt from @stmt_text;
execute update_stmt using @status, @id;
end if;
until done end repeat;
deallocate prepare update_stmt;
end|
call p1()|
select * from t1|
drop procedure p1|
drop table t1|

It is error 1295 (This command is not supported in the prepared statement protocol yet). Everything looks OK...

set_error_status called with NULL condition

The problem is that THD::m_internal_handler is not set.

part of following cset is lost:
revno: 3313
committer: Alexander Nozdrin <alexander.nozdrin@oracle.com>
branch nick: mysql-trunk
timestamp: Thu 2011-07-28 12:12:40 +0400
message:
Pre-requisite patch for Bug#11763162 (55843 - Handled condition
appears as not handled).

The patch introduces Diagnostics_area::m_error_condition and
adds the code to set it properly.

Diagnostics_area::m_error_condition is intended to store a pointer
to a MYSQL_ERROR object corresponding to the error state of
Diagnostics_area.

above revision checked and fix pushed.

ps_1general it looks like only output changed...

Fixed and pushed trivial issues with ps_tests.

ps_ddl has some problem with temporary table

It does not allow to create temporary table if there is normal one

create table t1 (a int);
create temporary table t1 AS SELECT 1;
drop temporary table t1;
drop table t1;

It looks like main problem in ps*.test is absence of errors, so I take it.

5.6 also does not show errors in the procedures.

Yes. 10.0 show errors but 5.6 and 10.0-monty does not. it looks like a new feature (should be discussed).

I take view.test.

collate should not prevent insertion into the view:
#

1. Collation with view update
   #
   create table t1 (s1 char);
   create view v1 as select s1 collate latin1_german1_ci as s1 from t1;
   insert into v1 values ('a');
   drop view v1;
   drop table t1;

Only indirect reference via SP do not give the error.

CREATE TEMPORARY TABLE t1 (a int);
CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT FROM t1);
– error ER_VIEW_SELECT_TMPTABLE
CREATE VIEW v1 AS SELECT f1();
DROP FUNCTION f1;
DROP TABLE t1;

table t1 in lex->query_tables has no correct view link...

sorry above not view but table (table is not opened)

temporary table opening solved.
known results changes applied

changes in hh_* (good/bad) functions is OK.

sqlexception
-Warnings:
-Error 1136 Column count doesn't match value count at row 1
call bug6900_9074(1)|
-23000
-23000
-Warnings:
-Error 1062 Duplicate entry 'a' for key 'w'
+sqlexception
+sqlexception

is also OK (exactly the same result in 5.6)

@@ -2628,17 +2588,13 @@
row_count()
1
call bug4905()|
-Warnings:
-Error 1062 Duplicate entry '1' for key 'PRIMARY'
select row_count()|
row_count()
--1
+0
call bug4905()|
-Warnings:
-Error 1062 Duplicate entry '1' for key 'PRIMARY'
select row_count()|
row_count()
--1
+0

Also the same in 5.6

I will check ps_ddl. (I thought ir was fixed once)

Com_stmt_reprepare do not indicate repreparation of the statement due to changing metadata.

Dealing with reprepare observer looks like changed a lot... added push/pop which is not present in our code.

in 10.0 reprepare observer has no mark that the query should be reprepared.

thd->m_reprepare_observer is not set (???) so the change in tables is not detected

above was another statement execution. so check_and_update_table_version checks one table and it has correct type and version.

in 5.6 for t2

(gdb) p m_table_ref_type
$1 = TABLE_REF_NULL

when 10.0 checks t1 and of cocourse

(gdb) p m_table_ref_type
$1 = TABLE_REF_BASE_TABLE

the problem is that in 5.6 t2 (temporary table) is opened while in 10.0 it is not.

yet another part of following is lost

revno: 2876.430.1
committer: Alexander Nozdrin <alexander.nozdrin@oracle.com>
branch nick: mysql-trunk-bug27480
timestamp: Mon 2011-01-17 19:27:07 +0300
message:
Prerequisite patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege
to allow temp table operations):

• Move opening of temporary tables (in normal case) to the stage of
  statement execution which precedes privilege checking. In the upcoming
  patches we will use this fact during privilege checks to easily and
  cheaply determine if table is temporary;

• make open_table() to work with base tables and views only.
  It will be renamed to open_base_table_or_view()
  in a follow-up patch.

• introduce open_temporary_table() to open temporary tables
  (similar to open_table());

• introduce open_temporary_tables() to open temporary tables
  corresponding to table list elements;

• introduce a new "command flag" (CF_PREOPEN_TMP_TABLES) to mark
  statements that work with temporary tables, thus temporary tables
  should be opened for those statements;

• open temporary tables in a unified way in the beginning of
  the statements marked with CF_PREOPEN_TMP_TABLES flag;

• introduce a new "command flag" (CF_HA_CLOSE) to mark statements
  for which open handlers (by HANDLER OPEN) should be closed;

• close open handlers in a unified way in the beginning of
  the statements marked with CF_HA_CLOSE flag.

signal_demo3

mysql 5.6 has different diff on the same test but there is so much skipped reports about signals

checking signal.test

equal to 5.6

I'll check vcol test suite

it looks like vcol is not recalculated on change of expression during alter table in vcol_non_sorted_columns_myisam

yes, it was doing "inplace" alter table when it should be copy to change stored virtual column values

fixed by serg

I have started with innodb suite

innodb_bug14007649 has a patch http://lists.mysql.com/commits/144170

in both (xtradb/innodb) the patch is present

This part of the task (merge to 10.0-monty tree) is finished