Bug #32650 SELECT with OUTER JOIN from I_S: "Out of memory" for "memory" and "MyISAM"
Submitted: 23 Nov 2007 10:37 Modified: 7 May 2008 6:59
Reporter: Joerg Bruehe Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Tests: Server Severity:S3 (Non-critical)
Version:5.0.44, 5.1.19, 6.0.4 and up OS:Other (AIX, i5os, Sol8/x86)
Assigned to: Sergei Glukhov CPU Architecture:Any

[23 Nov 2007 10:37] Joerg Bruehe
Description:
This test failure is extracted from a build of the "community" sources 5.0.51,
but I find this symptom in AIX build logs starting from 5.0.44 and 5.1.19,
just did not extract it.

The "*__datadict" tests
(5.0: innodb__datadict, memory__datadict, myisam__datadict)
contain this SELECT with OUTER JOIN from INFORMATION_SCHEMA:

select information_schema.tables.table_name as "table name",
count(distinct(column_name)) as "no of columns in the table"
  from information_schema.tables left outer join information_schema.columns on
information_schema.tables.table_name = information_schema.columns.table_name
group by information_schema.tables.table_name;

For table handlers "memory" and "myisam" (but *not* for "innodb"),
this statement fails on some platforms with this message:

--- 2824,2830
    from information_schema.tables left outer join information_schema.columns on
  information_schema.tables.table_name = information_schema.columns.table_name
  group by information_schema.tables.table_name;
! ERROR HY000: Out of memory (Needed 268435377 bytes)

  root: simple select to check all - and never forget some - tables
  -----------------------------------------------------------------

In 5.0, there is no test "ndb__datadict";
in 5.1, it did not show this symptom AFAICT.

Affected hosts:
aix52
intelxeon3    (5.0 only)
i5os-02-32bit

How to repeat:
Run the *__datadict tests.

Suggested fix:
Check whether it is realistic that this statement might need 256 MB,
this might be some (initial ?) value which is not computed correctly.

Also, i5os and aix52 use the same compiler, and they share this problem in both 5.0 and 5.1.
intelxeon3 does not show it in 5.1, so there might be a different cause on this host.

These tests are far too large:

1) It is difficult to maintain them, following code changes.

2) It is difficult to check their results.
   In that 5.0.51 build, up to 5 different bugs show up in one such test run.
I spent more than a whole day splitting the log output into different bugs and ensuring they all are reported.
(Had I not invested that time, I could not have isolated this bug.)

Split these tests into manageable parts !
[3 Jan 2008 10:57] Joerg Bruehe
Reproduced in the build of 6.0.4-alpha
for both AIX and i5os (32 bit build, each),
Solaris 8 is not built with 6.0 any more.

Build trees are still available on the build hosts,
all build info (including configuration) is logged.
[28 Apr 2008 7:23] Sergei Glukhov
checked latest 5.0, 5.1, 6.0 trees on aix52
There is no _datadict_ tests on 5.0&5.1. Other test work fine.
datadict test on 6.0 is broken, but I checked the query from the bug report.
It works fine too, without OOM.

Community trees are very old,
so I think there's little point in testing of these trees.