About Null in array fields


#1

hi, I have a table such as create table t1 (f1 smallint, f2 smallint); i want to insert somedata into f2, there maybe some NULL value, i run insert into t1 valus (1, {1, null}), but it not works, how to do this ?


#2

Hi @giverking,

Thanks for reporting this, I was able to replicate. We’ll have to take a look, but should be fairly simple. In the meantime, you can insert nulls into arrays via copy from.

Regards


#3

Hi @giverking,

In the meanwhile the bug is being corrected you can use the bulk copy command as @darwin suggested, or you can use the database’s internal values used to define a NULL value. For small ints this value is -32767, for tiny ints is -128 and so on

mapdql> create table t1 (f1 smallint, f2 smallint[]);
mapdql> insert into t1 values (1,{1,2,3,4,-32768});
mapdql> select * from t1;
f1|f2
1|{1, 2, 3, 4, NULL}

#4

it seems to insert Null into table, but how to query by this Null ?
i try “select * from t1 where f[5] is null” or “select * from t1 where f[5]=-32768”, but return no records.


#5

there is the same problem in other dataType field, such as text ; please let me know if you have solute this problem


#6

hi @giverking,

which release of omnisci database are you running? I can’t reproduce your issue on various 4.3 releases

mapdql> create table t1 (f1 smallint, f2 smallint[]);
mapdql> insert into t1 values (1,{1,2,3,4,-32768});
Execution time: 94 ms, Total time: 95 ms
mapdql> select * from t1 where f2[5] is null;
1|{1, 2, 3, 4, NULL}
1 rows returned.
Execution time: 143 ms, Total time: 144 ms
mapdql> select * from t1 where f2[5] is not null;
No rows returned.

The workaround I supplied works for numeric type only; it can’t be applied to text datatype


#7

You are right, but how about another numeric filed ? such as float, double, text, especial text dataType, How to Insert into Null value… another sample:
create table te (f1 smallint, f2 textt);
insert into te values (1, {‘one’, null}) is error, but if insert into te values (1, {‘one’, ‘null’}), this ‘null’ not really means null, it means a string ‘null’, it can not query by select * from te where f2[2] is null ;


#8

I think you can’t do anything for text fields, and I don’t know how to force an inssert of a NULL value for float/doubles in an array because I’m getting an error, so the workaround is usable just for interger datatypes