Examples of NestLoopPlanNode

Examples of org.voltdb.plannodes.NestLoopPlanNode

674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
    public void testBasicOuterJoin() {
        // select * with ON clause should return all columns from all tables
        AbstractPlanNode pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C");
        AbstractPlanNode n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        AbstractPlanNode c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R1".equalsIgnoreCase(((SeqScanPlanNode) c0).getTargetTableName()));
        AbstractPlanNode c1 = nl.getChild(1);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R2".equalsIgnoreCase(((SeqScanPlanNode) c1).getTargetTableName()));

        pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C AND R1.A = 5");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R1".equalsIgnoreCase(((SeqScanPlanNode) c0).getTargetTableName()));
        c1 = nl.getChild(1);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R2".equalsIgnoreCase(((SeqScanPlanNode) c1).getTargetTableName()));
    }
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
    public void testRightOuterJoin() {
        // select * FROM R1 RIGHT JOIN R2 ON R1.C = R2.C => select * FROM R2 LEFT JOIN R1 ON R1.C = R2.C
        AbstractPlanNode pn = compile("select * FROM R1 RIGHT JOIN R2 ON R1.C = R2.C");
        AbstractPlanNode n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        AbstractPlanNode c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R2".equalsIgnoreCase(((SeqScanPlanNode) c0).getTargetTableName()));
        AbstractPlanNode c1 = nl.getChild(1);
        assertTrue(c1 instanceof SeqScanPlanNode);
        assertTrue("R1".equalsIgnoreCase(((SeqScanPlanNode) c1).getTargetTableName()));

        // Same but with distributed table
        pn = compile("select * FROM P1 RIGHT JOIN R2 ON P1.C = R2.C");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue("R2".equalsIgnoreCase(((SeqScanPlanNode) c0).getTargetTableName()));
        c1 = nl.getChild(1);
        assertTrue(c1 instanceof ReceivePlanNode);

    }
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
    public void testSeqScanOuterJoinCondition() {
        // R1.C = R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
        AbstractPlanNode pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C");
        AbstractPlanNode n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(ExpressionType.COMPARE_EQUAL, nl.getJoinPredicate().getExpressionType());
        assertNull(nl.getWherePredicate());
        assertEquals(2, nl.getChildCount());
        SeqScanPlanNode c0 = (SeqScanPlanNode) nl.getChild(0);
        assertNull(c0.getPredicate());
        SeqScanPlanNode c1 = (SeqScanPlanNode) nl.getChild(1);
        assertNull(c1.getPredicate());

        // R1.C = R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
        // R1.A > 0 Outer Join Expr stays at the the NLJ as pre-join predicate
        // R2.A < 0 Inner Join Expr is pushed down to the inner SeqScan node
        pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C AND R1.A > 0 AND R2.A < 0");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertNotNull(nl.getPreJoinPredicate());
        AbstractExpression p = nl.getPreJoinPredicate();
        assertEquals(ExpressionType.COMPARE_GREATERTHAN, p.getExpressionType());
        assertNotNull(nl.getJoinPredicate());
        p = nl.getJoinPredicate();
        assertEquals(ExpressionType.COMPARE_EQUAL, p.getExpressionType());
        assertNull(nl.getWherePredicate());
        assertEquals(2, nl.getChildCount());
        c0 = (SeqScanPlanNode) nl.getChild(0);
        assertNull(c0.getPredicate());
        c1 = (SeqScanPlanNode) nl.getChild(1);
        assertNotNull(c1.getPredicate());
        p = c1.getPredicate();
        assertEquals(ExpressionType.COMPARE_LESSTHAN, p.getExpressionType());

        // R1.C = R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
        // (R1.A > 0 OR R2.A < 0) Inner-Outer join Expr stays at the NLJ as Join predicate
        pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C AND (R1.A > 0 OR R2.A < 0)");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        p = nl.getJoinPredicate();
        assertEquals(ExpressionType.CONJUNCTION_AND, p.getExpressionType());
        if (ExpressionType.CONJUNCTION_OR == p.getLeft().getExpressionType()) {
            assertEquals(ExpressionType.CONJUNCTION_OR, p.getLeft().getExpressionType());
        } else {
            assertEquals(ExpressionType.CONJUNCTION_OR, p.getRight().getExpressionType());
        }
        assertNull(nl.getWherePredicate());
        assertEquals(2, nl.getChildCount());
        c0 = (SeqScanPlanNode) nl.getChild(0);
        assertNull(c0.getPredicate());
        c1 = (SeqScanPlanNode) nl.getChild(1);
        assertNull(c1.getPredicate());

        // R1.C = R2.C Inner-Outer join Expr stays at the NLJ as Join predicate
        // R1.A > 0 Outer Where Expr is pushed down to the outer SeqScan node
        // R2.A IS NULL Inner Where Expr stays at the the NLJ as post join (where) predicate
        // (R1.C > R2.C OR R2.C IS NULL) Inner-Outer Where stays at the the NLJ as post join (where) predicate
        pn = compile("select * FROM R1 LEFT JOIN R2 ON R1.C = R2.C WHERE R1.A > 0 AND R2.A IS NULL AND (R1.C > R2.C OR R2.C IS NULL)");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertNotNull(nl.getJoinPredicate());
        p = nl.getJoinPredicate();
        assertEquals(ExpressionType.COMPARE_EQUAL, p.getExpressionType());
        AbstractExpression w = nl.getWherePredicate();
        assertNotNull(w);
        assertEquals(ExpressionType.CONJUNCTION_AND, w.getExpressionType());
        assertEquals(ExpressionType.OPERATOR_IS_NULL, w.getRight().getExpressionType());
        assertEquals(ExpressionType.CONJUNCTION_OR, w.getLeft().getExpressionType());
        assertEquals(2, nl.getChildCount());
        c0 = (SeqScanPlanNode) nl.getChild(0);
        assertEquals(ExpressionType.COMPARE_GREATERTHAN, c0.getPredicate().getExpressionType());
        c1 = (SeqScanPlanNode) nl.getChild(1);
        assertNull(c1.getPredicate());

        // R3.A = R2.A Inner-Outer index join Expr. NLJ predicate.
        // R3.A > 3 Index Outer where expr pushed down to IndexScanPlanNode
        // R3.C < 0 non-index Outer where expr pushed down to IndexScanPlanNode as a predicate
        pn = compile("select * FROM R3 LEFT JOIN R2 ON R3.A = R2.A WHERE R3.A > 3 AND R3.C < 0");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        AbstractPlanNode outerScan = n.getChild(0);
        assertTrue(outerScan instanceof IndexScanPlanNode);
        IndexScanPlanNode indexScan = (IndexScanPlanNode) outerScan;
        assertEquals(IndexLookupType.GT, indexScan.getLookupType());
        assertNotNull(indexScan.getPredicate());
        assertEquals(ExpressionType.COMPARE_LESSTHAN, indexScan.getPredicate().getExpressionType());

        // R3.C = R2.C Inner-Outer non-index join Expr. NLJ predicate.
        // R3.A > 3 Index null rejecting inner where expr pushed down to IndexScanPlanNode
        // NLJ is simplified to be INNER
        pn = compile("select * FROM R2 LEFT JOIN R3 ON R3.C = R2.C WHERE R3.A > 3");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.INNER, nl.getJoinType());
        outerScan = n.getChild(1);
        assertTrue(outerScan instanceof IndexScanPlanNode);
        indexScan = (IndexScanPlanNode) outerScan;
        assertEquals(IndexLookupType.GT, indexScan.getLookupType());
        assertNull(indexScan.getPredicate());
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

