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

[26 Sep 2005 15:00] Berto van de Kraats
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)
[26 Sep 2005 15:02] Berto van de Kraats
Cpp trace of problem

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

[26 Sep 2005 20:10] Hartmut Holzgraefe
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
[27 Sep 2005 7:41] Berto van de Kraats
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.
[29 Sep 2005 16:54] MySQL Verification Team
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
[11 Oct 2005 21:37] Bugs System
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
[12 Oct 2005 11:27] Berto van de Kraats
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.
[12 Oct 2005 11:49] Berto van de Kraats
Trace of 2nd problam

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

[12 Oct 2005 20:40] Bugs System
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
[13 Oct 2005 8:17] Berto van de Kraats
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?
[9 Nov 2005 16:12] Bugs System
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
[10 Nov 2005 13:38] Konstantin Osipov
Reviewed over IRC.
[10 Nov 2005 13:39] Konstantin Osipov
Fixed in 5.0 tree currently tagged 5.0.17
[23 Nov 2005 13:16] Jon Stephens
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.