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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.4-m7 OS:Any
Assigned to: CPU Architecture:Any
Tags: binding, string, trailing spaces, utf8
Triage: Needs Triage: D3 (Medium)

[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)