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
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.org https://www.monetdb.org/mailman/listinfo/users-list