On Wednesday 03 March 2010 20:12:51 K. John Wu wrote:
> Hi, Andreas,
Hi John,
> Here is how I understand the SQL standard,
>
> select count(*) ...
>
> should return a table with one row and one column, and the content of
> the single value should be the value of "count(*)". So if you ask how
> many rows the table object has, it should return 1, similarly for the
> number of columns. One needs to get the actual content of the table
> to find out how many rows satisfy the query conditions.
COUNT(*) tells us how many rows the result set (table) has.
Short example in mysql:
mysql> SELECT COUNT(*) FROM login_att;
+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
On the other hand:
mysql> SELECT COUNT(login_expire) FROM login_att;
+---------------------+
| COUNT(login_expire) |
+---------------------+
| 2 |
+---------------------+
This counts all NON NULL values of the specified field in the table.
What fastbit currently does (without my patch): It always reports 1 as
COUNT(*) which is definately wrong here.
> I guess what you want might be a tabula object. To get that one,
> leave the select clause a blank string or a nil pointer. Since I did
> not see anything in SQL standard about a missing select clause, I have
> implementation a table with a certain number of rows but without
> columns -- a fairly silly object, but might be what you are after.
What i am currently doing is i am faciliating the table::select interface of
fastbit. This creates a tabele object for count(*) and this one currently
reports 1 for every COUNT(*) query. So i am not using the lower level stuff of
fastbit yet.
These are the lines in mensa.cpp:
else if (stricmp(sel, "count(*)") == 0) { // count(*)
int64_t nhits = computeHits(cond);
if (nhits < 0) {
return 0;
}
else {
std::string des = name_;
if (! desc_.empty()) {
des += " -- ";
des += desc_;
}
return new ibis::tabele(cond, des.c_str(), nhits);
}
}
Here fastbit correctly counts the number of hits and creates a correct tabele
object.
After selecting via table::select i am generating a cursor:
ibis::table::cursor *cur;
cur=result->createCursor();
while (0 == cur->fetch()) {
[...]
else if (tps[j]==ibis::ULONG)
{
uint64_t ivalue;
cur->getColumnAsULong(nms[j],ivalue);
}
[...]
Here i am always getting a 1 due to the problem in the cursor.
Am i doing something wrong here? If not i am pretty sure i hit a bug :S
> If anyone knows exactly what the SQL standard intended, please let us
> know.
I am not really into the SQL standard but i am pretty sure that the mysql
behaviour is as intended by the standard here.
> John
Kind regards,
Andreas Streichardt
_______________________________________________
FastBit-users mailing list
[email protected]
https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
