I dumped the first 50 rows to a separate table and ran the queries again. With the same columns, but just the first 50 rows, the queries work as expected. I verified the full table (83153144 entries) still fails. It appears size matters - I’ll try to narrow it down. sql>select count(*) from kellyfirstfifty; +------+ | L1 | +======+ | 50 | +------+ 1 tuple (1.881ms) sql> sql>select count(bkn22vsbkn40_weir_and_cockerham_fst) from kellyfirstfifty; +------+ | L1 | +======+ | 16 | +------+ 1 tuple (3.625ms) sql> From: users-list mailto:users-list-bounces+lcj34=cornell.edu@monetdb.org> on behalf of Anthony Damico mailto:ajdamico@gmail.com> Reply-To: Communication channel for MonetDB users mailto:users-list@monetdb.org> Date: Monday, May 23, 2016 at 9:55 AM To: Communication channel for MonetDB users mailto:users-list@monetdb.org> Subject: Re: issue with IS NULL hi, looks sorta buggy to me.. but i'm not able to reproduce using a small table. any chance you could share a minimal reproducible example using only the first few records of your table `kelly`? thanks On Mon, May 23, 2016 at 9:47 AM, Lynn Carol Johnson mailto:lcj34@cornell.edu> wrote: Good morning - I am having an issue identifying columns with a null value in the July2015-SP3 monetdb version. A sample of my table with null values is below: sql>select * from kelly limit 10; +------+-------+------------+-------------+-------------+-------------+-------------+ | chr | pos | bkn22vsbkn | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop |> : : : 16_weir_an : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ :> : : : d_cockerha : cockerham_f : cockerham_f : _weir_and_c : weir_and_co :> : : : m_fst : st : st : ockerham_fs : ckerham_fst :> : : : : : : t : :> +======+=======+============+=============+=============+=============+=============+ | 1 | 10004 | null | null | null | null | null | | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | | 1 | 10077 | null | null | null | null | null | | 1 | 10084 | null | null | null | null | null | | 1 | 10097 | null | null | 0.0823631 | 0 | null | | 1 | 10112 | null | null | null | null | null | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | | 1 | 10162 | null | null | null | null | null | | 1 | 10270 | null | null | null | null | null | | 1 | 10299 | null | null | null | null | null | +------+-------+------------+-------------+-------------+-------------+-------------+ 10 tuples (18.9s) !144 columns dropped! note: to disable dropping columns and/or truncating fields use \w-1 sql> Verify the number of values in the table: sql>select count(1) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (479.905ms) sql> I would like to pull all values that are NOT null from a specific column. My understanding is that running a “count” on a column should return the count of the non-null values. When I run a count on bkn22vsbkn40_weir_and_cockerham_fst I get the following result, which is the same value as the total number of entries above: sql>select count(bkn22vsbkn40_weir_and_cockerham_fst) from kelly; +----------+ | L1 | +==========+ | 83153144 | +----------+ 1 tuple (860.533ms) sql> The number returned is the same as the total number of entries, but it should be less. However, if I run a query to count the values that are NOT NULL, it counts them: sql>select count(1) from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL; +----------+ | L1 | +==========+ | 41754484 | +----------+ 1 tuple (1.0s) sql> When I try to select column values based on my selected column having a null value, no results are returned. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NULL limit 10; +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ | chr | pos | bkn22vsbkn16_weir_and_cockerham_fst | bkn22vsbkn35_weir_and_cockerham_fst | bkn22vsbkn40_weir_and_cockerham_fst |> +=====+=====+=====================================+=====================================+=====================================+ +-----+-----+-------------------------------------+-------------------------------------+-------------------------------------+ 0 tuples (1.1s) !146 columns dropped! sql> When I select column values based on my selected column value NOT null, I get values. sql>select * from kelly where bkn22vsbkn40_weir_and_cockerham_fst IS NOT NULL limit 10; +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ | chr | pos | bkn22vsbkn1 | bkn22vsbkn3 | bkn22vsbkn4 | bkn22vstemp | bkn22vstrop | bkn33vsbkn1 | bkn33vsbkn2 | bkn33vsbkn35 | bkn33vsbkn40 |> : : : 6_weir_and_ : 5_weir_and_ : 0_weir_and_ : erate_cinta : ical_cinta_ : 6_weir_and_ : 2_weir_and_ : _weir_and_co : _weir_and_co :> : : : cockerham_f : cockerham_f : cockerham_f : _weir_and_c : weir_and_co : cockerham_f : cockerham_f : ckerham_fst : ckerham_fst :> : : : st : st : st : ockerham_fs : ckerham_fst : st : st : : :> : : : : : : t : : : : : :> +======+=======+=============+=============+=============+=============+=============+=============+=============+==============+==============+ | 1 | 10045 | 0.050576 | 0 | 0.154324 | 0.130628 | 0.0398957 | 0.0289548 | 0 | 0 | 0.124971 | | 1 | 10097 | null | null | 0.0823631 | 0 | null | 0.0406325 | 0.0226116 | 0.0226116 | 0 | | 1 | 10128 | 0.00185251 | 0 | 0 | 0.00272515 | 0.00594128 | null | 0 | null | null | | 1 | 10707 | 0.380632 | 0 | 0 | 0 | 0 | 0.44799 | 0.0206532 | 0.0111431 | 0.0234156 | | 1 | 10751 | 0.0462002 | 0.0439648 | 0.0084101 | 0.0505068 | 0 | 0.0475206 | 0 | 0.0453142 | 0.00522015 | | 1 | 10755 | 0.00147929 | 0 | 0.00290363 | 0.00560938 | 0 | null | 0 | 0 | null | | 1 | 10760 | 0 | 0.011568 | 0 | 0 | 0.0882033 | 0.00590868 | 0 | 0 | 0 | | 1 | 10814 | null | null | 0 | null | null | null | null | null | 0 | | 1 | 10838 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0587818 | 0 | 0 | 0.0621118 | | 1 | 10839 | 0.133482 | 0 | 0 | 0.00616907 | 0.146222 | 0.0639509 | 0 | 0 | 0.0558049 | +------+-------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+ 10 tuples (2m 36s) !140 columns dropped! sql> My question: AM I doing something wrong, or is this a bug? I am running the july2015 sp3: [lcj34@cbsudc01 monetdbFiles]$ monetdb -v MonetDB Database Server Toolkit v1.1 (Jul2015-SP3) [lcj34@cbsudc01 monetdbFiles]$ Thanks - Lynn _______________________________________________ users-list mailing list users-list@monetdb.orgmailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list