[Gambas-user] Bug when executing SQL..

[Stephen Bungay]

On Tuesday 22 August 2006 15:14, Benoit Minisini wrote:
> 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,

  Will check that debug is enabled. Will fwd you a dump of the table for 
testing if required.