MySQL Bugs: #110209: PRIMARY KEY should not change the query result.

Bug #110209	PRIMARY KEY should not change the query result.
Submitted:	24 Feb 2023 16:01	Modified:	28 Feb 2023 13:17
Reporter:	John Jove	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Whether a column has the PRIMARY KEY constraint should not change the query results on it. However, in the following cases, when I remove the PRIMARY KEY constraint from case 1, and construct case 2. The same SELECT statement returns different results, in which case 1 returns a normal query result, while case 2 returns an error.

How to repeat:
Run the following two cases, and observe their query results.

-- case 1
CREATE TABLE t0(c0 char(1) PRIMARY KEY );
INSERT INTO t0 VALUES ('1');
SELECT * FROM t0 WHERE (- ((~ (((t0.c0) IS NULL))))); -- 1

-- case 2
CREATE TABLE t0(c0 char(1));
INSERT INTO t0 VALUES ('1');
SELECT * FROM t0 WHERE (- ((~ (((t0.c0) IS NULL))))); -- ERROR 1690 (22003): BIGINT value is out of range in '-(~((`test`.`t0`.`c0` is null)))'

Hi John, 

Are you using GIPK mode (sql_generate_invisible_primary_key) ?

Cheers,

I deploy MySQL with its default configuration.
I check its value by the following command. OFF is returned.

show variables like 'sql_generate_invisible_primary_key'; -- OFF

Hi Mr. Jove,

Thank you very much for your bug report.

We do not think that your report is a bug. It is simply because you are using a column type from the CHAR domain, to which you can not apply all the operators that you used.

However, we have replaced your CHAR column type with BIGINT and got 100 % the same result.

That is why this report has become a verified bug.