Today has failed again and I have been able to do more tests. Here are my findings:
1.- Simplifying the query to (selecting only one column):
SELECT vn.id FROM bemp_demo1.desc_real vn LEFT JOIN bemp.categories c ON vn.category_id = c.cat_id LEFT JOIN bemp.var_filters f ON vn.filter_id = f.filter_id WHERE vn.id = 10000 limit 100;
gives a slightly different error but still fails:
java.lang.IllegalStateException: stream has already been operated upon or closed
at java.base/java.util.stream.AbstractPipeline.spliterator(AbstractPipeline.java:346)
at java.base/java.util.stream.ReferencePipeline.iterator(ReferencePipeline.java:143)
at io.crate.data.InMemoryBatchIterator.moveToStart(InMemoryBatchIterator.java:79)
at io.crate.data.join.JoinBatchIterator.moveToStart(JoinBatchIterator.java:70)
at io.crate.data.MappedForwardingBatchIterator.moveToStart(MappedForwardingBatchIterator.java:36)
at io.crate.data.join.RightJoinNLBatchIterator.tryAdvanceRight(RightJoinNLBatchIterator.java:126)
at io.crate.data.join.RightJoinNLBatchIterator.moveLeft(RightJoinNLBatchIterator.java:93)
at io.crate.data.join.RightJoinNLBatchIterator.moveNext(RightJoinNLBatchIterator.java:80)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.data.LimitingBatchIterator.moveNext(LimitingBatchIterator.java:57)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.action.sql.RowConsumerToResultReceiver.consumeIt(RowConsumerToResultReceiver.java:69)
at io.crate.action.sql.RowConsumerToResultReceiver.accept(RowConsumerToResultReceiver.java:52)
at io.crate.execution.engine.InterceptingRowConsumer.lambda$tryForwardResult$1(InterceptingRowConsumer.java:92)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
2.- Doing the query with a single join (the first one):
SELECT vn.id FROM bemp_demo1.desc_real vn LEFT JOIN bemp.categories c ON vn.category_id = c.cat_id WHERE vn.id = 10000 limit 100;
returns the data correctly (only one result as expected)
3.- Doing the query with a single join (the second one):
SELECT vn.id FROM bemp_demo1.desc_real vn LEFT JOIN bemp.var_filters f ON vn.filter_id = f.filter_id WHERE vn.id = 10000 limit 100;
returns the data correctly (only one result as expected)
4.- Doing the double join query selecting only one column from the second join table:
SELECT f.name FROM bemp_demo1.desc_real vn LEFT JOIN bemp.categories c ON vn.category_id = c.cat_id LEFT JOIN bemp.var_filters f ON vn.filter_id = f.filter_id WHERE vn.id = 1000 limit 100;
Also returns error:
java.lang.IllegalStateException: stream has already been operated upon or closed
at java.base/java.util.stream.AbstractPipeline.spliterator(AbstractPipeline.java:346)
at java.base/java.util.stream.ReferencePipeline.iterator(ReferencePipeline.java:143)
at io.crate.data.InMemoryBatchIterator.moveToStart(InMemoryBatchIterator.java:79)
at io.crate.data.join.JoinBatchIterator.moveToStart(JoinBatchIterator.java:70)
at io.crate.data.MappedForwardingBatchIterator.moveToStart(MappedForwardingBatchIterator.java:36)
at io.crate.data.join.RightJoinNLBatchIterator.tryAdvanceRight(RightJoinNLBatchIterator.java:126)
at io.crate.data.join.RightJoinNLBatchIterator.moveLeft(RightJoinNLBatchIterator.java:93)
at io.crate.data.join.RightJoinNLBatchIterator.moveNext(RightJoinNLBatchIterator.java:80)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.data.LimitingBatchIterator.moveNext(LimitingBatchIterator.java:57)
at io.crate.data.BatchIterators$1.moveNext(BatchIterators.java:119)
at io.crate.data.AsyncFlatMapBatchIterator.moveNext(AsyncFlatMapBatchIterator.java:67)
at io.crate.data.MappedForwardingBatchIterator.moveNext(MappedForwardingBatchIterator.java:41)
at io.crate.action.sql.RowConsumerToResultReceiver.consumeIt(RowConsumerToResultReceiver.java:69)
at io.crate.action.sql.RowConsumerToResultReceiver.accept(RowConsumerToResultReceiver.java:52)
at io.crate.execution.engine.InterceptingRowConsumer.lambda$tryForwardResult$1(InterceptingRowConsumer.java:92)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
This is the test data (hope is enough):
INSERT INTO bemp_demo1.desc_real (id, name, description, filter_id, category_id, isnumeric, unit, type, device_id, alarm_id, filter_execution_time, alarm_execution_time, gateway_config) VALUES(10000, 'ac_energy', 'Energy consumption from AC unit', 96, 107, 1, 'wh', 'electricity', NULL, [19,18], NULL, NULL, NULL);
INSERT INTO bemp.categories (cat_id, "name", description, parent_cat, client_id, cat_order, code) VALUES(107, 'Meteogune', '', NULL, 15, 1, 'b8Ksh2');
INSERT INTO bemp.var_filters (filter_id, client_id, name, description, content, code) VALUES(96, 15, 'Energía eléctrica 15min con máx.', '', '[]', 'kUm2Yt');
Here I attach the definitions of these new tables:
crate_tables_sql.txt (3.8 KB)
After restarting the node everything works again as expected.