The same problem exists for derived tables (AKA subquery in the FROM clause):
set @@optimizer_switch='derived_merge=OFF';
SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN (SELECT * FROM t2) v2 ON t1_2.i = j ) ON t1_1.state = v2.state;
i state i state state j
2 Louisiana NULL NULL NULL NULL
9 Maine NULL NULL NULL NULL

The problem connected with varchar length (997 works, 998 and above does not). So it should be something with temporary table limitations.

The limit is field related (adding new fields do not lead to wrong result).

1000 byte index size limit?

No, it uses HASH index which takes 8 bytes.
Whole record size with some pointer reaches exactly 1024 bytes, but it also should not cause problems...

good:
KEYUSE array (2 elements)
KEYUSE: v2.state=`test`.`t1_1`.`state` optimize: 0 used_tables: 1 ref_table_rows: 0 keypart_map: 1
KEYUSE: v2.j=`test`.`t1_2`.`i` optimize: 0 used_tables: 2 ref_table_rows: 0 keypart_map: 1

bad:
KEYUSE array (2 elements)
KEYUSE: v2.j=`test`.`t1_1`.`state` optimize: 0 used_tables: 1 ref_table_rows: 0 keypart_map: 1
KEYUSE: v2.j=`test`.`t1_2`.`i` optimize: 0 used_tables: 2 ref_table_rows: 0 keypart_map: 1

A fix maybe?

--- sql/sql_select.cc   2014-09-12 14:51:41 +0000

+++ sql/sql_select.cc   2014-09-20 10:10:08 +0000

@@ -9268,10 +9268,12 @@

       else

       {

         /* Mark keyuses for this key to be excluded */

-        for (KEYUSE *curr=save_first_keyuse; curr < first_keyuse; curr++)

-       {

+        KEYUSE *curr=save_first_keyuse;

+        do

+        {

           curr->key= MAX_KEY;

-        }

+          curr++;

+        } while (curr < first_keyuse);

       }

       first_keyuse= keyuse;

       key_count++;

It's still possible to get "Internal error: 'Using too big key for internal temp tables'" with the following test case:

CREATE TABLE t1 (i INT, state VARCHAR(995)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (2,'Louisiana'),(9,'Maine');

CREATE TABLE t2 (state VARCHAR(995), j INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('Louisiana',9),('Alaska',5);

INSERT INTO t2 SELECT t2.* FROM t2 JOIN t2 AS t3 JOIN t2 AS t4 JOIN t2 AS t5;

SET @@max_heap_table_size= 16384;

set @@optimizer_switch='derived_merge=OFF';

SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN (SELECT * FROM t2) v2 ON t1_2.i = j ) ON t1_1.state = v2.state;

DROP TABLE t1, t2;

Due to differences in:
1. key length computation: key_length() in check_tmp_table vs pack_length() in create_internal_tmp_table
2. key length comparing:
"key_len <= MI_MAX_KEY_LENGTH" in check_tmp_table
vs
"keyinfo->key_length >= table->file->max_key_length()" in create_internal_tmp_table

Thank you! I'll dig here. (I compared explain and debug output before and it looked the same and I have not noted this debug output difference)

It looks like problem is here. It should exclude allkeys, but exclude none.

The second test suite is a different bug you can report it separately or I will.

Sent for review.

http://lists.askmonty.org/pipermail/commits/2014-October/006694.html

looks like this hasn't been committed yet (not in 5.5 or 10.0 trunk/10.0 branch)

yes

it was fixed between 5.5.40 and 5.5.41
git bisect claims that 6f762cdd6c2805d2b912fa858c36dffdb181b755 fixed it (I do not know how that is possible).

The problem disappeared from 5.5 tree after 5.3=>5.5 merge, particularly this change:

commit b75090c7dede338236b98ace65362348579ffa01

Author: Sergey Petrunya <psergey@askmonty.org>

Date:   Thu Dec 18 20:06:49 2014 +0300

    MDEV-6830: Server crashes in best_access_path after a sequence of SELECTs ...

    generate_derived_keys_for_table() did not work correctly in the case where

    - it had a potential index on derived table

    - however, TABLE::check_tmp_key() would disallow creation of this index

      after looking at its future key parts (because of the key parts exceeding

      max. index length)

    - the code would leave a KEYUSE structure that refers to a non-existant index.

      Depending on further optimizer calculations, this could cause a crash.

Whether it really fixed this bug or just hid it is another question, I don't know that.

sanja, please close this bug if it's really fixed or reassign back to me if there's something to review.

It was fixed by the Petrunia commit.