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: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 5.7.8, 5.6.28, 5.7.10 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[18 Dec 2015 4:25]
Su Dylan
[18 Dec 2015 6:37]
MySQL Verification Team
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
[18 Dec 2015 9:58]
Su Dylan
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;
[18 Dec 2015 11:57]
MySQL Verification Team
Thank you for the feedback. Verified with 5.6.28, 5.7.10 builds. Thanks, Umesh
[18 Dec 2015 11:58]
MySQL Verification Team
// 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>
[18 Dec 2015 12:18]
Peter Laursen
*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)
[18 Dec 2015 12:24]
Peter Laursen
MySQL 5.5 not affected
[18 Dec 2015 12:25]
Peter Laursen
MySQL 5.6(.28) not affected either.
[18 Dec 2015 12:31]
Su Dylan
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.
[18 Dec 2015 12:40]
Peter Laursen
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).
[18 Dec 2015 12:56]
Peter Laursen
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.
[22 Dec 2015 7:35]
Tor Didriksen
==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)
[16 Jan 2016 15:55]
Paul DuBois
Noted in 5.8.0 changelog. The REPEAT() function did not properly handle output from the SUBSTR() function.