MySQL Bugs: #13488: Left outer join query incorrectly gives MYSQL_DATA

Bug #13488	Left outer join query incorrectly gives MYSQL_DATA_TRUNCATED
Submitted:	26 Sep 2005 15:00	Modified:	23 Nov 2005 13:16
Reporter:	Berto van de Kraats	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.14-rc-nightly-20050926	OS:	Linux (Linux Suse SLES 9 SP1)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
A SELECT statement containing a left outer join gives error MYSQL_DATA_TRUNCATED. In the snapshot of September 22 this was still working, but in the snapshots of 0925 and 0926 not anymore.

How to repeat:
Compile attached cpp trace of the problem. If the problem does not reproduce then the program terminates silently with exit status 0. If the problem reproduces then you'll get the following:

!!! Error (340): 
  Expected value:0
  Returned value:101
   mysql error: 
a.out: /usr1/gemini/bse/log/mysql_trace.0.cpp:340: int main(): Assertion `0' failed.
Abort (core dumped)

Cpp trace of problem

Attachment: bug13488.cpp (text/plain), 36.55 KiB.

how do you compile your server and the script?

for me the result is always 

 > ./bug13488 
!!! Error (235): 
  Expected value:0
  Returned value:1
   mysql error: MySQL server has gone away
bug13488: bug13488.cpp:235: int main(): Assertion `0' failed.
Aborted

no matter whether i use latest bk sources, a snapshot from last week or the 5.1.12 release

I used the following commands to build the server:

   ./configure --disable-shared --enable-static
   make clean
   make

The mysql error message says "MySQL server has gone away". If I'm right, this usually means a crash of the mysql server. Not the same error as I got, but still very serious. I've never seen this on a mysql_stmt_prepare call though. 

If I modify the output columns of the SELECT statement to select string columns only, then the result is okay. If I select an integer column from the outer table (tdbtst100000) then the next output column is corrupted.

miguel@hegel:~/dbs/5.0> ./bug13488
!!! Error (340): 
  Expected value:0
  Returned value:101
   mysql error: 
bug13488: /home/miguel/dbs/5.0/bug13488.cpp:340: int main(): Assertion `0' failed.
Aborted

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/internals/30936

There is another problem in the same area. If the query is like:

   select * from t where ((select 1) x left join t on t.c = 10

then data of type FLOAT is corrupted. Use the attached program (bug13488b.cpp) to reproduce. If the problem reproduces then you'll get the following:

!!! Error (319): 
  Expected value:[Int]"10",[Str]"CHRISTINE           ",[Str]"I                   ",[Str]"HAAS                ",[Str]"A00   ",[Str]"15972               ",[Str]"1218                ",[Str]"23548               ",[Dtm]"1965/01/01 00:00:00",[Str]"PRES                ",[Int]"18",[Int]"2",[Dtm]"1933/08/24 00:00:00",[Dbl]"52750",[Flt]"1000",[Int]"0"
  Returned value:[Int]"10",[Str]"CHRISTINE           ",[Str]"I                   ",[Str]"HAAS                ",[Str]"A00   ",[Str]"15972               ",[Str]"1218                ",[Str]"23548               ",[Dtm]"1965/01/01 00:00:00",[Str]"PRES                ",[Int]"18",[Int]"2",[Dtm]"1933/08/24 00:00:00",[Dbl]"52750",[Flt]"0",[Int]"1083129856"
   error: Wrong row returned
a.out: /home/bvdkraat/bug13488b.cpp:319: int main(): Assertion `0' failed.
Abort (core dumped) 

It seems that a fix in the function create_tmp_field_from_item() case REAL_RESULT is required similar to the one for the former bug.

Trace of 2nd problam

Attachment: bug13488b.cpp (text/plain), 31.30 KiB.

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/internals/30982

The patch does not solve the problem in the comment of 12 Oct 13:27. In line with the patch I also changed the code in the REAL_RESULT case (sql_select.cc function create_tmp_field_from_item). I changed the statement

    new_field=new Field_double(item->max_length, maybe_null,
                               item->name, table, item->decimals);

into

    new_field= item->tmp_table_field_from_field_type(table);

which seems to solve the problem. Can you please verify this and deliver a patch?

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/internals/32112

Reviewed over IRC.

Fixed in 5.0 tree currently tagged 5.0.17

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 5.0.17 changelog. Closed.