I WAS WRONG ! Re: additional details on IS NULL problem

After all this, it seems I was doing something stupid. I had originally used Float.MIN_VALUE, which is the smallest POSITIVE minimum value. I also tried hard coding -3.40282e+38, but this had issues as Stephan pointed out. What I neglected to try, and what works for float is public static float flt_nil = -Float.MAX_VALUE; Sorry for all the confusion, but I learned something. Null values for float types DO work. Thanks for your patience and help - Lynn On 5/24/16, 10:44 AM, "users-list on behalf of Lynn Carol Johnson" wrote:

Yes, we’re using float as 32 bit, storing into a real.

Still no sunshine with your suggestions below. Java doesn’t allow me to assign public static float flt_nil = (float) -340282346638528859811704183484516925440; It complains “the literal -340282346638528859811704183484516925440 of type int is out of range”. This isn’t surprising as 32 bits will only hold up to -2^31 (-2,147,483,648)

Using static float flt_nil = (float) 0xff7fffff; gives me the value -8388609.0, which is incorrect.

Does storing null work for you using “float” types? Is this just a java-to-C issue? Still looks like we’ll need to pick a default other than NULL for our “real” data.

Thanks - Lynn

On 5/24/16, 9:53 AM, "users-list on behalf of Stefan Manegold" wrote:

...

Hi Lynn,

public static float flt_nil = (float) -3.40282e+38;

does not necessarily create the exact bit pattern as C macro -FLT_MAX, mainly as "-3.40282e+38" is only an approximation of the exact value: "-340282346638528859811704183484516925440"

You can either try

public static float flt_nil = (float) -340282346638528859811704183484516925440;

or use the actual bit pattern in hex notation, e.g.,

public static float flt_nil = (float) 0xff7fffff;

Please take my Java(?) code snippets with a grain of salt; I'm everything but a Java expert --- or better, I'm a Java illiterate ...

Be also aware that C type float, MAL typr :flt and SQL type REAL are 32-bit, while C type double, MAL type :dbl, and SQL types FLOAT & DOUBLE are 64-bit

Hence, for 64-bit types you'd need to use one of

public static double dbl_nil = (double) -179769313486231570814527423731704356798070567525844996598917476803157260 7 8002853876058955863276687817154045895351438246423432132688946418276846754 6 7035375169860499105765512820762454900903893289440758685084551339423045832 3 6903222948165808559332123348274797826204144723168738177180919299881250404 0 26184124858368;

public static double dbl_nil = (double) 0xffefffffffffffff;

Hope this helps ...

Best, Stefan

----- On May 24, 2016, at 3:42 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

Thanks, Stephan.

Short response good news: using int_nil = ‹2147483648 (-2^31) is translated properly to NULL for int values. The IS NULL and IS NOT NULL queries are fine.

Short response bad news: I am unable to get NULL to work for Float regardless of what value I used. Details below:

I replaced my Float.MIN_VALUE with flt_nil defined as below :

public static float flt_nil = (float) -3.40282e+38;

Ran again with just 50 rows (created as binary files). At this point my belief is NULL does not work at all for float with binary files. There are 16 of the 50 values for column amesbyd_weir that are null, but I can only select those values if I check for the column < -3.40282e+38. Here are the sql results:

