Thread:

I'm currently handling a migration from an Informix system to both mySQL
and Postgres (same data in each).
Having uploaded the DB's into both systems, from Informix, I am having
some problems carrying out a query. The query, which I believe, is in
standard SQL is as follows:

SELECT field1, SUM(field2) as Field2, SUM(field3)
FROM table1 tb1, table2 tb2
WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006'
AND tb1.field6 = tb2.field7
AND tb1.field8 = tb2.field8
AND tb2.field9 BETWEEN 50.00 AND 150.00
GROUP BY field1
HAVING SUM(field2) BETWEEN 95.00 AND 100.00
ORDER BY 2 DESC, field1;


*The 'field(n)' and 'table(n)' values have been substituted for actual
field names!

The results I get from Informix and mySql return identical results,
however postgres includes a great deal more i.e. 11 rows from Informix
and mySQL, 203 from postgres.

Does anyone have any idea why this disparity may exist?




This email may contain information which is privileged or confidential. This information is intended only for the named
recipient.If you are not the intended recipient, please be aware that disclosure, copying, distribution or use of this
informationis prohibited. If you have received this email in error, we would be grateful if you would inform us as soon
aspossible by telephoning +44 (0) 1769 573431, or by email to admin@molevalleyfarmers.com and then delete this email.
Viewsor opinions expressed in this email are those of the writer, and are not necessarily the views of Mole Valley
FarmersLimited or its subsidiary companies. Unless specifically stated, this email does not constitute any part of an
offeror contract.
 

On 4 aug 2006, at 08.55, Jonathan Sinclair wrote:
> SELECT field1, SUM(field2) as Field2, SUM(field3)
> FROM table1 tb1, table2 tb2
> WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006'
> AND tb1.field6 = tb2.field7
> AND tb1.field8 = tb2.field8
> AND tb2.field9 BETWEEN 50.00 AND 150.00
> GROUP BY field1
> HAVING SUM(field2) BETWEEN 95.00 AND 100.00
> ORDER BY 2 DESC, field1;
>
> The results I get from Informix and mySql return identical results,
> however postgres includes a great deal more i.e. 11 rows from Informix
> and mySQL, 203 from postgres.
>
> Does anyone have any idea why this disparity may exist?

An obvious source of ambiguity is the date comparison:

tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006'

Is that interval a day or a month (mm/dd/yyyy or dd/mm/yyyy)? Check  
your datestyle setting and make sure all systems interpret the date  
correctly (or at least the same).


Sincerely,

Niklas Johansson