Re: 100% CPU loop in find_field_in_tables
This bug is also present in maria-5.1 and mysql-next-mr, so appears to be a legacy issue around the second execution of a prepared statement:

Test case:

CREATE TABLE `AA` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_int` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
INSERT INTO `AA` VALUES (1,6,2);
INSERT INTO `AA` VALUES (2,9,0);
INSERT INTO `AA` VALUES (3,-1240072192,2);
INSERT INTO `AA` VALUES (4,-221380608,-390332416);
INSERT INTO `AA` VALUES (5,255328256,9);
INSERT INTO `AA` VALUES (6,1167392768,-1272053760);
INSERT INTO `AA` VALUES (7,3,-125960192);
INSERT INTO `AA` VALUES (8,-950403072,9);
INSERT INTO `AA` VALUES (9,2,-1546649600);
INSERT INTO `AA` VALUES (10,23789568,8);
INSERT INTO `AA` VALUES (11,8,3);
INSERT INTO `AA` VALUES (12,-1638400000,5);
INSERT INTO `AA` VALUES (13,-245235712,1);
INSERT INTO `AA` VALUES (14,5,1200160768);
INSERT INTO `AA` VALUES (15,4,-1131675648);
INSERT INTO `AA` VALUES (16,3,6);
INSERT INTO `AA` VALUES (17,6,-1137836032);
INSERT INTO `AA` VALUES (18,1114701824,4);
INSERT INTO `AA` VALUES (19,27721728,-1256194048);
INSERT INTO `AA` VALUES (20,-644415488,1646657536);
INSERT INTO `AA` VALUES (21,-1060896768,8);
INSERT INTO `AA` VALUES (22,-1074724864,1298071552);
INSERT INTO `AA` VALUES (23,-1069547520,3);
INSERT INTO `AA` VALUES (24,3,569573376);
CREATE TABLE `D` (
`col_int` int(11) DEFAULT NULL,
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (6,1,-506134528);
CREATE TABLE `I` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
INSERT INTO `I` VALUES (1,2037514240,-971046912);
INSERT INTO `I` VALUES (2,819331072,7);
INSERT INTO `I` VALUES (3,-2099249152,-1953824768);
INSERT INTO `I` VALUES (4,-400621568,8);
INSERT INTO `I` VALUES (5,-1273102336,-1452474368);
INSERT INTO `I` VALUES (6,7,-111607808);

PREPARE st1 FROM 'SELECT table1 . `pk` AS field1 , table1 .`pk` AS field2 , table2 . `col_int` AS field3 , MIN( table1 . `pk` ) AS field4
FROM AA AS table1 LEFT JOIN D AS table2 ON table1 . `pk` = table2 . `col_int_key` RIGHT JOIN I AS table3 ON table1 . `col_int_key` = table3 . `col_int_key`
WHERE ( table2 . `col_int` IS NULL OR table3 . `pk` IS NULL )
GROUP BY field1, field2, field3
ORDER BY field1, field2, field3, field4
LIMIT 2';

EXECUTE st1;
EXECUTE st1;

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
Asssigned to Sanja since it involves GROUP BY / ORDER BY

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
I can't repeat this bug any more.

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
EXECUTE st1;
field1 field2 field3 field4
NULL NULL NULL NULL
11 11 NULL 11
EXECUTE st1;
field1 field2 field3 field4
NULL NULL NULL NULL
11 11 NULL 11
EXECUTE st1;
field1 field2 field3 field4
NULL NULL NULL NULL
11 11 NULL 11
drop table AA,D,I;

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
A new, unsimplified test case.

A new, unsimplified test case.
bug627370.test
LPexportBug627370_bug627370.test

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
The problem is that on the first execution simplify_joins() in the try to flatten joins makes looped list of tables via next_name_resolution_table: table 'D' with alias 'alias5' refers table 'D' with alias1 (the very beginning of the list).

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
I am not sure that it is the same bug, but trying to simplify above problem I got this test case with crash:

create table t1 (a int, b int);
insert into t1 values (1,1),(2,2);
create table t2 (a int, b int);
insert into t2 values (1,1),(2,2);

select t1.b, t2.b from t1 right join t2 on (t2.a=t1.a) where t1.a > 5;

drop table t1, t2;

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
above reported as https://bugs.launchpad.net/maria/+bug/965950

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
It is the query for better understanding it (the query is the same):

prepare st1 from 'SELECT alias1 . `pk` AS field1
FROM
D AS alias1 LEFT OUTER JOIN
D AS alias2 ON alias1 . `col_int_key` = alias2 . `pk` RIGHT OUTER JOIN
EE AS alias3 LEFT JOIN
G AS alias4 ON alias3 . `col_varchar_10_latin1_key` = alias4 . `col_varchar_10_utf8` LEFT JOIN
D AS alias5 ON alias3 . `col_varchar_10_utf8_key` = alias5 . `col_varchar_10_latin1` ON alias2 . `col_int_key` = alias3 . `pk` LEFT JOIN
H AS alias6 ON alias5 . `col_int` = alias6 . `col_int_key` LEFT JOIN
BB AS alias7 RIGHT JOIN
CC AS alias8 ON alias7 . `col_int_key` = alias8 . `col_int_key` LEFT JOIN
GG AS alias9 ON alias7 . `col_varchar_1024_latin1` = alias9 . `col_varchar_1024_utf8_key` ON alias4 . `col_varchar_1024_latin1` = alias8 . `col_varchar_1024_utf8`
WHERE alias9 . `col_int_key` >= alias7 . `pk`
GROUP BY field1
HAVING field1 > 6
ORDER BY field1
LIMIT 2 OFFSET 3';

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
To catch it in debugger set breakpoint on mysql_sql_stmt_execute then mysql_execute_command and then watchpoint on changing next_name_resolution_table of table with alias5, it will be place where the list looped.

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
Checked all test cases above with

• mariadb 5.1, 5.2, 5.5
• mysql 5.5
  and couldn't reproduce the bug.

Re: 100% CPU loop in find_field_in_tables on 2nd execution of a prepared statement
The bug has been fixed by the following patch:

revno: 2661.806.1
committer: Martin Hansson <martin.hansson@oracle.com>
branch nick: mysql-5.1-security
timestamp: Tue 2012-02-07 14:16:09 +0100
message:
Bug #11765810 58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY
IS EXECUTED TWICE FROM P

This bug is a duplicate of bug 12567331, which was pushed to the
optimizer backporting tree on 2011-06-11. This is just a back-port of
the fix. Both test cases are included as they differ somewhat.

Launchpad bug id: 627370