COUNT on varchar column, counts nulls as well - how can I prevent that?

[romsok]

Hi,

I am trying to run AGGR functions on a table - COUNT (column_name) and it looks like it counts <null>'s as well.
Is there a way to prevent it?

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users

[Martin Kersten]

Roman Sokolyuk wrote:
> Hi,
>
> I am trying to run AGGR functions on a table - COUNT (column_name) and
> it looks like it counts <null>'s as well.
> Is there a way to prevent it?
SELECT COUNT(T.A)
FROM T
WHERE T.A IS NOT NULL

>
>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@...
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users

[Niels Nes]

On Mon, Nov 02, 2009 at 02:21:29PM -0500, Roman Sokolyuk wrote:
> Hi,
>
> I am trying to run AGGR functions on a table - COUNT (column_name) and
> it looks like it counts <null>'s as well.
> Is there a way to prevent it?

This is a bug, count(col) shouldn't count nulls

Niels

> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@...
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1090 GB Amsterdam, The Netherlands
room C0.02/M3.46,  phone ++31 20 592-4098
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@...

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users

[Stefan Manegold]

On Mon, Nov 02, 2009 at 02:21:29PM -0500, Roman Sokolyuk wrote:
> Hi,
>
> I am trying to run AGGR functions on a table - COUNT (column_name) and it
> looks like it counts <null>'s as well.

Using the Aug2009-SP2 release on my 64-bit Fedora 10 desktop,
this appears to work fine and as expected for me:

sql>select count(*) from tables;
+-------+
| L7    |
+=======+
|    30 |
+-------+
1 tuple
sql>select count(query) from tables;
+-------+
| L10   |
+=======+
|     3 |
+-------+
1 tuple
sql>select count(*) from tables where query is not null;
+-------+
| L11   |
+=======+
|     3 |
+-------+
1 tuple
sql>select count(*) from tables where query is null;
+-------+
| L12   |
+=======+
|    27 |
+-------+
1 tuple
sql>

Stefan

> Is there a way to prevent it?

> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> http://p.sf.net/sfu/devconference
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@...
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

--
| Dr. Stefan Manegold | mailto:Stefan.Manegold@... |
| CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
| 1090 GB Amsterdam   | Tel.: [phone]       |
| The Netherlands     | Fax : [phone]       |

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
MonetDB-users mailing list
MonetDB-users@...
https://lists.sourceforge.net/lists/listinfo/monetdb-users