"Can't write, because of unique constraint" with subquery cache

[Philip Stoev]

The following query:

SELECT table1 .`col_varchar_nokey`
FROM C table1 RIGHT JOIN CC LEFT JOIN (
SELECT SUBQUERY1_t2 .*
FROM CC SUBQUERY1_t1 LEFT JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( (
SELECT *
FROM CC ) table4 JOIN ( CC table5 JOIN C table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key`
WHERE table3 .`col_varchar_key` IN (
SELECT `col_varchar_key`
FROM view_C ) AND table1 .`col_varchar_key` OR table1 .`pk` ;

causes the following error when run with subquery cache enabled:

1169 Can't write, because of unique constraint, to table '#sql_7c8c_2'

backtrace:

#0 my_error (nr=1169, MyFlags=0) at my_error.c:81
#1 0x083c8856 in handler::print_error (this=0xb67c97b0, error=141, errflag=0) at handler.cc:2801
#2 0x0831bdcb in create_internal_tmp_table_from_heap2 (thd=0x94454f8, table=0xb671d238, start_recinfo=0xb671dc48, recinfo=0xb671d134, error=135,
    ignore_last_dupp_key_error=true, hton=0x93081f8, proc_info=0x889103e "converting HEAP to Maria") at sql_select.cc:12426
#3 0x0831b7d9 in create_internal_tmp_table_from_heap (thd=0x94454f8, table=0xb671d238, start_recinfo=0xb671dc48, recinfo=0xb671d134, error=135,
    ignore_last_dupp_key_error=true) at sql_select.cc:12145
#4 0x0849572d in Expression_cache_tmptable::put_value (this=0xb671d0f0, value=0xb671d060) at sql_expression_cache.cc:299
#5 0x081d2f6e in Item_cache_wrapper::cache (this=0xb671cfe8) at item.cc:6726
#6 0x081c8d95 in Item_cache_wrapper::val_bool (this=0xb671cfe8) at item.cc:6889
#7 0x082028d9 in Item_cond_and::val_int (this=0xb671b740) at item_cmpfunc.cc:4548
#8 0x081b8e4e in Item::val_bool (this=0xb671b740) at item.cc:187
#9 0x082029bf in Item_cond_or::val_int (this=0xb671b690) at item_cmpfunc.cc:4566
#10 0x082d98b6 in SQL_SELECT::skip_record (this=0xb671b8a8, thd=0x94454f8) at opt_range.h:767
#11 0x082da162 in JOIN_CACHE::check_match (this=0xb671c8e8, rec_ptr=0xb6782570 <incomplete sequence \360>) at sql_join_cache.cc:1984
#12 0x082d786f in JOIN_CACHE::generate_full_extensions (this=0xb671c8e8, rec_ptr=0xb6782570 <incomplete sequence \360>) at sql_join_cache.cc:1933
#13 0x082d76ef in JOIN_CACHE_BNL::join_matching_records (this=0xb671c8e8, skip_last=false) at sql_join_cache.cc:1833
#14 0x082d71be in JOIN_CACHE::join_records (this=0xb671c8e8, skip_last=false) at sql_join_cache.cc:1636
#15 0x0831cf72 in sub_select_cache (join=0xb6737850, join_tab=0xb671a810, end_of_records=true) at sql_select.cc:12877
#16 0x0831d18b in sub_select (join=0xb6737850, join_tab=0xb671a650, end_of_records=true) at sql_select.cc:13039
#17 0x0831cf9e in sub_select_cache (join=0xb6737850, join_tab=0xb671a650, end_of_records=true) at sql_select.cc:12879
#18 0x0831d18b in sub_select (join=0xb6737850, join_tab=0xb671a490, end_of_records=true) at sql_select.cc:13039
#19 0x0831cf9e in sub_select_cache (join=0xb6737850, join_tab=0xb671a490, end_of_records=true) at sql_select.cc:12879
#20 0x0831d18b in sub_select (join=0xb6737850, join_tab=0xb671a2d0, end_of_records=true) at sql_select.cc:13039
#21 0x0831c745 in do_select (join=0xb6737850, fields=0x9446f0c, table=0x0, procedure=0x0) at sql_select.cc:12635
#22 0x08302ad3 in JOIN::exec (this=0xb6737850) at sql_select.cc:2355
#23 0x08303209 in mysql_select (thd=0x94454f8, rref_pointer_array=0x9446f90, tables=0xb663bdc0, wild_num=0, fields=..., conds=0xb66b9a38, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb66f61e8, unit=0x9446bd4, select_lex=0x9446e74)
    at sql_select.cc:2556
#24 0x082fb7fb in handle_select (thd=0x94454f8, lex=0x9446b78, result=0xb66f61e8, setup_tables_done_option=0) at sql_select.cc:276
#25 0x0829a244 in execute_sqlcom_select (thd=0x94454f8, all_tables=0xb663bdc0) at sql_parse.cc:5081
#26 0x08290c1c in mysql_execute_command (thd=0x94454f8) at sql_parse.cc:2265
#27 0x0829c405 in mysql_parse (thd=0x94454f8,
    inBuf=0xb66acfc8 "SELECT table1 .`col_varchar_nokey`\nFROM C table1 RIGHT JOIN CC LEFT JOIN (\nSELECT SUBQUERY1_t2 .*\nFROM CC SUBQUERY1_t1 LEFT JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`"..., length=588, found_semicolon=0xb6983230) at sql_parse.cc:6027
#28 0x0828e6d2 in dispatch_command (command=COM_QUERY, thd=0x94454f8, packet=0x9447519 "", packet_length=591) at sql_parse.cc:1184
#29 0x0828dbc0 in do_command (thd=0x94454f8) at sql_parse.cc:890
#30 0x0828ad58 in handle_one_connection (arg=0x94454f8) at sql_connect.cc:1153
#31 0x00a08919 in start_thread () from /lib/libpthread.so.0
#32 0x00951e5e in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Test case:

SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on,table_elimination=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MARIA AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `CC` VALUES (10,7,8,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'r','r');
INSERT INTO `CC` VALUES (12,5,9,'a','a');
INSERT INTO `CC` VALUES (13,3,186,'m','m');
INSERT INTO `CC` VALUES (14,6,NULL,'y','y');
INSERT INTO `CC` VALUES (15,92,2,'j','j');
INSERT INTO `CC` VALUES (16,7,3,'d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'z','z');
INSERT INTO `CC` VALUES (18,3,133,'e','e');
INSERT INTO `CC` VALUES (19,5,1,'h','h');
INSERT INTO `CC` VALUES (20,1,8,'b','b');
INSERT INTO `CC` VALUES (21,2,5,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'e','e');
INSERT INTO `CC` VALUES (23,1,8,'j','j');
INSERT INTO `CC` VALUES (24,0,6,'e','e');
INSERT INTO `CC` VALUES (25,210,51,'f','f');
INSERT INTO `CC` VALUES (26,8,4,'v','v');
INSERT INTO `CC` VALUES (27,7,7,'x','x');
INSERT INTO `CC` VALUES (28,5,6,'m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'c','c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `C` VALUES (1,NULL,2,'w','w');
INSERT INTO `C` VALUES (2,7,9,'m','m');
INSERT INTO `C` VALUES (3,9,3,'m','m');
INSERT INTO `C` VALUES (4,7,9,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'r','r');
INSERT INTO `C` VALUES (6,2,9,'t','t');
INSERT INTO `C` VALUES (7,6,3,'j','j');
INSERT INTO `C` VALUES (8,8,8,'u','u');
INSERT INTO `C` VALUES (9,NULL,8,'h','h');
INSERT INTO `C` VALUES (10,5,53,'o','o');
INSERT INTO `C` VALUES (11,NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'k','k');
INSERT INTO `C` VALUES (13,188,166,'e','e');
INSERT INTO `C` VALUES (14,2,3,'n','n');
INSERT INTO `C` VALUES (15,1,0,'t','t');
INSERT INTO `C` VALUES (16,1,1,'c','c');
INSERT INTO `C` VALUES (17,0,9,'m','m');
INSERT INTO `C` VALUES (18,9,5,'y','y');
INSERT INTO `C` VALUES (19,NULL,6,'f','f');
INSERT INTO `C` VALUES (20,4,2,'d','d');

CREATE VIEW view_C AS SELECT * FROM C;

SELECT table1 .`col_varchar_nokey`
FROM C table1 RIGHT JOIN CC LEFT JOIN (
SELECT SUBQUERY1_t2 .*
FROM CC SUBQUERY1_t1 LEFT JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_...