Zero is imported as Null

[schwab]

When importing from csv files where a column has values like 0,1,2,3 etc, the 0 value always imports as a Null into the graph.

`

hash_value	rescue_nausea_meds
e9ab6037a25b0fae297751422279084f	2
beeb21285afaf07ea1b557703cbd00dd	1
93964ffcda314d4941908dfd6c6f116c	2
b70c1dfd353a22ddcad8fe5a09953913	1
d820181b209561b370caeaa5b5a88112	1
bb0420cbb067aa764cc686cb9cd06e2d	1
37b473f8cc72b0ff3d229d757a0715d5	0
0fae0f1578fd4d48f9c2d06b4ea14c51	0
6300351ca9b19e1bc9eddfb9f961de9d	0
425a7c5efa8ef22a6b90b4568d20fb3e	0
0f86ca97659d942fe5d22ab096c7a1f5	0
c62df49732032e95cb0baeeef390998d	0
850197a09e64129f28108f187d692a96	0

`

Results in:

graph.query graph:ortho "MATCH (f:fact_ortho) WHERE f.hash_value='37b473f8cc72b0ff3d229d757a0715d5' RETURN   distinct f.hash_value, f.rescue_nausea_meds limit 10"
1) ["f.hash_value","f.rescue_nausea_meds"]
2) [["37b473f8cc72b0ff3d229d757a0715d5",null]]
3) ["Query internal execution time: 7.162228 milliseconds"]

We can also see that all the rows with 0 were imported that way...

graph.query graph:ortho "MATCH (f:fact_ortho) RETURN   distinct f.rescue_nausea_meds, count(f) limit 10"
1) ["f.rescue_nausea_meds","count(f)"]
2) [["1",504],["2",86],["3",3],[null,7363]]
3) ["Query internal execution time: 8.976518 milliseconds"]

This happens with multiple columns. How can we force the build insert to store real zeros?

Note: This bug is adversely affecting math operations as well. For instance queries with filters like "WHERE x.value < 1 RETURN count x" return no records even when the are 0 values since they are all set to null.

[jeffreylovitz]

@schwab I'm having trouble reproducing this behavior! The only ways to insert null values in the bulk-loader should be empty fields or by defining a field as NULL with the --field-types argument.

Using the following as zero.csv:

hash_value, rescue_nausea_meds
e9, 2
be, 0
93,
b7, 1

And a simple insertion command:
python3 bulk_insert.py G -n zero.csv

My results are:

127.0.0.1:6379> GRAPH.QUERY G "MATCH (a) RETURN a.hash_value, a.rescue_nausea_meds"
1) 1) "a.hash_value"
   2) "a.rescue_nausea_meds"
2) 1) 1) "e9"
      2) "2"
   2) 1) "be"
      2) "0"
   3) 1) "93"
      2) (nil)
   4) 1) "b7"
      2) "1"
3) 1) "Query internal execution time: 0.606562 milliseconds"

Are you running bulk_insert.py with any particular command-line flags? If rescue_nausea_meds is the last field in your CSV files, have you considered the possibility of editor misbehavior?

To offer a possible workaround, you can provide JSON with the --field-types argument to force a particular column to be interpreted as numeric, but I'm not certain that would help in this case, and would prefer to solve the root issue regardless.

[m4g005]

@jeffreylovitz: Try running the bulk_insert.py file with -q 2 with the following:

"hash_value","zero"
"75f0f686118f3771c2cdec71a1cfc0c8",0
"d18f044cfd0d9c9a8a7326ddda030106",1
"5c2dfecbcc9d02ffe5b8e20213895a5b",0

[jeffreylovitz]

Oof, what a stupid bug! I hadn't tested on QUOTE_NONNUMERIC, and there was a falsity check instead of an identity check in property conversion. (No other quote level actually converts inputs to a non-string type).

This should be fixed now! Sorry about this - now I'm gonna retreat to C, where the behaviors may be absurd but at least we have static typing.

[m4g005]

I'm not sure if this is the same bug exactly, but the following csv breaks after the fix:

"hash_value","timestamp","minute","week_number","month","day","year","qtr","month_name","iso_year","iso_week","second","week_day","hour","date"
"826ac790a0ab1a8d4cf548ab1fe81a59",1565766000.0,0,33,8,14,2019,3,"August",2019,33,0,2,0,"2019-08-14"
"91d2328b846d9aa3ba76aa9bf0a5ce0d",1565593200.0,0,33,8,12,2019,3,"August",2019,33,0,0,0,"2019-08-12"
"a81f8bb6741f0c467a941cc007fc0b89",1564642800.0,0,31,8,1,2019,3,"August",2019,31,0,3,0,"2019-08-01"

this is the stack trace:

Traceback (most recent call last):
  File "bulk_insert.py", line 165, in pack_props
    FIELD_TYPES[self.entity_str][field_type_idx]
TypeError: 'NoneType' object is not subscriptable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "bulk_insert.py", line 427, in <module>
    bulk_insert()
  File "/Users/matthewgo/sources/github/redisgraph-bulk-loader/lib/python3.7/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/Users/matthewgo/sources/github/redisgraph-bulk-loader/lib/python3.7/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/Users/matthewgo/sources/github/redisgraph-bulk-loader/lib/python3.7/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/matthewgo/sources/github/redisgraph-bulk-loader/lib/python3.7/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "bulk_insert.py", line 415, in bulk_insert
    process_entity_csvs(Label, nodes, separator)
  File "bulk_insert.py", line 329, in process_entity_csvs
    entity = cls(in_csv, separator)
  File "bulk_insert.py", line 180, in __init__
    self.process_entities(expected_col_count)
  File "bulk_insert.py", line 213, in process_entities
    row_binary = self.pack_props(row)
  File "bulk_insert.py", line 167, in pack_props
    props.append(prop_to_binary(field, None))
  File "bulk_insert.py", line 308, in prop_to_binary
    if prop_val.lower() == 'false':
AttributeError: 'float' object has no attribute 'lower'

run with python bulk_insert.py testme -h redis-graph -p 6381 -n "dim_date.csv" -q 2

[jeffreylovitz]

Ah, sorry about that - should have tested more thoroughly! I'll get this resolved tomorrow.

[schwab]

I'd come to the same conclusion about the bug being localized to the QUOTE_NONNUMERIC setting, but hadn't had time to write up my findings so far. In our case we are using quotes on all strings (pandas df.save(..., quoting=csv.QUOTE_NONNUMERIC) since they often have commas and other characters in them that cause issues. I did find however, that it's probably possible to use quote level 0 as long as the file is saved to csv using pandas default quoting setting. This should be a good work-around as it automatically quotes strings with commas.

[jeffreylovitz]

@schwab I wrote a comment about a possible design augmentation here earlier, and would love to hear your thoughts!

Of course we intend to make the bulk converter as sane as possible regardless of user parameters, but given how many unexpected behaviors the quoting+casting has already caused, I want to hear more from users about the experience that they'd prefer.

[jeffreylovitz]

@schwab This error should now be resolved, can you please verify?

[m4g005]

the dim_datetest worked fine.

[jeffreylovitz]

👍 Thank you! Closing this issue.