sql>select count(amesbyd_weir) from weirtestnull; +------+ | L1 | +======+ | 50 | +------+ 1 tuple (1.933ms) sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir IS NULL; +-----+-----+--------------+ | chr | pos | amesbyd_weir | +=====+=====+==============+ +-----+-----+--------------+ 0 tuples (1.291ms) sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir = -3.40282e+38; +-----+-----+--------------+ | chr | pos | amesbyd_weir | +=====+=====+==============+ +-----+-----+--------------+ 0 tuples (0.528ms) sql> sql>select chr,pos,amesbyd_weir from weirtestnull where amesbyd_weir < -3.40282e+38; +------+--------+-----------------+ | chr | pos | amesbyd_weir | +======+========+=================+ | 10 | 228864 | -3.40282e+38 | | 10 | 228962 | -3.40282e+38 | | 10 | 228972 | -3.40282e+38 | | 10 | 229011 | -3.40282e+38 | | 10 | 229025 | -3.40282e+38 | | 10 | 229187 | -3.40282e+38 | | 10 | 229367 | -3.40282e+38 | | 10 | 229405 | -3.40282e+38 | | 10 | 229444 | -3.40282e+38 | | 10 | 229593 | -3.40282e+38 | | 10 | 229615 | -3.40282e+38 | | 10 | 229671 | -3.40282e+38 | | 10 | 229829 | -3.40282e+38 | | 10 | 230000 | -3.40282e+38 | | 10 | 230052 | -3.40282e+38 | | 10 | 230120 | -3.40282e+38 | +------+--------+-----------------+ 16 tuples (2.999ms) sql>

NOTE: Dumping the 50 row database to a .txt file, dropping the table and reloading into monetdb did NOT fix it as it did when I used Float.NaN values. That is actually irrelevant for us as our db tables are large (2.1 Billion rows in 1 table, 80M in another) and as we add additional columns, binary loading is the only way to go. We love the binary load option, and have been very happy with the speed of monetdb processing.

For now, we¹re going to follow PLINK convention and use -99 for our Float null values.

Thanks - Lynn

On 5/23/16, 4:06 PM, "users-list on behalf of Stefan Manegold" wrote:

...

Hi Lynn,

thanks for diving into this!

The information that you loaded your data using from binary files using copy *binary* into is crucial for us to understand and investigate the problem!

In fact, I recently discovered and fixed a similar bug; cf., https://www.monetdb.org/bugzilla/show_bug.cgi?id=3937

This fix is in the Jul2013-SP3 bug-fix release. Hence, I'm wondering why you (still) experience this (or a similar) problem.

Of what type are your data / columns? Integer? decimal? floating point? string?

If more then one type, do you experience these problems with all types or just with some? If only some, which?

If the problem occurs only with floating point numbers, are you sure you're using the exact NULL representation that MonetDB uses?

Otherwise a potential source of the problem --- I'm just speculating --- could be the following:

