Changeset: 1f6077dfb61f for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=1f6077dfb61f
Modified Files:
monetdb5/modules/mal/tablet.c
sql/backends/monet5/sql_result.c
sql/test/copy/Tests/int_parse.stable.err
sql/test/copy/Tests/int_parse.stable.out
sql/test/copy/Tests/int_parse_best.stable.out
sql/test/copy/Tests/null_as_string_errors.sql
sql/test/copy/Tests/null_as_string_errors.stable.err
sql/test/copy/Tests/null_as_string_errors.stable.out
Branch: resultset
Log Message:
Better handle NULL strings
The old code mixed the MonetDB 'nil' value with the SQL NULL
Also embedded user defined null strings were not kept as is.
diffs (truncated from 342 to 300 lines):
diff --git a/monetdb5/modules/mal/tablet.c b/monetdb5/modules/mal/tablet.c
--- a/monetdb5/modules/mal/tablet.c
+++ b/monetdb5/modules/mal/tablet.c
@@ -900,7 +900,12 @@ SQLload_parse_line(READERtask *task, int
error++;
goto errors1;
} else *line++ = 0;
- }
+ } else
+ /* check for user defined NULL string */
+ if( fmt->nullstr && task->fields[i][idx] &&
strncasecmp(task->fields[i][idx], fmt->nullstr, fmt->null_length + 1) == 0) {
+ task->fields[i][idx] = 0;
+ line +=fmt->null_length +1;
+ }
/* eat away the column separator */
for (; *line; line++)
@@ -927,10 +932,6 @@ SQLload_parse_line(READERtask *task, int
i--;
}
endoffieldcheck:;
- /* check for user defined NULL string */
- if( fmt->nullstr && task->fields[i][idx] &&
strncasecmp(task->fields[i][idx], fmt->nullstr, fmt->null_length + 1) == 0) {
- task->fields[i][idx] = 0;
- }
}
#ifdef _DEBUG_TABLET_
if(error)
@@ -943,9 +944,9 @@ SQLload_parse_line(READERtask *task, int
for (i = 0; i < as->nr_attrs; i++) {
task->fields[i][idx] = line;
#ifdef _DEBUG_TABLET_
- MT_lock_set(&errorlock, "insert_val");
- mnstr_printf(GDKout,"before #2 %s\n",line);
- //MT_lock_unset(&errorlock, "insert_val");
+ MT_lock_set(&errorlock, "insert_val");
+ mnstr_printf(GDKout,"before #2 %s\n",line);
+ //MT_lock_unset(&errorlock, "insert_val");
#endif
/* eat away the column separator */
for (; *line; line++)
@@ -958,9 +959,9 @@ SQLload_parse_line(READERtask *task, int
goto endoffield2;
}
#ifdef _DEBUG_TABLET_
- //MT_lock_set(&errorlock, "insert_val");
- mnstr_printf(GDKout,"#after #23 %s\n",line);
- MT_lock_unset(&errorlock, "insert_val");
+ //MT_lock_set(&errorlock, "insert_val");
+ mnstr_printf(GDKout,"#after #23 %s\n",line);
+ MT_lock_unset(&errorlock, "insert_val");
#endif
/* not enough fields */
if (i < as->nr_attrs - 1) {
diff --git a/sql/backends/monet5/sql_result.c b/sql/backends/monet5/sql_result.c
--- a/sql/backends/monet5/sql_result.c
+++ b/sql/backends/monet5/sql_result.c
@@ -564,8 +564,6 @@ static void *
int len;
const char *e;
- if( strcmp(s,"nil")== 0)
- return NULL;
if (type == TYPE_str) {
sql_column *col = (sql_column *) c->extra;
int len;
@@ -586,8 +584,7 @@ static void *
}
c->data = p;
}
-
- if (s == e) {
+ if (s == e || *s == 0) {
len = -1;
*(char *) c->data = 0;
} else if ((len = (int) GDKstrFromStr(c->data, (unsigned char
*) s, (ssize_t) (e - s))) < 0) {
@@ -603,6 +600,9 @@ static void *
}
return c->data;
}
+ // All other values are not allowed to the MonetDB nil value
+ if( strcmp(s,"nil")== 0)
+ return NULL;
len = (*BATatoms[type].atomFromStr) (s, &c->len, (ptr) &c->data);
if (len < 0)
diff --git a/sql/test/copy/Tests/int_parse.stable.err
b/sql/test/copy/Tests/int_parse.stable.err
--- a/sql/test/copy/Tests/int_parse.stable.err
+++ b/sql/test/copy/Tests/int_parse.stable.err
@@ -30,20 +30,24 @@ stderr of test 'int_parse` in directory
# 11:23:33 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-4649" "--port=31080"
# 11:23:33 >
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
+MAPI = (monetdb) /var/tmp/mtest-18609/.s.monetdb.32538
+QUERY = copy 1 records into t_int from stdin USING DELIMITERS ',','\n','\"'
NULL AS '';
+ ""
+ERROR = !Failed to import table line 1 field 1 'int' expected in ''
+MAPI = (monetdb) /var/tmp/mtest-18609/.s.monetdb.32538
QUERY = copy 3 records into t_int from stdin USING DELIMITERS ',','\n','\"'
NULL AS '';
0
5.1
9
ERROR = !Failed to import table line 2 field 1 'int' expected in '5.1'
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
+MAPI = (monetdb) /var/tmp/mtest-18609/.s.monetdb.32538
QUERY = copy 5 records into t_int from stdin delimiters ',','\n' NULL as '';
1
nil
null
abc
-ERROR = !Failed to import table line 4 field 1 'int' expected in 'null'
+ERROR = !Failed to import table line 2 field 1 'int' expected in 'nil'
# 11:23:33 >
# 11:23:33 > "Done."
diff --git a/sql/test/copy/Tests/int_parse.stable.out
b/sql/test/copy/Tests/int_parse.stable.out
--- a/sql/test/copy/Tests/int_parse.stable.out
+++ b/sql/test/copy/Tests/int_parse.stable.out
@@ -24,14 +24,14 @@ Ready.
# 13:54:35 > mclient -lsql -umonetdb -Pmonetdb --host=eir --port=34075
# 13:54:35 >
-[ 1 ]
+#create table t_int(i int);
+#select * from t_int;
% sys.t_int # table_name
% i # name
% int # type
% 1 # length
-[ NULL ]
#delete from t_int;
-[ 1 ]
+[ 0 ]
#copy 2 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS
'';
#3.00
#-4.0000
diff --git a/sql/test/copy/Tests/int_parse_best.stable.out
b/sql/test/copy/Tests/int_parse_best.stable.out
--- a/sql/test/copy/Tests/int_parse_best.stable.out
+++ b/sql/test/copy/Tests/int_parse_best.stable.out
@@ -62,15 +62,20 @@ Ready.
#create table t_int(i int);
#copy 1 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS
'' best effort;
#""
-[ 1 ]
+[ 0 ]
#select * from t_int;
% sys.t_int # table_name
% i # name
% int # type
% 1 # length
-[ NULL ]
+#select * from sys.rejects;
+% .rejects, .rejects, .rejects, .rejects # table_name
+% rowid, fldid, message, input # name
+% bigint, int, clob, clob # type
+% 1, 1, 14, 1 # length
+[ 1, 1, "'int' expected", "\n" ]
#delete from t_int;
-[ 1 ]
+[ 0 ]
#copy 2 records into t_int from stdin USING DELIMITERS ',','\n','\"' NULL AS
'' best effort;
#3.00
#-4.0000
@@ -115,7 +120,7 @@ Ready.
#
#null
#abc
-[ 3 ]
+[ 2 ]
#select * from t_int;
% sys.t_int # table_name
% i # name
@@ -123,16 +128,16 @@ Ready.
% 1 # length
[ 1 ]
[ NULL ]
-[ NULL ]
#select * from sys.rejects;
% .rejects, .rejects, .rejects, .rejects # table_name
% rowid, fldid, message, input # name
% bigint, int, clob, clob # type
% 1, 1, 14, 5 # length
+[ 2, 1, "'int' expected", "nil\n" ]
[ 4, 1, "'int' expected", "null\n" ]
[ 5, 1, "'int' expected", "abc\n" ]
#delete from t_int;
-[ 3 ]
+[ 2 ]
#drop table t_int;
# 20:58:16 >
diff --git a/sql/test/copy/Tests/null_as_string_errors.sql
b/sql/test/copy/Tests/null_as_string_errors.sql
--- a/sql/test/copy/Tests/null_as_string_errors.sql
+++ b/sql/test/copy/Tests/null_as_string_errors.sql
@@ -6,13 +6,22 @@ create table null_as_string (i int, s st
copy 1 records into null_as_string from stdin delimiters ',','\n' NULL as '';
NULL,NULL,NULL
+select * from sys.rejects;
+call sys.clearrejects();
+
copy 1 records into null_as_string from stdin delimiters ',','\n' NULL as '';
NULL,zero,0
+select * from sys.rejects;
+call sys.clearrejects();
+
-- shouldn't fail because NULL as string is just fine
copy 1 records into null_as_string from stdin delimiters ',','\n' NULL as '';
1,NULL,1
+select * from sys.rejects;
+call sys.clearrejects();
+
copy 1 records into null_as_string from stdin delimiters ',','\n' NULL as '';
2,two,NULL
diff --git a/sql/test/copy/Tests/null_as_string_errors.stable.err
b/sql/test/copy/Tests/null_as_string_errors.stable.err
--- a/sql/test/copy/Tests/null_as_string_errors.stable.err
+++ b/sql/test/copy/Tests/null_as_string_errors.stable.err
@@ -80,30 +80,18 @@ stderr of test 'null_as_string_errors` i
# 21:02:57 > mclient -lsql -umonetdb -Pmonetdb --host=alf --port=38808
# 21:02:57 >
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
+MAPI = (monetdb) /var/tmp/mtest-21189/.s.monetdb.32700
QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n' NULL
as '';
NULL,NULL,NULL
ERROR = !Failed to import table line 1 field 1 'int' expected in 'NULL'
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
+MAPI = (monetdb) /var/tmp/mtest-21189/.s.monetdb.32700
QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n' NULL
as '';
NULL,zero,0
ERROR = !Failed to import table line 1 field 1 'int' expected in 'NULL'
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
+MAPI = (monetdb) /var/tmp/mtest-21189/.s.monetdb.32700
QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n' NULL
as '';
2,two,NULL
ERROR = !Failed to import table line 1 field 3 'decimal(5,2)' expected in
'NULL'
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
-QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n';
- ,,
-ERROR = !Failed to import table line 1 field 1 'int' expected in ''
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
-QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n';
- ,zero,0
-ERROR = !Failed to import table line 1 field 1 'int' expected in ''
-MAPI = (monetdb) /var/tmp/mtest-7656/.s.monetdb.37934
-QUERY = copy 1 records into null_as_string from stdin delimiters ',','\n';
- 2,two,
-ERROR = !Failed to import table line 1 field 3 'decimal(5,2)' expected in ''
# 08:37:31 >
# 08:37:31 > "Done."
diff --git a/sql/test/copy/Tests/null_as_string_errors.stable.out
b/sql/test/copy/Tests/null_as_string_errors.stable.out
--- a/sql/test/copy/Tests/null_as_string_errors.stable.out
+++ b/sql/test/copy/Tests/null_as_string_errors.stable.out
@@ -29,9 +29,27 @@ Ready.
# 08:37:31 >
#create table null_as_string (i int, s string, d decimal(5,2));
+#select * from sys.rejects;
+% .rejects, .rejects, .rejects, .rejects # table_name
+% rowid, fldid, message, input # name
+% bigint, int, clob, clob # type
+% 1, 1, 23, 15 # length
+[ 1, 1, "'int' expected", "NULL,NULL,NULL\n" ]
+[ 1, 3, "'decimal(5,2)' expected", "NULL,NULL,NULL\n" ]
+#select * from sys.rejects;
+% .rejects, .rejects, .rejects, .rejects # table_name
+% rowid, fldid, message, input # name
+% bigint, int, clob, clob # type
+% 1, 1, 14, 12 # length
+[ 1, 1, "'int' expected", "NULL,zero,0\n" ]
#copy 1 records into null_as_string from stdin delimiters ',','\n' NULL as '';
#1,NULL,1
[ 1 ]
+#select * from sys.rejects;
+% .rejects, .rejects, .rejects, .rejects # table_name
+% rowid, fldid, message, input # name
+% bigint, int, clob, clob # type
+% 1, 1, 0, 0 # length
#select * from null_as_string;
% sys.null_as_string, sys.null_as_string, sys.null_as_string # table_name
% i, s, d # name
@@ -44,18 +62,20 @@ Ready.
% .rejects, .rejects, .rejects, .rejects # table_name
% rowid, fldid, message, input # name
% bigint, int, clob, clob # type
-% 1, 1, 23, 15 # length
-[ 1, 1, "'int' expected", "NULL,NULL,NULL\n" ]
-[ 1, 3, "'decimal(5,2)' expected", "NULL,NULL,NULL\n" ]
-[ 1, 1, "'int' expected", "NULL,zero,0\n" ]
+% 1, 1, 23, 11 # length
[ 1, 3, "'decimal(5,2)' expected", "2,two,NULL\n" ]
+#copy 1 records into null_as_string from stdin delimiters ',','\n' best effort;
+#,,
+[ 0 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
