Comment 1 for bug 910083

Minimal optimizer_switch: materialization=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

# The test case involves a concurrency element,
# so it's not 100% deterministic.
# It fails regularly on current maria-5.3 debug build for me,
# but if you are not getting the failure,
# try to increase the number of rows in the tables, it seems to help.
# With more knowledge about possible problem
# it might be possible to create a deterministic test case with sync points,
# and maybe even get rid of the trigger.

# Test case:

SET optimizer_switch = 'materialization=on';

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );

CREATE TRIGGER tr AFTER UPDATE ON t1 FOR EACH ROW
  UPDATE t2 SET b = ( SELECT COUNT(a) FROM t1 );

INSERT INTO t1
  VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO t2
  VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0);

send
  UPDATE t1 SET a = 3;

connect(con1,localhost,root,,);
  SELECT COUNT(*) FROM t1;
disconnect con1;

connection default;
reap;
UPDATE t1 SET a = 2;

# Cleanup
DROP TABLE t1, t2;

# End of test case