MySQL Bugs: #29769: mistaken warning rolling back UPDATE with JOIN and error in WHERE clause

Bug #29769	mistaken warning rolling back UPDATE with JOIN and error in WHERE clause
Submitted:	12 Jul 2007 18:27	Modified:	31 Jul 2007 15:33
Reporter:	Daniel Convissor	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.41-community/5.1	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
MySQL mistakenly produces warning when rolling back transaction on an UPDATE query that has both a JOIN and an error in the WHERE clause.

How to repeat:
-- MySQL mistakenly produces warning when rolling
-- back transaction on an UPDATE query that has both
-- a JOIN and an error in the WHERE clause.
--
-- Warning:
-- Some non-transactional changed tables couldn't be rolled back

CREATE DATABASE db_a;
USE db_a;

CREATE TABLE t_a (
  c_1 int,
  c_2 int
) ENGINE=InnoDB;

CREATE TABLE t_b (
  c_1 int,
  c_2 int
) ENGINE=InnoDB;

INSERT INTO t_a VALUES (1, 1);
INSERT INTO t_b VALUES (1, 1);

-- GOOD
BEGIN;
UPDATE t_a SET c_2 = 2 WHERE c_3 = 1;
-- ERROR 1054 (42S22): Unknown column 'c_3' in 'where clause'
ROLLBACK;
-- Query OK, 0 rows affected (0.00 sec)

-- GOOD
BEGIN;
UPDATE t_a JOIN t_b USING (c_1) SET t_a.c_3 = 2;
-- ERROR 1054 (42S22): Unknown column 't_a.c_3' in 'field list'
ROLLBACK;
-- Query OK, 0 rows affected (0.00 sec)

-- BAD
BEGIN;
UPDATE t_a JOIN t_b USING (c_1) SET t_a.c_2 = 2 WHERE t_a.c_3 = 1;
-- ERROR 1054 (42S22): Unknown column 't_a.c_3' in 'where clause'
ROLLBACK;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS\G
--   Level: Warning
--    Code: 1196
-- Message: Some non-transactional changed tables couldn't be rolled back

SELECT VERSION();
-- Tested on:
-- 4.1.22-log
-- 5.0.41-community

DROP DATABASE db_a;
DROP DATABASE db_b;

Thank you for the bug report. Verified as described.

This is probably a MySQL bug, because InnoDB does not track non-transactional tables.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30972

ChangeSet@1.2530, 2007-07-16 19:08:21+03:00, gkodinov@magare.gmz +3 -0
  Bug #29769: mistaken warning rolling back UPDATE with JOIN 
  and error in WHERE clause
  
  The trans_safe flag was initialized inconsistently with 
  transactional_tables flag. Thus caused 
  multi_update::~multi_update to erroneously set the flag
  that there are non-transactional table updated and as a 
  result the user is getting a wrong warning.
  Fixed by setting the trans_safe flag to the correct 
  value (off)

This bug is fixed by the fix for bug #27417.