CODEWITHSUNDEEP
mysqlsql
Tito
Tito

Reputation: 9054

mysql same records returned even if I give a wrong equality value

Query 1

SELECT supplier.supplierId, supplierName, supplierTypeId, iconFileName, description, url, defaultLanguageCode
                    FROM supplier, supplier_service_area
                    WHERE supplier.supplierId = supplier_service_area.supplierId
                    AND postalCode = '72631dsdf'
                    ORDER BY supplierTypeId, supplierName, supplier.supplierId, supplierServiceAreaId limit 0,2;

Query 2

SELECT supplier.supplierId, supplierName, supplierTypeId, iconFileName, description, url, defaultLanguageCode
                    FROM supplier, supplier_service_area
                    WHERE supplier.supplierId = supplier_service_area.supplierId
                    AND postalCode = '72631'
                    ORDER BY supplierTypeId, supplierName, supplier.supplierId, supplierServiceAreaId limit 0, 2;

I am giving postalCode = '72631dsdf' in query 1 and postalCode = '72631' in the second query.

But both queries are returning correct results.Why is that so ? The second query is valid.But the first query is also returning same records.Its as if LIKE condition has been applied , but I am using '='

Upvotes: 1

Views: 45

Answers (1)

user2864740
user2864740

Reputation: 61935

SQL usually allows various implicit conversions, which is what is happening here: the "invalid" input is being silently converted to a REAL.

From http://sqlfiddle.com/#!2/26bacb/11 (see the section below as this shows conversions result but not "why"):

SELECT
  ("72631dsd") as a,
  (0.0 + "72631dsd") as b,
  (0.0 + "a72631dsd") as c;

The results are:

a           |  b      |  c
"72631dsd"  |  72631  |  0

The rules in MySQL are covered in 12.2. Type Conversion in Expression Evaluation:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa ..

The following rules describe how conversion occurs for comparison operations: ..

  • [...]

  • In all other cases, the arguments are [implicitly converted and] compared as floating-point (real) numbers.

Upvotes: 1

Related Questions