Bug #64634 No result when binding to same field with different trailing spaces

• Submitted: 13 Mar 2012 13:11
• Modified: 1 Apr 2012 10:56
• Reporter: Erik Remmelzwaal
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.4-m7
• OS: Any
• CPU Architecture: Any
• Tags: binding, string, trailing spaces, utf8

[13 Mar 2012 13:11] Erik Remmelzwaal

Description:
Trailing spaces are suppose not to have any influence the '=' in the where clause.
So when I've a query that returns a row like:
  SELECT f FROM t WHERE f = 'a';
Its result is the same when I write:
  SELECT f FROM t WHERE f = 'a ';
and even when I write:
  SELECT f FROM t WHERE f = 'a ' AND f = 'a';

But when I use the C-api and binding and the column definition of t.f is utf8 / utf8_bin (but not latin1 / latin1_general_cs) the query:
  SELECT f FROM t WHERE f = ? AND f = ?;
with bind values 'a' and 'a ' does not return a result.

One can argue that this kind of constructs does not make sense, but this kind of constructs happen in generated SQL from complex 4GL software.

Versions:
I found this bug on various versions later than 5.0.x.

How to repeat:
I attached a CPP script that needs to be compiled (see at top of script). It is full containing. You need to change the connection settings also at the top of the script.

At line 445 / 446 you can switch the column definition from utf8 to latin1. This script will then run fine.

The output looks like:
$ ./select_bind_utf8                  
SELECT a0.t_lastname FROM tdbtst120000 a0  WHERE a0.t_lastname = ?
        Binding 'HAAS'
        Fetched: HAAS
SELECT a0.t_lastname FROM tdbtst120000 a0  WHERE a0.t_lastname = ?
        Binding 'HAAS '
        Fetched: HAAS
SELECT a0.t_lastname FROM tdbtst120000 a0  WHERE a0.t_lastname = ? AND a0.t_lastname = ?
        Binding 'HAAS ' and 'HAAS'
!!! Error at line 390: 
  Expected value:0
  Returned value:100
   mysql error: 
select_bind_utf8: select_bind_utf8.cpp:85: void m_expect_equals(...
Abort(coredump)