Custom data types

[Michael Widenius]

Hi!

>>>>> "Antony" == Antony T Curtis <antony@stripped> writes:

Antony> I'm using my own custom datatype (DECIMAL as a packed BCD fixed-point
Antony> number) and I have the following situation:

>> SELECT PART, MFTR, CURR_PLM_DC1 FROM INVNSC WHERE COMM_CURR = "EUP" AND
> CURR_PLM_DC1 <= 0.0;
Antony> +----------+------+--------------+
Antony> | PART     | MFTR | CURR_PLM_DC1 |
Antony> +----------+------+--------------+
Antony> | 1.5KE15A | STM  |       0.1600 |
Antony> +----------+------+--------------+
Antony> 1 row in set

>> SELECT PART, MFTR, CURR_PLM_DC1 FROM INVNSC WHERE COMM_CURR = "EUP" HAVING
> CURR_PLM_DC1 <= 0.0;
Antony> Empty set


Antony> The above two statements return different results. Significantly, the
Antony> first one returns erronious results!

Antony> So - apart from ploughing through the code to see how HAVING differs
Antony> from WHERE, I was wondering what is the difference between the
Antony> comparison done by the two methods and why would it cause it to yield
Antony> different results.

Antony> I'm pretty sure that there is something wrong in my own custom decimal
Antony> class - just a helpful pointer would be appreciated.

Try using EXPLAIN on both queries.  I am sure that the queries are
using different index methods to access the rows in the table.

Is curr_plm_dc1 a key in the table ?

If yes, it may be some problem in the key_pack() function.

Try also to run the first query under a mysqld version run with --debug
The trace log should show you the key usage and this should give you a
clue of what's wrong!

Regards,
Monty