Type not serializable


#1

Hello everyone.
I have executed a sql statement on mapd,but an error occurred Exception occurred:type not serializable. The sql statement is

select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15and p_type like ‘%BRASS’
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = ‘EUROPE’
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = ‘EUROPE’)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;

And, i noticed that the subquery statement did not have the table part , but the keywords, p_partkey, in the subquery statement, was from the table part. So i want to know whether it is the reason of the error.

Besides, i also tested the sql statement on PostgreSQL,it passed, and returned 100 rows.

I tried inserting the table name part into subquery and tested it on mapd , it passed,but returned no rows(it should return 100 rows).


#2

This kind of correlated subqueries aren’t working yet


#3

Thank you for your answer.


#4

you can rewrite the query this way

with min_sc_part as (select p_partkey as min_p_partkey,min(ps_supplycost) min_sc 
                                 from part, partsupp, supplier, nation, region 
                                 where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' 
                                 group by p_partkey ) 
select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment 
from part, supplier, partsupp, nation, region, min_sc_part 
where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost = min_sc and p_partkey=min_p_partkey 
order by s_acctbal desc, n_name, s_name, p_partkey 
limit 100;

#5

Thank you so much.
And i also have a question about timestamp. i want to import data into mapd, and the format of time in the data file is like 2017/4/7 11:10:12. But after the operation, i found that the time was transformed into 1970-01-01 00:33:37. It is obvious that the time 1970-01-01 00:33:37 was transformed from the number 2017,rather than from the time 2017/4/7 11:10:12, and the reason of the error was the difference of / and -. So could you tell me how to deal with this problem?


#6

I’d use Wed with a regex to transform the file then use copy command on the new file, or a regex with stremimport utility (never tried). I’m not sure the / is the only problem.
the best would be modify the code of the core to expand the format compatibility of timestamp format, but this would take a lot of time :thinking:


#7

ok,i will use python scripts to modify the format of time.
Thank you for your answer.


#8

maybe sed is faster…the expression is like that

sed “s/([0-9]{4})/([0-9]{1,2})/([0-9]{1,2})/\1-\2-\3/g”

with python is more readable ([0-9]{4})/([0-9]{1,2})/([0-9]{1,2})