860
861
862
863
864
865
866
867
868
869
870
871
872
873
        // Distributed Inner table
        pn = compile("select * FROM R2 LEFT JOIN P1 ON P1.C = R2.C");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(2, nl.getChildCount());
        assertTrue(nl.getChild(0) instanceof SeqScanPlanNode);
        assertTrue(nl.getChild(1) instanceof ReceivePlanNode);

        // Distributed Inner and Outer table joined on the partition column
        lpn = compileToFragments("select * FROM P1 LEFT JOIN P4 ON P1.A = P4.A");
        assertEquals(2, lpn.size());
        n = lpn.get(1).getChild(0);
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
        // R3 is indexed but it's the outer table and the join expression must stay at the NLJ
        // so index can't be used
        AbstractPlanNode pn = compile("select * FROM R3 LEFT JOIN R2 ON R3.A = R2.C");
        AbstractPlanNode n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        AbstractPlanNode c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue(((SeqScanPlanNode) c0).getTargetTableName().equalsIgnoreCase("R3"));
        AbstractPlanNode c1 = nl.getChild(1);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue(((SeqScanPlanNode) c1).getTargetTableName().equalsIgnoreCase("R2"));

        // R3 is indexed but it's the outer table so index can't be used
        pn = compile("select * FROM R2 RIGHT JOIN R3 ON R3.A = R2.C");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        nl = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nl.getJoinType());
        assertEquals(2, nl.getChildCount());
        c0 = nl.getChild(0);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue(((SeqScanPlanNode) c0).getTargetTableName().equalsIgnoreCase("R3"));
        c1 = nl.getChild(1);
        assertTrue(c0 instanceof SeqScanPlanNode);
        assertTrue(((SeqScanPlanNode) c1).getTargetTableName().equalsIgnoreCase("R2"));

        pn = compile("select * FROM R2 LEFT JOIN R3 ON R2.C = R3.A");
        n = pn.getChild(0).getChild(0);
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
        // R3.C != 0 Non-index Inner Join Expression is pushed down to the inner IndexScan node as a predicate
        // R2.A < 6 Outer Join Expr is a pre-join predicate for NLJ
        pn = compile("select * FROM R2 LEFT JOIN R3 ON R3.C = R2.A AND R3.A > 0 AND R3.C != 0 AND R2.A < 6");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nlj = (NestLoopPlanNode) n;
        assertEquals(JoinType.LEFT, nlj.getJoinType());
        assertNotNull(nlj.getPreJoinPredicate());
        p = nlj.getPreJoinPredicate();
        assertEquals(ExpressionType.COMPARE_LESSTHAN, p.getExpressionType());
        assertNotNull(nlj.getJoinPredicate());
        assertEquals(ExpressionType.COMPARE_EQUAL, nlj.getJoinPredicate().getExpressionType());
        assertNull(nlj.getWherePredicate());
        c1 = n.getChild(0);
        assertTrue(c1 instanceof SeqScanPlanNode);
        assertNull(((SeqScanPlanNode)c1).getPredicate());
        AbstractPlanNode c2 = n.getChild(1);
        assertTrue(c2 instanceof IndexScanPlanNode);
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
        // Distributed Inner table
        pn = compile("select * FROM R2 LEFT JOIN P1 ON P1.C = R2.C");
        n = pn.getChild(0).getChild(0);
        assertTrue(n instanceof NestLoopPlanNode);
        NestLoopPlanNode nl = (NestLoopPlanNode) n;
        assertEquals(2, nl.getChildCount());
        assertTrue(nl.getChild(0) instanceof SeqScanPlanNode);
        assertTrue(nl.getChild(1) instanceof ReceivePlanNode);

        // Distributed Inner and Outer table joined on the partition column
        lpn = compileToFragments("select * FROM P1 LEFT JOIN P4 ON P1.A = P4.A");
        assertEquals(2, lpn.size());
        n = lpn.get(1).getChild(0);
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
                        || ((SeqScanPlanNode)n.getChild(0)).getTargetTableName().endsWith(Integer.toString(ii + 1)));
                assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii))
                        || ((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii + 1)));
                break;
            } else {
                NestLoopPlanNode node = (NestLoopPlanNode)n;
                assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii)));
                n = node.getChild(0);
            }
        }

        pn = compileSPWithJoinOrder("select * FROM T1, T2, T3, T4, T5, T6, T7", "T1,T2,T3,T4,T5,T6,T7");
        n = pn.getChild(0).getChild(0);
        for (int ii = 7; ii > 0; ii--) {
            if (ii == 2) {
                assertTrue(((SeqScanPlanNode)n.getChild(0)).getTargetTableName().endsWith(Integer.toString(ii))
                        || ((SeqScanPlanNode)n.getChild(0)).getTargetTableName().endsWith(Integer.toString(ii - 1)));
                assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii))
                        || ((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii - 1)));
                break;
            } else {
                NestLoopPlanNode node = (NestLoopPlanNode)n;
                assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii)));
                n = node.getChild(0);
            }
        }

        pn = compileSPWithJoinOrder("select * from T1, T2 where A=B", "  T1  ,  T2  ");
        /* DEBUG */ System.out.println(pn.toExplainPlanString());
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
                        || ((SeqScanPlanNode)n.getChild(0)).getTargetTableName().endsWith(Integer.toString(ii - 1)));
                assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii))
                        || ((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii - 1)));
                break;
            } else {
                NestLoopPlanNode node = (NestLoopPlanNode)n;
                if (mySets.contains(Integer.valueOf(ii))) {
                    assertTrue(((SeqScanPlanNode)n.getChild(0)).getTargetTableName().endsWith(Integer.toString(ii)));
                    n = node.getChild(1);
                } else {
                    // starts from T6 on child 1 because of the invalid join
                    assertTrue(((SeqScanPlanNode)n.getChild(1)).getTargetTableName().endsWith(Integer.toString(ii)));
                    n = node.getChild(0);
                }
            }
        }
    }
