Selecting from a decimal field

What's this?

[rocknbil]

Am I missing something here?

server version: 4.1.20 (yeah I know - irrelevant)

mysql> describe some_table; 
+--------------+--------------+------+-----+------------+----------------+ 
¦ Field  ¦ Type   ¦ Null ¦ Key ¦ Default ¦ Extra   ¦ 
+--------------+--------------+------+-----+------------+----------------+ 
¦ id   ¦ int(11)  ¦  ¦ PRI ¦ NULL  ¦ auto_increment ¦ 
............ 
¦ price  ¦ decimal(6,2) ¦  ¦ MUL ¦ 0.00  ¦     
............ 
mysql> select count(*) from some_table where price <> '0.00'; 
ERROR 2013 (HY000): Lost connection to MySQL server during query 

Does this on both our server and my local XP install.
If I remove the quotes, it queries, but doesn't query correctly, of course.

[phranque]

try "EXPLAIN select count(*) from some_table where price <> '0.00';" and see if it tells you something useful.

[rocknbil]

pranque thank you, I thought I'd leave out all the stuff I'd tried, my posts are often bloated. :-) Using explain does the same thing, disconnects from the database with the error. I have other decimal fields in the same table, and other tables with decimal fields, and the entire DB is duplicated on my local computer - both disconnect when querying the price field with the simple query above.

I'm hoping it's something dumb I've overlooked or forgotten. A few other interesting results:

mysql> select some_table.id from some_table where price > '20' limit 20;
Empty set (0.00 sec)
(There are over 1700 records, most over 20.00)

mysql> select some_table.id from some_table where price = '2.95' limit 20;
Empty set (0.00 sec)
(There are known records with this value)

Here's where it really gets wonky. This more complex query runs JUST FINE! Note I have applied the same as above, > 20 on the "price" field:

select count(distinct some_table.id) from some_table  
 left join item_options on some_table.id=item_options.some_table_id  
 left join category_table on (some_table.category_id=category_table.id  
 or some_table.category_two_id=category_table.id)  
 left join subcategory_table on 
 (some_table.subcategory_id_id=subcategory_table.id  
  or some_table.subcategory_two_id=subcategory_table.id 
 ) where (  
 (if 
  (item_options.price>some_table.price,item_options.price,some_table.price) > '20') 
  and ( 
  (some_table.width <>'0.00' 
   and some_table.height > 0 
   and some_table.depth <>'0.00' 
   and some_table.weight <>'0.00' 
  ) 
  or 
  (item_options.width <>'0.00' 
   and item_options.height > 0 
   and item_options.depth <>'0.00' 
   and item_options.weight <>'0.00' 
  ) 
  ) 
 ) 
 and some_table.item_enabled=1 
; 

+-----------------------------+ 
¦ count(distinct some_table.id) ¦ 
+-----------------------------+ 
¦       205 ¦ 
+-----------------------------+ 

This DB has been running for two years, the scripts associated with it are cruising along fine - but coincidentally nowhere does it perform the simple query shown in post #1 - either I am very lucky for never needing it or have overlooked something really important!

[phranque]

have you tried:

select id from some_table where price <> '0.00';

and then getting the number of rows?
i know it's not an ultimate solution but it may tell you something new...

[rocknbil]

Yes I did - however - at 3AM PST - I got it. :-)

I began looking closely at how the complex query worked and the simple one did not, and more importantly, why.

After digging around the docs I found (or should say, was reminded) that indexes are not always used in all queries. I located an article that demonstrated when you are querying multiple columns, it will often only use the index on the leftmost column even though all searched columns are indexed.

So I dropped the index - the query works. But this column is involved in where's and searches, we need an index on it. Which comes to why.

As I suspected it was something dumb, or maybe I was confused (which is dumb with an excuse.) It used to be that decimal fields were treated as text, or I got that impression. But they're not, they are still numeric. So if an index is created this way

index(fieldname(3))

For whatever reason, the query fails. Crete an index this way on a decimal field,

index(fieldname)

And it returns correct queries for count(*) . . . where price='0.00'. Learn something new every day!

I can sleep now. :-)

[phranque]

good one!
i was hoping the "explain ..." would give us that answer...