MySQL Bugs: #55172: Innodb errors as 1104: The SELECT would examine more than MAX_JOIN

Bug #55172	Innodb errors as 1104: The SELECT would examine more than MAX_JOIN_SIZE rows...
Submitted:	12 Jul 2010 7:37	Modified:	19 Dec 2010 12:33
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Tests: Server	Severity:	S3 (Non-critical)
Version:	5.1.48, 5.5.5-m3	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Test fails as below when ran with Innodb. With Myisam test gives no errors.

mysqltest: At line 1427: query 'CALL initial_cur2()' failed: 1104: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay 

How to repeat:
Run the attached .test files one by one
(Need to set before running
export MASTER_HOST=localhost
export CLIENT_HOST=localhost)

1st file

Attachment: storedproc-initial-5-1-major.test (application/octet-stream, text), 23.20 KiB.

2nd file

Attachment: storedproc-upgrade-5-1-major.test (application/octet-stream, text), 37.35 KiB.

Dump before failure

Attachment: dump (application/octet-stream, text), 3.07 KiB.

- Doesn't fail with Myisam. Fails with Innodb. 
- Doesn't fail with root user. Fails with the error mentioned, when calling the SP connected as user having EXECUTE priv. on the SP.

Privs. assigned to user connected are:
-------------------------------
Grants for sp_execute@localhost 
GRANT USAGE ON *.* TO 'sp_execute'@'localhost' IDENTIFIED BY PASSWORD '*E2DF02C78F50CE764A3966740A70B6993125EA9F' 
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `procdb`.* TO 'sp_execute'@'localhost' 
GRANT EXECUTE ON PROCEDURE `sp_db3`.`dummy` TO 'sp_execute'@'localhost'

This happens as test is setting 
SELECT @@SQL_MAX_JOIN_SIZE;

@@SQL_MAX_JOIN_SIZE

100

somewhere earlier in the test, for sp_execute user. 

On setting SET @@max_join_size = DEFAULT, it sets it back as
SELECT @@SQL_BIG_SELECTS;

@@SQL_BIG_SELECTS

1

SELECT @@SQL_MAX_JOIN_SIZE;

@@SQL_MAX_JOIN_SIZE

18446744073709551615

and the problem doesn't occur. Test passes fine. 

So the error is thrown when SQL_MAX_JOIN_SIZE=100 and SQL_BIG_SELECTS=0.

Though I am not able to understand and also reproduce the steps manually (test is too big), to conclude why the error should be thrown on the call to the stored procedure.
Can anyone have a look at the 'dump' before failure(attached) and see why it happens on CALL initial_cur2();

The procedure is created as below:

DELIMITER |
CREATE PROCEDURE initial_cur2() SQL SECURITY INVOKER 
BEGIN 
  DECLARE done INT DEFAULT 0; 
  DECLARE c1 CURSOR FOR SELECT id,data FROM t1 ORDER BY id; 
  DECLARE c2 CURSOR FOR SELECT i FROM t2 ORDER BY i; 
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

  OPEN c1; 
  OPEN c2; 
  REPEAT 
  BEGIN 
    DECLARE a CHAR(16); 
    DECLARE b,c INT; 

    FETCH FROM c1 INTO a, b; 
    FETCH NEXT FROM c2 INTO c; 
    IF NOT done THEN 
      IF b < c THEN 
        INSERT INTO t3 VALUES (a, b); 
      ELSE 
        INSERT INTO t3 VALUES (a, c); 
      END IF; 
    END IF; 
  END; 
  UNTIL done END REPEAT; 
  CLOSE c1; 
  CLOSE c2; 
END| 
DELIMITER ;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".