MySQL select doesn't retrieve my inserts

Question

I don't understand why I can't retrieve an INSERT with a (seemingly) exactly identical SELECT statement :

INSERT INTO parcelrates(fromzip, tozip, weight, length, height, width) VALUES (69003, 69001, 141.096, 3.93701, 3.93701, 1.5748)

SELECT * FROM parcelrates WHERE fromzip = 69003 AND tozip = 69001 AND weight = 141.096 AND length = 3.93701 AND height = 3.93701 AND width = 1.5748

This simply returns nothing.

Here is my table structure :

id      INT(11)   AI PK
fromzip INT(11)
tozip   INT(11)
weight  FLOAT
length  FLOAT
height  FLOAT
width   FLOAT
date    TIMESTAMP (on update CURRENT_TIMESTAMP())
rates   LONGTEXT

My guess is that I've messed up the float values but I can't figure out how.

Edit:

Here is the create statement:

CREATE TABLE `parcelrates` (
  `id` int(11) NOT NULL,
  `fromzip` int(11) NOT NULL,
  `tozip` int(11) NOT NULL,
  `weight` float NOT NULL,
  `length` float NOT NULL,
  `height` float NOT NULL,
  `width` float NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `rates` longtext CHARACTER SET utf8
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Unfortunately the only log I find (MAMP) is mysql_error_log.err, which does not log anything :

tomsihap@itchy > /Applications/MAMP/logs $ tail mysql_error_log.err
    160304 10:10:12 InnoDB: Completed initialization of buffer pool
    160304 10:10:12 InnoDB: highest supported file format is Barracuda.
    160304 10:10:13  InnoDB: Waiting for the background threads to start
    160304 10:10:14 InnoDB: 5.5.42 started; log sequence number 9768747
    160304 10:10:14 [Note] Server hostname (bind-address): '0.0.0.0'; port: 8889
    160304 10:10:14 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
    160304 10:10:14 [Note] Server socket created on IP: '0.0.0.0'.
    160304 10:10:14 [Note] Event Scheduler: Loaded 0 events
    160304 10:10:14 [Note] /Applications/MAMP/Library/bin/mysqld: ready for connections.
    Version: '5.5.42'  socket: '/Applications/MAMP/tmp/mysql/mysql.sock'  port: 8889  Source distribution

SELECT * FROM parcelrates WHERE fromzip = '69003' AND tozip = '69001' AND weight = '141.096' AND length = '3.93701' AND height = '3.93701' AND width = '1.5748' — Priyanshu, Mar 04 '16 at 09:17
@tomsihap Try to enable additional logging http://stackoverflow.com/questions/5441972/how-to-see-log-files-in-mysql — Dan, Mar 04 '16 at 09:23

score 2 · Accepted Answer · answered Mar 04 '16 at 08:53

Please refer the manual which gives the reason:

Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems.

You can use the decimal datatype and you will see that the result is coming. Example:

create table parcelrates
(id      INT(11)   ,
fromzip INT(11),
tozip   INT(11),
weight  decimal(21,9),
length  decimal(21,9),
height  decimal(21,9),
width   decimal(21,9),
date    TIMESTAMP ,
rates   LONGTEXT
  );

  INSERT INTO parcelrates(fromzip, tozip, weight, length, height, width) 
  VALUES (69003, 69001, 141.096, 3.93701, 3.93701, 1.5748);

SELECT * FROM parcelrates WHERE fromzip = 69003 AND tozip = 69001 AND weight = 141.096 AND length = 3.93701 AND height = 3.93701 AND width = 1.5748

SQL FIDDLE DEMO

Thanks a lot ! This is working. Does this rounds to 10^-9 (which would still be a problem then) or crop the decimals to store the ones before 10-^9 ? — tomsihap, Mar 04 '16 at 09:22
I agree with you on fixing the use of like without wildcards (it was useless IMHO). — Lelio Faieta, Mar 04 '16 at 09:40

score 0 · Answer 2 · answered Mar 04 '16 at 08:52

0

Do you run your scripts on same transaction? Because of this, you may not see inserted data when you query database. Try to run insert script first, then run select script.

answered Mar 04 '16 at 08:52

ahmet

702
1
10
29

I've tried on the same script and on different scripts. – tomsihap Mar 04 '16 at 09:22

score 0 · Answer 3 · edited Mar 04 '16 at 09:47

0

As replied by one of the fellow member above, decimal numbers are not stored as given. So, the following query should yield you the required result.

SELECT * FROM parcelrates 
WHERE fromzip = 69003 
AND tozip = 69001 
AND weight LIKE '141.096' 
AND length LIKE '3.93701' 
AND height LIKE '3.93701' 
AND width LIKE '1.5748'

edited Mar 04 '16 at 09:47

Rahul Tripathi

168,305
31
280
331

answered Mar 04 '16 at 09:34

Vineela Panuganti

3
2

Thats not a good idea to approach when dealing with numbers :) – Rahul Tripathi Mar 04 '16 at 09:48

MySQL select doesn't retrieve my inserts

3 Answers3