SubQueryPreProcessor { public static QueryTreeNode optimize(QueryTreeNode qtn) throws QueryException { qtn = findComparisonsSubQueryToJoinNode(qtn); return qtn; }  static QueryTreeNode optimize(QueryTreeNode qtn);  }
@Test public void test_等于子查询() { TableNode table1 = new TableNode("TABLE1"); table1.query("ID = (SELECT ID FROM TABLE2)"); table1.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(table1); Assert.assertTrue(qn instanceof JoinNode); Assert.assertEquals("1", qn.getWhereFilter().toString()); }
@Test public void test_In子查询() { TableNode table1 = new TableNode("TABLE1"); table1.query("ID IN (SELECT ID FROM TABLE2)"); table1.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(table1); Assert.assertTrue(qn instanceof JoinNode); Assert.assertEquals("1", qn.getWhereFilter().toString()); }
@Test public void test_NotIn子查询() { TableNode table1 = new TableNode("TABLE1"); table1.query("ID NOT IN (SELECT ID FROM TABLE2)"); table1.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(table1); Assert.assertTrue(qn instanceof JoinNode); Assert.assertEquals("TABLE2.ID IS NULL", qn.getWhereFilter().toString()); }
@Test public void test_等于子查询_存在OR查询_报错() { TableNode table1 = new TableNode("TABLE1"); table1.query("ID = (SELECT ID FROM TABLE2 WHERE NAME = 'HELLO') OR NAME = 3"); table1.build(); try { SubQueryPreProcessor.optimize(table1); Assert.fail(); } catch (Exception e) { } }
@Test public void test_等于子查询_存在多条件() { TableNode table1 = new TableNode("TABLE1"); table1.query("ID = (SELECT ID FROM TABLE2 WHERE NAME = 'HELLO' AND SCHOOL = 'HELLO' ) AND NAME = 3 AND SCHOOL IN ('A','B')"); table1.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(table1); Assert.assertTrue(qn instanceof JoinNode); Assert.assertEquals(null, table1.getWhereFilter()); }
@Test public void test_等于子查询_外部是个join节点() { TableNode table1 = new TableNode("TABLE1"); TableNode table2 = new TableNode("TABLE2"); JoinNode join = table1.join(table2, "ID", "ID"); join.query("TABLE1.ID = (SELECT ID FROM TABLE3 WHERE NAME = 'HELLO' AND SCHOOL = 'HELLO' ) AND TABLE1.NAME = 3 AND TABLE1.SCHOOL IN ('A','B')"); join.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(join); Assert.assertTrue(qn instanceof JoinNode); Assert.assertTrue(((JoinNode) qn).getLeftNode() instanceof JoinNode); Assert.assertEquals(null, join.getWhereFilter()); }
@Test public void test_等于IN组合子查询_复杂条件_外部是个join节点() { TableNode table1 = new TableNode("TABLE1"); TableNode table2 = new TableNode("TABLE2"); JoinNode join = table1.join(table2, "ID", "ID"); join.query("TABLE1.ID = (SELECT ID FROM TABLE3 WHERE NAME = 'HELLO' AND SCHOOL = 'HELLO' ) AND TABLE1.NAME = 3 AND TABLE1.SCHOOL IN (SELECT SCHOOL FROM TABLE4)"); join.build(); QueryTreeNode qn = SubQueryPreProcessor.optimize(join); Assert.assertTrue(qn instanceof JoinNode); Assert.assertTrue(((JoinNode) qn).getLeftNode() instanceof JoinNode); Assert.assertTrue(((JoinNode) ((JoinNode) qn).getLeftNode()).getLeftNode() instanceof JoinNode); Assert.assertEquals(null, join.getWhereFilter()); }