Possible Bug (I think) in count()

[Benigno]

In the table this is taken from, activo is either 1 or 0. In the table
which is legacy MySQL it is defined as INT(1)

These are the results I am getting:

In [46]: db(db.direccionesgrupo.activo == 1).count()
Out[46]: 58L

In [48]: a =db().select(db.direccionesgrupo.activo.sum())

In [49]: print a
-------> print(a)
SUM(direccionesgrupo.activo)
112

In [51]: b = db(db.direccionesgrupo.activo==1).select('count(*)')

In [52]: print b
-------> print(b)
count(*)
58

The correct value is the one done with the aggregate sum().

[mdipierro]

print

db(db.direccionesgrupo.activo==1)._select('count(*)')

do you see anything wrong in the generated sql?

[Benigno]

Hmmm, it translates on its own 1 to True like this:
"SELECT count(*) FROM direccionesgrupo WHERE
direccionesgrupo.activo='T';"
Which results in 58 rows

Whereas if it does
"SELECT count(*) FROM direccionesgrupo WHERE
direccionesgrupo.activo=1;"
Then result is 112

MySQL seems to take anything it doesnt understand as a 0 and returns
all the values = 0. (If instead of 'T' I enter any other char value
still gets the ceroes as if it was retreiving false values).

True or false are like
SELECT count(*) FROM direccionesgrupo WHERE activo=TRUE;
or
SELECT count(*) FROM direccionesgrupo WHERE activo=FALSE;

for MySQL. Which are actually just aliases TRUE for 1, FALSE for 0.

Probably need to change the SELECT generated to 1 or 0, or not change
value from 1 to 'T' on its own.

If instead of ==1 I use ==True, same result (though I guess you are
aware of that).
db(db.direccionesgrupo.activo==True)._select('count(*)')
Out[3]: "SELECT count(*) FROM direccionesgrupo WHERE
direccionesgrupo.activo='T';"

[mdipierro]

web2py for portability reasons defines:

'boolean': 'CHAR(1)',

If you are using a legacy database and you are not using a CHAR(1) for
boolean, you need to use "string" instead of "boolean" when you define
the field in web2py.

Massimo

[mdipierro]

On a second thought. If you are summing it perhaps it is not a
'boolean' or a 'string' but an 'integer' that you wish to use as a
boolean with 1,0 instead of True/False.

Massimo

[Benigno]

You are absolutelly right again, I was surprised that the comparison
value was changed from 1 to 'T'. I had assumed that boolean was
ussually mapped to int fields rather than char(1). By defining the
field in web2py as integer, everything works as expected.

Thanks again.
Benigno.