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