Member Avatar for naui95

Can somebody explain me why does this mysql query

SELECT * FROM mytable WHERE F='d2f2' AND Tot>Pmin AND Tot<Pmax

omit this row

ID ¦IDa¦F ¦Pmax¦Pmin¦Tot

124¦160¦d2f2¦200 ¦12 ¦182

thanks!

  • 4 Contributors
  • 4 Replies
  • 211 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

A few focused diagnostics will quickly show why that row is being excluded. The usual causes (hidden/trailing bytes in column F, NULLs in Tot/Pmin/Pmax, column-type surprises such as unsigned or VARCHAR, or index/table corruption) cover what , and were pointing toward. The checks below make each cause explicit and safe to inspect.

Run these to confirm column definitions and the exact bytes stored for the problematic row:

SHOW CREATE TABLE mytable;
SHOW FULL COLUMNS FROM mytable;

SELECT id, F, LENGTH(F) AS bytes, CHAR_LENGTH(F) AS chars, HEX(F) AS hexF,
       Tot, Pmin, Pmax,
       (Tot IS NULL) AS TotIsNull, (Pmin IS NULL) AS PminIsNull, (Pmax IS NULL) AS PmaxIsNull
FROM mytable
WHERE id = 124;

HEX(F) reveals invisible characters (0x20 = space, 0x00 = NUL, etc.). LENGTH vs CHAR_LENGTH distinguishes multi-byte encodings. The IS NULL flags show whether any numeric operand will make the comparison evaluate to NULL (and so be excluded).

Bytewise equality and simple fixes:

SELECT id FROM mytable WHERE BINARY F = 'd2f2';

If BINARY matches while plain = did not, the issue is collation/nonprintables. Common fixes: update to trimmed/clean value (remove NULs), or change column type/collation. Example cleaning (run on a copy/after backup):

UPDATE mytable
SET F = TRIM(REPLACE(F, '\x00', ''))
WHERE id = 124;

If the row still does not appear, check table health and indexes:

CHECK TABLE mytable;
ANALYZE TABLE mytable;
-- For MyISAM only:
REPAIR TABLE mytable;

For InnoDB, rebuild indexes with ALTER TABLE ... FORCE or drop/add the index. Always work on a backup/copy when repairing. The diagnostics above directly test the hypotheses raised by and and will point to the exact corrective action.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

Show a SQL dump of that row.

Member Avatar for AndrisP

Is the data types are identical on columns Pmin, Pmax and Tot?

Member Avatar for naui95

Yes it's all INT

Member Avatar for Reverend Jim

I did it in MS SQL and got the expected results. Is it possible that the actual value of F iin the table is 'd2f2 ' or some such instead of 'd2f2'? Try the query

SELECT * FROM mytable WHERE F LIKE '%d2f2%' AND Tot > Pmin AND Tot < Pmax

or

SELECT * FROM mytable WHERE F = 'd2f2'

and see what you get

Edited by Reverend Jim
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.