[Gambas-user] Bug when executing SQL..

[Stephen Bungay]

On Tuesday 22 August 2006 14:08, Benoit Minisini wrote:
> On Tuesday 22 August 2006 19:50, Stephen Bungay wrote:
> > The reason I say this is because I am getting some rather unexpected
> > results. For example the select statement;
> >
> > SELECT COALESCE(SUM(rec_payment), SUM(rec_Payment),0) AS Payment
> > FROM tbl_salespayments
> > WHERE rec_create > 1158921569;
> >
> >   When executed from within the database manager  (or my gambas
> > application) returns the sum of the column rec_payment, but when executed
> > in MySQL it returnes zero (0) (which is what it should return in this
> > case given the data I have in the table).
> >  Are there known issues with whatever engine is being used?
> >
> > Steve.
>
> There are no specific hack when using Exec() in Gambas, except the
> substitution of '&X' patterns, but you don't have any of one in your
> request.
>
> You should give more detailed informations (the more as you can). And tell
> me what does COALESCE, because it is the first time I see it. :-)
>
> Regards,

  OK, I will try to be clearer. First of all the COALESCE is not a part of the 
problem and I can (and have) proved it here. For example this

SELECT SUM(rec_payment) AS Payment
FROM tbl_salespayments 
WHERE rec_create < 1158921569;

and this

SELECT SUM(rec_payment) AS Payment
FROM tbl_salespayments 
WHERE rec_create > 1158921569;

  Both produce the same result even though the WHERE clauses are different!
While this...

SELECT rec_payment AS Payment
FROM tbl_salespayments 
WHERE rec_create < 1158921569;

and this

SELECT rec_payment AS Payment
FROM tbl_salespayments 
WHERE rec_create > 1158921569;

don't produce the same results, and that is what I would expect given that the 
conditions in the WHERE clauses are different.

 I suppose I can work around it by not using SUM. I could fetch the result set 
returned by

SELECT rec_payment AS Payment
FROM tbl_salespayments 
WHERE rec_create > 1158921569;

 And if it returns an empty result set then it follows that the 
SUM(rec_payment) should have returned a zero. If 
however it returns a result set with records in it then I will have to step 
through the result set and sum the fields manually. :(

Steve.