[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.
>

Actually this is not a database related bug, it is a bug in the TableView that 
does not refresh correctly! 

Here is a patch for 1.9.39 that should fix the problem.

Regards,

-- 
Benoit Minisini
-------------- next part --------------
A non-text attachment was scrubbed...
Name: CTableView.cpp
Type: text/x-c++src
Size: 32258 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20060823/8592b282/attachment.cpp>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: CTableView.h
Type: text/x-c++hdr
Size: 4388 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20060823/8592b282/attachment.h>