MySQL Bugs: #113180: Unexpected Results when Using Math Function SIN

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

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

Hello Suyang Zhong,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Also gives incorrect result in 5.7