[Gambas-user] Bug when executing SQL..

[Benoit Minisini]

On Tuesday 22 August 2006 20:58, Stephen Bungay wrote:
> 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.
>

Did you check the menu entry in the database manager that toggles the 
debugging mode? In this mode, all request strings sent to the database 
backend are printed to the standard error output. This way, you will be able 
to check that what is sent is exactly the same as what you entered directly 
in the 'mysql' program.

Otherwise you should send me a dump of your table so that I can test. Maybe it 
is a problem related to mySQL version.

Regards,

-- 
Benoit Minisini