| Bug #115342 | Ambiguous Boolean Handling in DELETE Statement. | ||
|---|---|---|---|
| Submitted: | 14 Jun 2024 18:32 | Modified: | 14 Jun 2024 18:41 |
| Reporter: | Govinda Sapkota | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | OS: | Windows | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | DatabaseError, MySQL, QuerySyntax, SQL | ||
[14 Jun 2024 18:41]
MySQL Verification Team
Hi Mr. Sapkota, Thank you for your bug report. SQL Standard does prescribe that datatypes should not be mixed in any expression, what so ever. That would have resulted in most of our customers and users being unable to design their queries. Furthermore, all relational databases in the world, just like MySQL, try to come up with some common denominators for the comparison of different types. That is making life much easier to programmers, because not all of them are experts in query design. Hence, in your case where you have the expression cart_id='127' which can resolve to TRUE or FALSE, from one row to another, you have to AND compare it to non-Boolean value of '128' which is a string, but it probably always evaluates to TRUE. That is a design that is intentional in all relational databases and it is here to stay. Not a bug.
Description: The DELETE SQL statement delete from cart WHERE cart_id='127' and '128' executes successfully despite an ambiguous boolean expression ('128'). According to standard SQL syntax, boolean expressions in the WHERE clause are expected to evaluate to true or false based on the conditions provided. In this case, '128' is not a valid boolean expression and should ideally result in a syntax error or unexpected behavior, such as not deleting any records due to an invalid condition. How to repeat: delete from cart WHERE cart_id='127' and '128' Expected Outcome The SQL statement should either: Raise a syntax error due to the invalid boolean expression '128'. Evaluate the boolean expression correctly and delete records based on valid conditions (cart_id='127' and cart_id='128') if they exist. Actual Outcome The statement executes successfully without any syntax errors, potentially deleting records based only on the cart_id='127' condition and ignoring the invalid '128' condition. Suggested fix: DELETE FROM cart WHERE cart_id = '127' OR cart_id = '128'; Validating that input values for conditions (cart_id in this case) are of the expected data type (e.g., integer for numeric IDs). Sanitizing input values to avoid unexpected characters or conditions that could lead to ambiguous boolean evaluations.