Bug #113180 Unexpected Results when Using Math Function SIN
Submitted: 22 Nov 2023 10:12 Modified: 22 Nov 2023 10:58
Reporter: Suyang Zhong Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.33, 8.1.0, 8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2023 10:12] Suyang Zhong
Description:
Consider the test case below.  It is unexpected that both queries return the row since the expression `SIN(1150199883)=c1` cannot be evaluated to `TRUE` at the same time.

mysql> CREATE TABLE t0(c0 INT, c1 INT, PRIMARY KEY(c0));
INSERT INTO t0(c0, c1) VALUES (1, 0);
CREATE UNIQUQuery OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c0, c1) VALUES (1, 0);
E INDEX i1 ON t0(c1 , c0 );Query OK, 1 row affected (0.00 sec)

mysql> CREATE UNIQUE INDEX i1 ON t0(c1 , c0 );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t0 WHERE SIN(1150199883)=c1; 
+----+------+
| c0 | c1   |
+----+------+
|  1 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t0 WHERE NOT SIN(1150199883)=c1; 
+----+------+
| c0 | c1   |
+----+------+
|  1 |    0 |
+----+------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE t0(c0 INT, c1 INT, PRIMARY KEY(c0));
INSERT INTO t0(c0, c1) VALUES (1, 0);
CREATE UNIQUE INDEX i1 ON t0(c1 , c0 );

SELECT * FROM t0 WHERE SIN(1150199883)=c1; -- 1 0
SELECT * FROM t0 WHERE NOT SIN(1150199883)=c1; -- 1 0
[22 Nov 2023 10:58] MySQL Verification Team
Hello Suyang Zhong,

Thank you for the report and test case.

regards,
Umesh
[23 Nov 2023 8:46] Tor Didriksen
Posted by developer:
 
Also gives incorrect result in 5.7