MySQL Bugs: #88405: BIGINT function value causes failure of other WHERE conditions

Bug #88405	BIGINT function value causes failure of other WHERE conditions
Submitted:	8 Nov 2017 13:53	Modified:	14 Nov 2017 14:23
Reporter:	David Webb	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.19	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Situation:
I have a stored function F with a single string input which digests it and returns an unsigned BIGINT value. F is DETERMINISTIC NOSQL.

I have a table T with a column C which stores an unsigned bigint value.

This query finds a correct match:

SELECT * FROM T WHERE C=F("some text")

But if I add any conditions to the WHERE clause, then it fails to produce any matches. For example:

SELECT * FROM T WHERE 1=1 AND C=F("some text")

It seems that MySQL is unable to evaluate any other conditions if there is a condition which depends on the function call. I tried nesting the query but that didn't help. The workaround I have found is to cast the function output as a CHAR:

SELECT * FROM T WHERE 1=1 AND C=CAST(F("some text") AS CHAR)

I am connecting over ODBC and running the queries in Workbench if that helps your analysis.

How to repeat:
Set up tables and a function as described above.

Hello David Webb,

Thank you for the report.
Could you please provide exact repeatable test case( create table, sample data and create function)? I tried with the provided description but not seeing the issue.
 
============5.7.19/20/21

DROP TABLE IF EXISTS T;
CREATE TABLE T(C BIGINT UNSIGNED NOT NULL);
INSERT INTO T VALUES(18446744073709551614),(18446744073709551615);
DROP FUNCTION IF EXISTS F;
CREATE FUNCTION F (A CHAR(20)) RETURNS BIGINT UNSIGNED DETERMINISTIC NO SQL RETURN 18446744073709551615;

mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

Thanks,
Umesh

I set up a test schema using your exact code. It works OK. Then I compared what was different about my schema, and my elimination I found that it relates to having an index on the BIGINT column. 

Using your example, add an index on column C (either INDEX or UNIQUE, it doesn't matter which). Then run the test query again and you should see the problem.

A further discovery: the problem seems to exist only when the returned unsigned BIGINT value is in the upper half of the unsigned range (i.e. 2^63 or greater, or above 7FFFFFFF in hex or above 9223372036854775807 decimal).

So the SELECT with two or more conditions works if F returns:
9223372036854775807
But it fails if F returns:
9223372036854775808 or higher.

So I think this is a problem with the indexing of UNSIGNED BIGINT fields.

(typo, I mean hex 7FFF FFFF FFFF FFFF of course!)

Thank you for the feedback.
I just tried adding unique key/key etc, still no change at my end. I request you to provide exact repeatable test case to confirm this issue at our end. You may want to mark it as private after posting your test case here.

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

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T(C BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO T VALUES(18446744073709551614),(18446744073709551615);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DROP FUNCTION IF EXISTS F;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION F (A CHAR(20)) RETURNS BIGINT UNSIGNED DETERMINISTIC NO SQL RETURN '18446744073709551615';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE T ADD UNIQUE KEY C_idx (C);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> show create table T\G
*************************** 1. row ***************************
       Table: T
Create Table: CREATE TABLE `T` (
  `C` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `C_idx` (`C`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thanks. I am using the same MySQL instructions for a test as you, but I observe the following differences between your system and mine. The default character set on your server is latin1. Mine is utf8mb4 with collation utf8mb4_unicode_ci. Can you try that?

The strange thing is that the query finds its match if there is only 1 condition (WHERE C=F('something')) but fails when I include a second condition (WHERE 1=1 AND...), but only if F(x) is in the top half of the range. This points to some kind of deep problem with the query optimiser. Perhaps the optimiser throws itself into SIGNED comparison mode for BIGINT when evaluating more than one condition. That would explain why it finds a match for values if they are in the bottom half (7FFF... and below) but not for larger unsigned numbers which would have a negative value if they were signed.

Thanks, looks like this is observed on 5.7.19 but no longer on 5.7.20.
I couldn't locate which exact bug fixed but this is no longer reproducible on GA build. Please could you confirm this at your end?

rm -rf 88412
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/88412 
bin/mysqld --no-defaults --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --basedir=$PWD --datadir=$PWD/88412 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=$PWD/88412/log.err 2>&1 &

-- 5.7.19

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: 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.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T(C BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO T VALUES(18446744073709551614),(18446744073709551615);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DROP FUNCTION IF EXISTS F;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION F (A CHAR(20)) RETURNS BIGINT UNSIGNED DETERMINISTIC NO SQL RETURN '18446744073709551615';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> ALTER TABLE T ADD KEY C_idx (C);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
Empty set (0.00 sec)

-- 5.7.20

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: 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.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T(C BIGINT UNSIGNED NOT NULL)DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO T VALUES(18446744073709551614),(18446744073709551615);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DROP FUNCTION IF EXISTS F;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION F (A CHAR(20)) RETURNS BIGINT UNSIGNED DETERMINISTIC NO SQL RETURN '18446744073709551615';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.01 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE T ADD KEY C_idx (C);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from T where C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from T where 1=1 AND C=F('AABBCCDDEEFF');
+----------------------+
| C                    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

I have now upgraded from 5.7.19 to 5.7.20 and I confirm that the problem appears to be solved by that even though the changelog says nothing about it. If I come across it again I will be sure to let you know. Thanks for your help.

Closing according last comment.