LP:876306 - Assertion `count <= lock->read_no_write_count && count2 <= 100' failed in check_locks with numerous subqueries

When using an excessive number of subqueries, the server asserted as follows. If there is a 100-table limit, it must result in a client error message and not in an assertion. Furthermore, the constant 100 should not be used in the code as a literal – it has to have a proper name.

mysqld: thr_lock.c:255: check_locks: Assertion `count <= lock->read_no_write_count && count2 <= 100' failed.

1. 2011-10-14T19:23:51 #3 <signal handler called>
2. 2011-10-14T19:23:51 #4 0x00000035a6e328f5 in raise () from /lib64/libc.so.6
3. 2011-10-14T19:23:51 #5 0x00000035a6e340d5 in abort () from /lib64/libc.so.6
4. 2011-10-14T19:23:51 #6 0x00000035a6e2b8b5 in __assert_fail () from /lib64/libc.so.6
5. 2011-10-14T19:23:51 #7 0x0000000000c4590b in check_locks (lock=0x7f4f40155048,
6. 2011-10-14T19:23:51 where=0xe94ff6 "read lock with no write locks", type=TL_READ,
7. 2011-10-14T19:23:51 allow_no_locks=0 '\000') at thr_lock.c:254
8. 2011-10-14T19:23:51 #8 0x0000000000c46af5 in thr_lock (data=0x7f4f4011b620, owner=0x4a1e150,
9. 2011-10-14T19:23:51 lock_type=TL_READ) at thr_lock.c:665
10. 2011-10-14T19:23:51 #9 0x0000000000c47c71 in thr_multi_lock (data=0x7f4f40017000, count=104,
11. 2011-10-14T19:23:51 owner=0x4a1e150) at thr_lock.c:1088
12. 2011-10-14T19:23:51 #10 0x00000000006a2002 in mysql_lock_tables (thd=0x4a1cca8,
13. 2011-10-14T19:23:51 sql_lock=0x7f4f40016ca8, write_lock_used=false, flags=4,
14. 2011-10-14T19:23:51 need_reopen=0x7f4fb03aa7e3) at lock.cc:286
15. 2011-10-14T19:23:51 #11 0x00000000006a1daa in mysql_lock_tables (thd=0x4a1cca8,
16. 2011-10-14T19:23:51 tables=0x7f4f40265348, count=104, flags=4, need_reopen=0x7f4fb03aa7e3)
17. 2011-10-14T19:23:51 at lock.cc:209
18. 2011-10-14T19:23:51 #12 0x000000000071ef6d in lock_tables (thd=0x4a1cca8, tables=0x7f4f400d5e70,
19. 2011-10-14T19:23:51 count=104, need_reopen=0x7f4fb03aa7e3) at sql_base.cc:5472
20. 2011-10-14T19:23:51 #13 0x000000000071e364 in open_and_lock_tables_derived (thd=0x4a1cca8,
21. 2011-10-14T19:23:51 tables=0x7f4f400d5e70, derived=true) at sql_base.cc:5120
22. 2011-10-14T19:23:51 #14 0x00000000006cfc9a in open_and_lock_tables (thd=0x4a1cca8,
23. 2011-10-14T19:23:51 tables=0x7f4f400d5e70) at mysql_priv.h:1749
24. 2011-10-14T19:23:51 #15 0x00000000006c7ec2 in execute_sqlcom_select (thd=0x4a1cca8,
25. 2011-10-14T19:23:51 all_tables=0x7f4f400d5e70) at sql_parse.cc:5078
26. 2011-10-14T19:23:51 #16 0x00000000006beebe in mysql_execute_command (thd=0x4a1cca8)
27. 2011-10-14T19:23:51 at sql_parse.cc:2250
28. 2011-10-14T19:23:51 #17 0x00000000006cac8f in mysql_parse (thd=0x4a1cca8,

test case:

--disable_abort_on_error
SET SESSION 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=off,derived_merge=on,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,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=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on';
SET SESSION join_cache_level = 1;
SET SESSION join_buffer_size = 131072;
SET SESSION debug = '';
--enable_abort_on_error

1. Begin test case for query 0

--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
--enable_warnings

CREATE TABLE t1 ( col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333))) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('tzvyvdkmdh','she');
INSERT INTO t1 VALUES ('fltzvyvdkm','hfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdx');
INSERT INTO t1 VALUES ('LWWVD','w');
INSERT INTO t1 VALUES ('w','mhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuhrqtxgyvluerppwdumq');
INSERT INTO t1 VALUES ('can\'t','just');
INSERT INTO t1 VALUES ('plkpmhfltz','hplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfx');
INSERT INTO t1 VALUES ('jhplkpmhfl','like');
INSERT INTO t1 VALUES ('m','LSPKM');
INSERT INTO t1 VALUES ('LAYAK','cvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuhrqtxgyvluerppwdumqrktxjwbzwwhhbiuqwuntarpigplemosmhqrbpgqvnmimrggpoktamszbswmyxdgtaqrcthasqjwveuzgfxkjthlklhuhvflffnnteztrrnyzyxqoxasxxmhzqzeyrrrpizfyjbaexravemn');
INSERT INTO t1 VALUES ('GGSSM','there');
INSERT INTO t1 VALUES ('j','txrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiq');
INSERT INTO t1 VALUES ('z','HLRYV');
INSERT INTO t1 VALUES ('y','mvxdvtxrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuh');
INSERT INTO t1 VALUES ('d','w');
INSERT INTO t1 VALUES ('VDBGR','IGRLP');
INSERT INTO t1 VALUES ('IZZWX','pwlqomvxdvtxrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytak');
INSERT INTO t1 VALUES ('just','HIHXI');
INSERT INTO t1 VALUES ('something','l');
INSERT INTO t1 VALUES ('ljadnpwlqo','b');
INSERT INTO t1 VALUES ('n','IJMQT');

SELECT alias2
FROM t1 AS alias1
LEFT OUTER JOIN view_F AS alias2 ON alias1.col_varchar_1024_utf8_key = alias2.col_varchar_10_utf8
WHERE
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
AND alias2 BETWEEN
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
OR 4 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
OR
( 65 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 30 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 25 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 100 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
,
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
)
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
AND alias1 BETWEEN
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 10 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 65 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 65 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 14 )
OR alias1 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
+
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 );

DROP TABLE t1;