Anyone know what this is? (MySQL)

This is a table:

ID          Fruit
1            Apple
2            Apple
3            Banana


SELECT COUNT(Fruit) as num, Fruit FROM tblFruit GROUP BY Fruit

And you'd expect:
Fruit          num
Apple          2
Banana        1

Thats what you get on windows.. but on OSX I get

Fruit          num
Apple          145666842154
Banana        324565512854


Same database, same query. Only the OS changed. I have googled like a madman on this but I can fint sh*t.

This must be a known problem so I was hoping someone here could tell me about it.

Thanks

Eric DeBois
Tuesday, January 20, 2004

fix mysql. most of the people use it because they can get the source and "we can fix it".

now, it's time to do what you always wanted to do

Tuesday, January 20, 2004

Maybe OSX comes with more apples and bananas than Windows :-)
  -tim

a2800276
Tuesday, January 20, 2004

Not a bad thought, though I dont know jack about OSX and Im not in a position to dictate platform choices for my client.

Eric DeBois
Tuesday, January 20, 2004

Do you get the expected results if you do COUNT(*) or COUNT(ID) instead of COUNT(Fruit)?

Konrad
Tuesday, January 20, 2004

MySQL 3.23.58-Max produces correct result on Red Hat Linux.

Evgeny Gesin /Javadesk.com/
Tuesday, January 20, 2004

Konrad > No, any count /group by statement produces very weird numbers.

Eric DeBois
Tuesday, January 20, 2004

But your client saved a bunch of money didn't they?


Ask them if incorrect counts is worth "free" or they could use oracle personal edition (I think they make a version on about the same level as MSDE now don't they?)

Chris Ormerod
Tuesday, January 20, 2004

Eric,

I am guessing this is reproducible with any table and any scenario? Are you sure there are no decimals hidden in that long numeric? Are other functions having problems such as average, min and max? Does mysql consider their version production ready for the Mac? Have you tried contacting the mysql ab folks? They are fairly responsive, especially if they see licensing or support contracts in their future.

Good luck!

m
Tuesday, January 20, 2004

I've never seen that type of error on my copy of MySQL running on my Mac.  I can try this later today and verify, but I haven't seen any strange behavior like this.  And yes, MySQL has been on the Mac for quite a while, and this sounds like a possibly hosed config/install.

Lou
Tuesday, January 20, 2004

Academically, it would be interesting to know what part of the install/config could be so hosed that the thing can't COUNT. That's really impressive. :-p

Brad Wilson (dotnetguy.techieswithcats.com)
Tuesday, January 20, 2004

>Are you sure there are no decimals hidden in that long numeric?

Even if there were, where would you expect them to be to produce something like :
Apple          145666842154
Banana        324565512854

> Ask them if incorrect counts is worth "free" or they could use oracle personal edition
Even without mentioning that MySql have a commercial licence for their products, free != incorrect/dodgy results. Something really funky must be going on to produce that effect.

SC
Tuesday, January 20, 2004

I was taking a guess that the MySQL installation was improperly told the type of device it was running on (x86 versus PPC, etc) and this was some result of that (big-endian little-endian and whatnot).  Of course I'm not certain it would even compile in that state.

Eric, have you tried posting on some MySQL specific fora?

Lou
Tuesday, January 20, 2004

This does look exactly like a problem with numeric representation.  Try a recompile and reinstall.  Also give a shot at SELECT * FROM tblFruit and see if you notice anything funky, in case the database is corrupt.

Clay Dowling
Tuesday, January 20, 2004

I believe this is a bug in the MySQL compiled for Panther SERVER.  Installing the MySQL from www.mysql.com will fix the problem.  See the Apple message boards for more info.

Michael H. Pryor (fogcreek)
Tuesday, January 20, 2004

It is an incorrectly installed version of Mysql, more than likely some unix flavor was installed instead of the mac version.  The in correct counts are because of how different OS's consider the sizes of ints, floats, and so on. 

mysql expert
Tuesday, January 20, 2004

Great responses ppl. Thanks.

Micheal Pryor and mysql expert > Ill forward that to the admin of the server, thanks a bunch.

Eric DeBois
Tuesday, January 20, 2004

Good that you got an answer. But I have another question, why the general anger at MySql?

curious
Wednesday, January 21, 2004

Because MySQL is a horrible SQL DBMS that is, really, barely a DBMS.

MR
Wednesday, January 21, 2004

What you're getting doesn't look like one of these, but it's certainly worth a read of this page:

http://sql-info.de/mysql/gotchas.html

Section 1.2 on auto-increment (like identity in SQL-Sever), really makes me wonder what they were thinking ..

Colm Larkin
Friday, January 23, 2004

  Recent Topics

  Fog Creek Home