Incorrect result when comparing a number with CONNECTION_ID

[Syang111]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Consider the following statements.

In theory, query1 and query2 should return the same result. However, query1 return nothing while query2 return a CONNECTION_ID 2097158( CONNECTION_ID is different when you reproduce).

CREATE TABLE t0(c0 BIGINT , c1 BLOB(301) , c2 BOOL);
INSERT INTO t0 VALUES (1, 1, 1);


SELECT f1 FROM (SELECT (CONNECTION_ID()) AS f1 FROM t0) AS t WHERE ((f1)>=(-1.487944961E9));  --query1
Empty set (0.00 sec)

SELECT f1 FROM (SELECT (CONNECTION_ID()) AS f1, (((CONNECTION_ID())>=(-1.487944961E9))) IS TRUE AS flag FROM t0) AS t  WHERE flag=1;  --query2
+---------+
| f1      |
+---------+
| 2097158 |
+---------+
1 row in set (0.00 sec)

2. What is your TiDB version? (Required)

v7.6.0, v5.0.1

[windtalker]

The root cause is for query 1

SELECT f1 FROM (SELECT (CONNECTION_ID()) AS f1 FROM t0) AS t WHERE ((f1)>=(-1.487944961E9));

The planner tries to convert -1.487944961E9 to uint64(https://github.com/pingcap/tidb/blob/v7.6.0/pkg/expression/builtin_compare.go#L1423), and inside the convert

https://github.com/pingcap/tidb/blob/v7.6.0/pkg/types/convert.go#L169-L183

func ConvertFloatToUint(flags Flags, fval float64, upperBound uint64, tp byte) (uint64, error) {
	val := RoundFloat(fval)
	if val < 0 {
		if !flags.AllowNegativeToUnsigned() {
			return 0, overflow(val, tp)
		}
		return uint64(int64(val)), overflow(val, tp)
	}

	ret, acc := new(big.Float).SetFloat64(val).Uint64()
	if acc == big.Below || ret > upperBound {
		return upperBound, overflow(val, tp)
	}
	return ret, nil
}

It returns uint64(int64(val)), but in https://github.com/pingcap/tidb/blob/v7.6.0/pkg/expression/builtin_compare.go#L1611-L1624

		if isExceptional && arg1.GetType().EvalType() == types.ETInt {
			// Judge it is inf or -inf
			// For int:
			//			inf:  01111111 & 1 == 1
			//		   -inf:  10000000 & 1 == 0
			// For uint:
			//			inf:  11111111 & 1 == 1
			//		   -inf:  00000000 & 1 == 0
			if arg1.Value.GetInt64()&1 == 1 {
				isPositiveInfinite = true
			} else {
				isNegativeInfinite = true
			}
		}

It use arg1.Value.GetInt64()&1 == 1 to check if the return value is +inf or -inf, which is clearly not a right way since uint64(int64(val)) itself is actually a random value based on val

[zimulala]

Thanks for your report!
We fixed this issue in v8.2.0, and the corresponding PR is #53590.

You can give it a try. I will close this issue for now. If you have any related questions in the future, please feel free to continue providing feedback.

[XuHuaiyu]

dup with #41736