[Gambas-user] Bug when executing SQL..

[Benoit Minisini]

On Tuesday 22 August 2006 23:35, Stephen Bungay wrote:
> 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,
>
>   Would you like the dump posted here or sent to another address?
>
> Steve.
>

Send it to my address, not to the mailing-list.

-- 
Benoit Minisini