MySQL Bugs: #79695: True where clause doesn't return expected row, but empty set

Bug #79695	True where clause doesn't return expected row, but empty set
Submitted:	18 Dec 2015 4:25	Modified:	16 Jan 2016 15:55
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.8, 5.6.28, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('11');
M t1Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
Empty set (0.00 sec)

mysql> select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;
+---------------------------------------------------------+
| c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
The following sql should return one row:
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ).

How to repeat:

drop table if exists t1;
create table t1(c1 char(20));
insert into t1 values('11');
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;

Suggested fix:
The following sql returns one row:
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ).

Hello Su Dylan,

Thank you for the report and test case.
I'm not seeing this issue on 5.5.48, 5.6.28, 5.7.10(and also on 5.7.8).

-- 4.1.25, 5.0.96, 5.1.77, 5.5.48, 5.6.28, 5.7.8-rc and 5.7.10

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(c1 char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('11');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
+------+
| c1   |
+------+
| 11   |
+------+
1 row in set (0.00 sec)

mysql> select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;
+---------------------------------------------------------+
| c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Thanks,
Umesh

Hi Umesh,

It seems that it is related to charset 'utf8mb4'.
Please try to recreate again with the following sqls:

set names 'utf8mb4';
drop table if exists t1;
create table t1(c1 char(20));
insert into t1 values('11');
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;

Thank you for the feedback.
Verified with 5.6.28, 5.7.10 builds.

Thanks,
Umesh

// 5.6.28, 5.7.10 ( need to set charset at DB level too in order to reproduce)

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE IF EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE test CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> set names 'utf8mb4';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(c1 char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('11');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
Empty set (0.00 sec)

mysql> select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;
+---------------------------------------------------------+
| c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

*Whitespace* matters here for soem weird reason:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(20));
INSERT INTO t1 VALUES('11');
SELECT c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) FROM t1;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- empty set
(order of the last 2 staements doesn't mattter)

Tested with SQLyog (GUI client)

MySQL 5.5 not affected

MySQL 5.6(.28) not affected either.

Hi Peter,
I just tried removing the whitespace, and it still returns empty set:

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
Empty set (0.00 sec)

mysql> select c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) from t1;
+---------------------------------------------------------+
| c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
Empty set (0.00 sec)

mysql> SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 );
Empty set (0.00 sec)

You can end your current session, then reconnect and try again.
Sometimes, I even see different results for exactly the same SQLs.

This is weird.  The error and reproduction of same seems non-deterministic. Now I get expected result 6 times with and without whitepace.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(20));
INSERT INTO t1 VALUES('11');
SELECT c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) FROM t1;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 );
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 );
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 );
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ;

I just restarted the server. Before that I tried several times and sometimes got wrong result both with and without trailing whitespace (but mostly with).

More randomness / non deterministic behavior here:

-- this is a new connection to the server

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(20));
INSERT INTO t1 VALUES('11');
SELECT c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) FROM t1;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- empty set
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- empty set
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- empty set

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 CHAR(20));
INSERT INTO t1 VALUES('11');
SELECT c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ) FROM t1;
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- empty set
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 ); -- expected result
SELECT * FROM t1 WHERE c1 <=  REPEAT( SUBSTR( UPPER('Rdlpikti') , 1 , 2 ), 8 )   ; -- expected result

Server is 5.7.10 for Winddows 64 bit.

==30918== Thread 24:
==30918== Invalid read of size 8
==30918==    at 0x4C2B1D0: memcpy@GLIBC_2.2.5 (in
/usr/lib64/valgrind/vgpreload_memcheck-amd64-linux.so)
==30918==    by 0x134735E: String::copy(String const&) (sql_string.cc:249)
==30918==    by 0x146606B: Item_cache_str::cache_value() (item.cc:10141)
==30918==    by 0x14644D1: Item_cache::has_value() (item.cc:9589)
==30918==    by 0x14662DD: Item_cache_str::val_str(String*) (item.cc:10178)
==30918==    by 0x147554F: Arg_comparator::compare_string()
(item_cmpfunc.cc:1660)
==30918==    by 0x148979D: Arg_comparator::compare() (in
/export-b/home/didrik/gitclone/trunk-review/bin-dbg/sql/mysqld)
==30918==    by 0x147817B: Item_func_le::val_int() (item_cmpfunc.cc:2630)
==30918==    by 0x1144519: evaluate_join_record(JOIN*, QEP_TAB*)
(sql_executor.cc:1494)
==30918==    by 0x1143EA5: sub_select(JOIN*, QEP_TAB*, bool)
(sql_executor.cc:1299)
==30918==    by 0x1143743: do_select(JOIN*) (sql_executor.cc:959)
==30918==    by 0x114172C: JOIN::exec() (sql_executor.cc:214)
==30918==    by 0x11AFB1A: handle_query(THD*, LEX*, Query_result*, unsigned
long long, unsigned long long) (sql_select.cc:194)
==30918==    by 0x1172A1E: execute_sqlcom_select(THD*, TABLE_LIST*)
(sql_parse.cc:5102)
==30918==    by 0x116C54B: mysql_execute_command(THD*, bool)
(sql_parse.cc:2769)
==30918==    by 0x1173853: mysql_parse(THD*, Parser_state*)
(sql_parse.cc:5508)

Noted in 5.8.0 changelog.

The REPEAT() function did not properly handle output from the
SUBSTR() function.