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