Got error while using join function


#1

Hello,

I got an error while running the join function, after about 1 min, it shows 2 error message:
Thrift: Tue Nov 28 13:29:32 2017 TSocket::write_partial() send() <Host: localhost Port: 9091>Broken pipe
Thrift: Tue Nov 28 13:29:54 2017 TSocket::write_partial() send() <Host: localhost Port: 9091>Broken pipe

and the FATAL file which at /var/lib/mapd/data/mapd_log/ shows follow…

Log file created at: 2017/11/28 13:29:42
Running on machine: ubuntu
Log line format: [IWEF]mmdd hh:mm:ss.uuuuuu threadid file:line] msg
F1128 13:29:42.499785 39279 RelAlgExecutor.cpp:1765] Check failed: filter

so what can i do with this error?

Thanks


#2

Hi

There not much you can do with that error it is causing an issue in the relational algebra execution.

If you can share the ddl and the query you are running we can work on getting a work around in short term by rewriting the query. And in the longer term identify and fix the root cause of the check.

Regards


#3

Thanks for your reply,

So, where should I find the ddl for you?

and the SQL query as below:
SELECT * FROM
(
SELECT
a.aci, a.aip, a.aipw, b.bci, b.bip, b.bipw
FROM
(
SELECT
test1.ci AS aci, test1.srcip AS aip, count(test1.srcip) AS aipw
FROM
test1
GROUP BY ci, srcip
) AS a
INNER JOIN
(
SELECT
test2.ci AS bci, test2.srcip AS bip, count(test2.srcip) AS bipw
FROM
test2
GROUP BY ci, srcip
) AS b
ON (a.aip = b.bip)
WHERE
a.aip = b.bip
AND a.aci <> b.bci
) AS ab
where
ab.aip = ab.bip
AND ab.aci <> ab.bci
ORDER BY ab.aipw DESC, ab.aci, ab.bci
LIMIT 10

I am parsing some Netflow with this join function SQL query with each table has 175931 records. I hope this can help and get your suggestions.

Thank you very mush.


#4

About the ddl
Connect to mapd server with mapdql utility (topically you have to connect to host where you have installed mapd database)

And use \d table_name for each table you are using in your query


#5

thanks for your instruction,
so here is the ddl of the tables,

thanks a lot

\d netflow1
CREATE TABLE netflow1 (
ci TEXT ENCODING DICT(32),
startDate DATE,
startTime TIME,
duration SMALLINT,
proto TEXT ENCODING DICT(32),
srcIP TEXT ENCODING DICT(32),
srcPort SMALLINT,
decIP TEXT ENCODING DICT(32),
decPort SMALLINT,
packet SMALLINT,
bytes SMALLINT,
flows SMALLINT)
\d netflow2
CREATE TABLE netflow2 (
ci TEXT ENCODING DICT(32),
startDate DATE,
startTime TIME,
duration SMALLINT,
proto TEXT ENCODING DICT(32),
srcIP TEXT ENCODING DICT(32),
srcPort SMALLINT,
decIP TEXT ENCODING DICT(32),
decPort SMALLINT,
packet SMALLINT,
bytes SMALLINT,
flows SMALLINT)

candido dessanti mapd@discoursemail.com 於 2017年11月29日 週三 上午2:50寫道:


#6

i would remove the a.aip = b.bip from where clause, because i think is useless (the condition is already on join clause) or remove the join clause and move the join condition on where clause

despite the explain calcite of query is the same something is going wrong

join condition and filter on where clause

select count() from (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as a, (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as b where (a.data_emissione = b.data_emissione) and a.DATA_scadenza <> b.DATA_scadenza;
Execution time: 147 ms, Total time: 147 ms
join condition on join clause and filtering on where

select count() from (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as a join (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as b on (a.data_emissione = b.data_emissione) where a.DATA_scadenza <> b.DATA_scadenza;
Execution time: 148 ms, Total time: 149 ms

join condition on join and where clause, filtering on where clause.
select count() from (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as a join (select DATA_scadenza,data_emissione,count() from fatture group by 1,2) as b on (a.data_emissione = b.data_emissione) where a.DATA_scadenza <> b.DATA_scadenza and a.data_emissione = b.data_emissione;

Thrift: Wed Nov 29 07:30:03 2017 TSocket::write_partial() send() <Host: localhost Port: 9091>Broken pipe
server crashes.

hopes this will help