Adam Majer wrote:

Well, first of all, you'd get a simpler and (I think) faster query by changing all those LEFT JOIN's to INNER JOINs and dropping every AND clause in your query. LEFT JOIN will match every row in the left-hand table to a minimum of one row in the right-hand table, even if there's no match, while an INNER JOIN will only produce a row if both values are equal (which seems to be what you want).

In this particular case, that may solve your problem. My guess is that your JOINing is filling more than 16 million rows, which is the max for this setting. It doesn't matter how many rows are returned, MAX_JOIN_SIZE looks at how many rows are *looked at*.

In the *general* case, though, MySQLCC does indeed have a bug: It's impossible for infinite-length queries. So I wrote a patch. It's poorly tested, but I *think* it works, and I've joined a SELECT 1 FROM table a LEFT JOIN table b ON 1=1 LEFT JOIN table c ON 1=1 LEFT JOIN... until I used up all my RAM and swap space and it's still going on as I'm typing this email. My memory and swap file usage graphs are triangular :). Oh, poof, there it goes, Killed (Linux >> Windows).

Using `bk -r diffs -cu` (I'm no bk God, but I think that's what I want), I get the following patch against bk trunk (also included as attachment). To use the new feature, set "Max Join Size" to 0 (will show up as 'Unlimited') and "Select Limit" to 0 ('Unlimited'). I'd even recommend them as defaults.... (Jorge: You getting this? :))

If you can't compile from source, well, erm... I can't help you :).

----------------------------------------------------------------------------- ===== shared/src/CMySQL.cpp 1.8 vs edited ===== --- 1.8/shared/src/CMySQL.cpp Wed Apr 30 22:12:42 2003 +++ edited/shared/src/CMySQL.cpp Thu Jul 24 20:43:45 2003 @@ -165,11 +165,18 @@

if (mysql_opt_load_local_infile) mysql_options(mysql, MYSQL_OPT_LOCAL_INFILE, mysql_opt_load_local_infile ? 0 : (char*) &mysql_opt_load_local_infile); - - if (mysql_opt_select_limit > 0) - sprintf(init_command, "SET SQL_SELECT_LIMIT=%lu,SQL_MAX_JOIN_SIZE=%lu", mysql_opt_select_limit, mysql_opt_max_join_size); - else - sprintf(init_command, "SET SQL_MAX_JOIN_SIZE=%lu", mysql_opt_max_join_size); + + if (mysql_opt_select_limit > 0) { + if (mysql_opt_max_join_size > 0) + sprintf(init_command, "SET SQL_SELECT_LIMIT=%lu,SQL_MAX_JOIN_SIZE=%lu", mysql_opt_select_limit, mysql_opt_max_join_size); + else + sprintf(init_command, "SET SQL_SELECT_LIMIT=%lu", mysql_opt_select_limit); + } else { + if (mysql_opt_max_join_size > 0) + sprintf(init_command, "SET SQL_MAX_JOIN_SIZE=%lu", mysql_opt_max_join_size); + else + sprintf(init_command, "SET SQL_BIG_SELECTS=1"); + }

mysql_options(mysql, MYSQL_INIT_COMMAND, init_command);

===== src/CRegisterServerDialog.cpp 1.30 vs edited ===== --- 1.30/src/CRegisterServerDialog.cpp Wed Apr 30 23:30:09 2003 +++ edited/src/CRegisterServerDialog.cpp Thu Jul 24 21:00:59 2003 @@ -76,7 +76,8 @@

maxJoinSize = new QSpinBox(this, "maxJoinSize"); maxJoinSize->setMaxValue(0xFFFFFF); - maxJoinSize->setMinValue(1); + maxJoinSize->setMinValue(0); + maxJoinSize->setSpecialValueText(tr("Unlimited")); maxJoinSize->setValue(1000000);

CMySQLOptionsTabLayout->addMultiCellWidget(maxJoinSize, 4, 4, 1, 2);

-----------------------------------------------------------------------------