Bug #95927 XOR operator returns incorrect result for strings with a floating-point number

• Submitted: 22 Jun 2019 11:32
• Modified: 1 Apr 2020 9:56
• Reporter: Manuel Rigger
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.44, 5.7.26, 8.0.16
• OS: Ubuntu
• CPU Architecture: x86

[22 Jun 2019 11:32] Manuel Rigger

Description:
The XOR operator interprets strings like '0.5' as FALSE, while the other logical operators interpret them as TRUE.

How to repeat:
SELECT FALSE XOR '0.5'; -- expected: 1, actual: 0

For the other logical operators, '0.5' is evaluated to TRUE:

SELECT FALSE OR '0.5'; -- 1
SELECT TRUE AND '0.5'; -- 1
SELECT NOT '0.5'; -- 0

[22 Jun 2019 13:11] MySQL Verification Team

Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh

[22 Jun 2019 14:02] Manuel Rigger

An additional observation:

SELECT NOT('0.5' XOR (FALSE)); -- expected: 1, actual: 0
SELECT NOT('0.5' XOR (NOT TRUE)); -- 1

[17 Jul 2019 14:21] OCA Admin

Contribution submitted via Github - Fix bug https://bugs.mysql.com/bug.php?id=95927 
(*) Contribution by Oleksandr Peresypkin (Github peresypkin, mysql-server/pull/275#issuecomment-512265738): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_298398772.txt (text/plain), 896 bytes.

[17 Jul 2019 14:21] OCA Admin

Contribution submitted via Github - Fix bug https://bugs.mysql.com/bug.php?id=95927 
(*) Contribution by Oleksandr Peresypkin (Github peresypkin, mysql-server/pull/276#issuecomment-512265603): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_298409842.txt (text/plain), 869 bytes.

[17 Jul 2019 14:21] OCA Admin

Contribution submitted via Github - Fix bug https://bugs.mysql.com/bug.php?id=95927 
(*) Contribution by Oleksandr Peresypkin (Github peresypkin, mysql-server/pull/278#issuecomment-512265408): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_298424019.txt (text/plain), 874 bytes.

[23 Jul 2019 11:15] Manuel Rigger

Thanks a lot for fixing this! I could just verify in 8.0.17 that the behavior is now as expected.

[2 Oct 2019 15:45] Tor Didriksen

Posted by developer:
 
Fixed by     WL#12358: Ensure that all predicates in SQL conditions are complete
    Itemization of AND, OR and XOR operators ensure that all operand
    expressions are complete boolean expressions.

[1 Apr 2020 9:56] Manuel Rigger

I'm closing this issue, since this is working now.