MySQL Bugs: #118595: NULL are transformed to false in certain expression in prepared statement

Bug #118595	NULL are transformed to false in certain expression in prepared statement
Submitted:	7 Jul 15:16	Modified:	10 Jul 7:19
Reporter:	chi zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	9.3.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I find in certain experssions in prepared statement, NULL are transformed to false.

There is an example:

```
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT ((('') <= ('')) AND (?))';
EXECUTE prepare_query USING @a; -- 0
SELECT ((('') <= ('')) AND (NULL)); -- NULL
```

but if I directly select the bind value, it is still NULL, like `PREPARE prepare_query FROM 'SELECT (?)';` will return NULL 

How to repeat:
```
SET @a = NULL;
PREPARE prepare_query FROM 'SELECT ((('') <= ('')) AND (?))';
EXECUTE prepare_query USING @a; -- 0
SELECT ((('') <= ('')) AND (NULL)); -- NULL
```

Hello Chi Zhang,

Thank you for the bug report.
Imho this is not a bug, please replace single quote with double quotes like below and you will get expected results.

PREPARE prepare_query FROM "SELECT ((('') <= ('')) AND (?))";

Regards,
Ashwini Patil

Hi, thanks for your confirmation, I got it, sorry for this false report!

Bug #118592 marked as duplicate of this one.

Bug #118591 marked as duplicate of this one.

Hi,

Thank you for your work again. May I ask a question about this?

According to the document of AND operator shown in https://dev.mysql.com/doc/refman/8.4/en/logical-operators.html#operator_and, when one operand of an expression is NULL, the expression will be evaluated to 0 only if the other operand is 0. So in this case, I guess the left operand is ') <= (', which will be implicit to TRUE, so why is the result 0. I know I can get the right result with double quotes, but as this query does not trigger a syntax error, so I'm curious how this result was calculated.