sql_big_selects and max_join_size

I am getting "ERROR 1104 (42000): 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" on the following query.

mysql> SELECT ord.*, pt.authorize_transaction_id FROM store_orders as
ord LEFT OUTER JOIN store_payments as pt ON ord.id = pt.order_id WHERE
(ord.user_id like '2' and ord.order_reference like '%' and ord.status_id
like '%' and (pt.authorize_transaction_id like '%' ||
pt.authorize_transaction_id is NULL)) ORDER BY created_at DESC;

sql_big_sqlect is 'OFF' but max_join_size is set to 524288000. Both
the tables have 748 rows each. So the query should scan less than a
million rows (748 * 748), in which case sql_big_selects should have no
effect on the query since max_join_size is set way higher than one
million.

Is it a BUG or am I missing something here? I am using version
5.1.30-ndb-6.3.20-cluster-gpl-log (64 bit machine).

Presently I have resolved it by setting max_join_size to its default
value 18446744073709551615, which turns 'ON' sql_big_selects for each
session. (http://dev.mysql.com/doc/refman/5.1/en/server-session-varia bles.html#sysvar_sql_big_selects)

--------------------------
I have attached more details below:

OUTPUT from explain command:
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- --------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- --------+
| 1 | SIMPLE | ord | ALL | NULL | NULL | NULL |
NULL | 748 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | pt | ALL | NULL | NULL | NULL |
NULL | 748 | Using where |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- --------+
2 rows in set (0.02 sec)

mysql> show variables like "sql_big_select%";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| sql_big_selects | OFF |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> select count(*) from store_orders;
+----------+
| count(*) |
+----------+
| 748 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from store_payments;
+----------+
| count(*) |
+----------+
| 748 |
+----------+
1 row in set (0.01 sec)

mysql> show variables like 'max_join_size';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| max_join_size | 524288000 |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_max_join_size';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| sql_max_join_size | 524288000 |
+-------------------+-----------+
1 row in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org