View Full Code Here

Examples of org.voltdb.plannodes.NestLoopPlanNode

569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
            canHaveNLIJ = false;
        }

        AbstractJoinPlanNode ajNode = null;
        if (canHaveNLJ) {
            NestLoopPlanNode nljNode = new NestLoopPlanNode();
            // get all the clauses that join the applicable two tables
            ArrayList<AbstractExpression> joinClauses = innerAccessPath.joinExprs;
            if (innerPlan instanceof IndexScanPlanNode) {
                // InnerPlan is an IndexScan. In this case the inner and inner-outer
                // non-index join expressions (if any) are in the otherExpr. The former should stay as
                // an IndexScanPlan predicate and the latter stay at the NLJ node as a join predicate
                List<AbstractExpression> innerExpr = filterSingleTVEExpressions(innerAccessPath.otherExprs);
                joinClauses.addAll(innerAccessPath.otherExprs);
                AbstractExpression indexScanPredicate = ExpressionUtil.combine(innerExpr);
                ((IndexScanPlanNode)innerPlan).setPredicate(indexScanPredicate);
            }
            nljNode.setJoinPredicate(ExpressionUtil.combine(joinClauses));

            // combine the tails plan graph with the new head node
            nljNode.addAndLinkChild(outerPlan);

            // If successful in the special case, the NLJ plan must be modified to cause the
            // partitioned inner data to be sent to the coordinator prior to the join.
            // This is done by adding send and receive plan nodes between the NLJ and its
            // right child node.
            if (needInnerSendReceive) {
                // This trick only works once per plan.
                if (outerPlan.hasAnyNodeOfType(PlanNodeType.RECEIVE) || innerPlan.hasAnyNodeOfType(PlanNodeType.RECEIVE)) {
                    return null;
                }
                innerPlan = addSendReceivePair(innerPlan);
            }

            nljNode.addAndLinkChild(innerPlan);
            ajNode = nljNode;
        }
        else if (canHaveNLIJ) {
            NestLoopIndexPlanNode nlijNode = new NestLoopIndexPlanNode();
View Full Code Here
TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.