You might have "non-valid" floating point numbers like NaN or INF in your data that are not known to SQL and hence MonetDB. If so, When parsed from text, MonetDB does (seems to?) silently turn them into (MonetDB's internal representation of) NULL. However, when loding your binary data, MonetDB stores these values "as-is" not recognizing them as NULL; then processing does not recognize them as NULL, either; only rendering produces NULL from the "non-valid" internal representation.

For the correct internal representation of NULL values in MonetDB per type see https://www.monetdb.org/wiki/MonetDB_type_system

If my speculation is true, we'd need to consider making our copy binary into bulk-loading for floating point numbers (more) fail-save by actually scanning each column and checking for "invalid" floating point number --- whatever that might cost ...

Best, Stefan

----- On May 23, 2016, at 8:37 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

Hi Anthony -

I¹ve narrowed down the problem. The bug isn¹t related to size, but rather how the table data was created/loaded. Loading from a .txt file gives good behavior, loading from binary does not.

Normally I create binary files for each column and use the binary bulk loader to load. That is what was failing. When I first tested with 50 columns, I created the test file from sql via the command copy (select * Š) into Œ/workdir/ŠkellyFirst50.txt using delimiters Œ\t¹,¹\n¹;

Then I created a new table called kellyFirst50 and used the ³COPY INTO kellyfirst50 from Œ/workdirŠkellyFirst50.txt¹ using delimiters Œ\t,¹\n¹; ² command. Checking for ³IS NULL² worked, so I thought it was a size problem.

But, when trying to figure out at what point the file size mattered, I ran java scripts to create binary files of ever decreasing sizes, then loaded them via the COPY BINARY commands. Checking for IS NULL always failed here, even when I only loaded 50 values.

Here are the results when loading from binary files into monetdb. Using IS NOT NULL works, but IS NULL does not:

sql>\

operation successful

sql>\

50 affected rows

sql>

sql>select count(*) from weirtestnull;

+------+

| L1 |

+======+

| 50 |

+------+

1 tuple (2.014ms)

sql>select count(amesbyd_weir) from weirtestnull;

+------+

| L1 |

+======+

| 50 |

+------+

1 tuple (0.865ms)

sql>select count(amesbyd_weir) from weirtestnull where amesbyd_weir IS NOT NULL;

+------+

| L1 |

+======+

| 34 |

+------+

1 tuple (3.005ms)

sql>

sql>select * from weirtestnull where amesbyd_weir IS NULL;

+-----+-----+---------------+--------------+--------------+----------- - -- --+--------------------+------------------+----------------+

| chr | pos | amesbycn_weir | amesbyd_weir | amesbyf_weir | amesbynam_weir | | amesbyturkeyp_weir | dentbyflint_weir | dentbynam_weir |>

+=====+=====+===============+==============+==============+=========== = == ==+====================+==================+================+

+-----+-----+---------------+--------------+--------------+----------- - -- --+--------------------+------------------+----------------+

0 tuples (4.041ms) !4 columns dropped!

sql>

Dump the data into a .txt file:

sql>copy (select * from weirtestnull) into '/workdir/db_outfiles/weirtestnull.txt' using delimiters '\t','\n';

50 affected rows (3.809ms)

sql>

Now drop the table, recreate it, but this time load from the txt file created above:

sql>drop table weirtestnull;

operation successful (1.948ms)

sql>\

operation successful

sql>copy into weirtestnull from '/workdir/db_outfiles/weirtestnull.txt' using delimiters '\t','\n';

50 affected rows (57.643ms)

sql>

Re-run the commands ­ I now get correct values for IS NULL:

sql>select count(*) from weirtestnull;

+------+

| L1 |

+======+

| 50 |

+------+

1 tuple (1.851ms)

sql>select count(amesbyd_weir) from weirtestnull;

+------+

| L1 |

+======+

| 34 |

+------+

1 tuple (0.983ms)

sql>

sql>select * from weirtestnull where amesbyd_weir IS NULL;

+------+--------+------------+------------+------------+------------+- - -- ---------+-------------+-------------+-------------+-------------+---- - -- ------+

| chr | pos | amesbycn_w | amesbyd_we | amesbyf_we | amesbynam_ | amesbyturke | | dentbyflint | dentbynam_w | dentbyturke | flintbynam_ | flintbyturk |>

: : : eir : ir : ir : weir : yp_weir : _weir : eir : ypen_weir : weir : eypen_weir :>

+======+========+============+============+============+============+= = == =========+=============+=============+=============+=============+==== = == ======+

| 10 | 228864 | 2.95496e-0 | null | null | -0.0001263 | null | null | -0.00028749 | | null | -0.00029885 | null |

: : : 5 : : : 25 : : : 4 : : 1 : :

| 10 | 228962 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 228972 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229011 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229025 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229187 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229367 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229405 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229444 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229593 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229615 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229671 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 229829 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 230000 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 230052 | null | null | null | null | null | null | null | null | null | | null |

| 10 | 230120 | null | null | null | null | null | null | null | null | null | | null |

+------+--------+------------+------------+------------+------------+- - -- ---------+-------------+-------------+-------------+-------------+---- - -- ------+

16 tuples (5.2

My java program uses Float.NaN and Integer.MIN_VALUE, Byte.MIN_VALUE to get ³null² stored in the db.

When we load directly from binary, are the null values created differently? Is something not marked that gets marked when loading from text files?

With this information, can you reproduce, or do you want me to give you my binary files in a dropbox somewhere?

Thanks - Lynn

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list