MySQL Bugs: #31524: One table one row select on NDB cause optmizer to require SQL_BIG

Bug #31524	One table one row select on NDB cause optmizer to require SQL_BIG_SELECTS=1
Submitted:	11 Oct 2007 9:38	Modified:	10 Apr 2008 14:11
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (64 Bit)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Hi,

In adding some test cases around NDB, I ran across the following:

--eval CREATE TABLE t1 (c1 VARBINARY(255), c2 VARBINARY(255))ENGINE=$engine_type;

SET CHARACTER_SET_CLIENT=koi8r,
    CHARACTER_SET_CONNECTION=cp1251,
    CHARACTER_SET_RESULTS=koi8r;

INSERT INTO t1 (c1, c2) VALUES ('ArA, AsA? A'ATA,A?AOA<A','ArA, AsA? A'ATA,A?AOA<A');

select hex(c1), hex(c2) from t1;

Without having  SET SQL_BIG_SELECTS=1; before the above select you will receive:

*******************

query 'select hex(c1), hex(c2) from t1' 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

*******************

But reading the docs, the SQL_BIG_SELECTS section reads:

SQL_BIG_SELECTS = {0 | 1}

If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which allows all SELECT statements.

If you set the max_join_size system variable to a value other than DEFAULT, SQL_BIG_SELECTS is set to 0.

There are no joins and we are only returning one row seeing as there is only one to return. So this leads me to believe that we have an optimizer bug going against NDB Cluster engine.
 

How to repeat:
See the above

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41259

ChangeSet@1.2658, 2008-01-25 17:56:44+02:00, gkodinov@magare.gmz +4 -0
  Bug #31524: One table one row select on NDB cause optimizer to 
    require SQL_BIG_SELECTS=1
  
  There was a previous test that leaves a non-default (small) max_join_size.
  Fixed by re-setting the max_join_size to default when the test is over.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Test failure only; no user-visible changes to document. Closed.