private Command helpTest(String sql, String expected, boolean aliasGroups, boolean stripColumnAliases, QueryMetadataInterface metadata) throws TeiidComponentException, TeiidProcessingException { AliasGenerator visitor = new AliasGenerator(aliasGroups, stripColumnAliases); return helpTest(sql, expected, metadata, visitor); }
@Test public void testStripAliases() throws Exception { String sql = "select intkey as a, stringkey as b from BQT1.SmallA ORDER BY a, b"; //$NON-NLS-1$ String expected = "SELECT g_0.IntKey, g_0.StringKey FROM BQT1.SmallA AS g_0 ORDER BY g_0.IntKey, g_0.StringKey"; //$NON-NLS-1$ helpTest(sql, expected, true, true, RealMetadataFactory.exampleBQTCached()); }
@Test public void testInlineViewWithSubQuery() throws Exception { String sql = "select intnum from (select intnum from bqt1.smallb where intnum in (select intnum a from bqt1.smalla)) b"; //$NON-NLS-1$ String expected = "SELECT v_0.c_0 FROM (SELECT g_0.IntNum AS c_0 FROM BQT1.SmallB AS g_0 WHERE g_0.IntNum IN (SELECT g_1.IntNum FROM BQT1.SmallA AS g_1)) AS v_0"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testUnionOrderBy() throws Exception { String sql = "select e1, e2 as c_0 from pm1.g1 union all select 1, e1 from pm1.g2 order by e1"; //$NON-NLS-1$ String expected = "SELECT g_1.e1 AS c_0, convert(g_1.e2, string) AS c_1 FROM pm1.g1 AS g_1 UNION ALL SELECT '1' AS c_0, g_0.e1 AS c_1 FROM pm1.g2 AS g_0 ORDER BY c_0"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.example1Cached()); }
@Test public void testDuplicateShortElementName() throws Exception { String sql = "select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 order by pm1.g1.e1, pm1.g2.e1"; //$NON-NLS-1$ String expected = "SELECT g_0.e1 AS c_0, g_1.e1 AS c_1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 ORDER BY c_0, c_1"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.example1Cached()); }
@Test public void testUnrelatedOrderBy2() throws Exception { String sql = "SELECT b.IntKey FROM (select intkey, stringkey from BQT1.SmallA) a, (select intkey, stringkey from BQT1.SmallA) b ORDER BY a.StringKey || b.intKey"; //$NON-NLS-1$ String expected = "SELECT v_1.c_0 FROM (SELECT g_0.IntKey AS c_0, g_0.StringKey AS c_1 FROM BQT1.SmallA AS g_0) AS v_0, (SELECT g_1.IntKey AS c_0, g_1.StringKey AS c_1 FROM BQT1.SmallA AS g_1) AS v_1 ORDER BY concat(v_0.c_1, convert(v_1.c_0, string))"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testLongOrderByAlias() throws Exception { String sql = "select pm1.g1.e1 || pm1.g1.e2 as asfasdfadfasdfasdfadfasdfadsfasdfasdfasdfasdfasdfadfa, pm1.g1.e2 from pm1.g1 order by asfasdfadfasdfasdfadfasdfadsfasdfasdfasdfasdfasdfadfa"; //$NON-NLS-1$ String expected = "SELECT concat(g_0.e1, convert(g_0.e2, string)) AS c_0, g_0.e2 AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.example1Cached()); }
@Test public void testNestedInlineViewOrderBy() throws Exception { String sql = "select x from (select intnum x from (select intnum from bqt1.smallb) b order by x) y order by x"; //$NON-NLS-1$ String expected = "SELECT v_1.c_0 FROM (SELECT v_0.c_0 FROM (SELECT g_0.IntNum AS c_0 FROM BQT1.SmallB AS g_0) AS v_0) AS v_1 ORDER BY c_0"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testCorrelatedRefernce() throws Exception { String sql = "select intnum, stringnum from (select intnum, stringnum from bqt1.smallb) b where intnum in (select b.stringnum || b.intnum from (select intnum from bqt1.smalla) b) "; //$NON-NLS-1$ String expected = "SELECT v_0.c_0, v_0.c_1 FROM (SELECT g_0.IntNum AS c_0, g_0.StringNum AS c_1 FROM BQT1.SmallB AS g_0) AS v_0 WHERE convert(v_0.c_0, string) IN (SELECT concat(v_0.c_1, convert(v_1.c_0, string)) FROM (SELECT g_1.IntNum AS c_0 FROM BQT1.SmallA AS g_1) AS v_1)"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testUnionAliasing() throws Exception { String sql = "SELECT IntKey FROM BQT1.SmallA UNION ALL SELECT IntNum FROM BQT1.SmallA"; //$NON-NLS-1$ String expected = "SELECT BQT1.SmallA.IntKey AS c_0 FROM BQT1.SmallA UNION ALL SELECT BQT1.SmallA.IntNum AS c_0 FROM BQT1.SmallA"; //$NON-NLS-1$ helpTest(sql, expected, false, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testUnrelatedOrderBy() throws Exception { String sql = "SELECT b.IntKey FROM BQT1.SmallA a, BQT1.SmallA b ORDER BY a.StringKey"; //$NON-NLS-1$ String expected = "SELECT g_1.IntKey AS c_0 FROM BQT1.SmallA AS g_0, BQT1.SmallA AS g_1 ORDER BY g_0.StringKey"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testUnrelatedOrderBy1() throws Exception { String sql = "SELECT b.IntKey FROM (select intkey, stringkey from BQT1.SmallA) a, (select intkey, stringkey from BQT1.SmallA) b ORDER BY a.StringKey"; //$NON-NLS-1$ String expected = "SELECT v_1.c_0 FROM (SELECT g_0.IntKey AS c_0, g_0.StringKey AS c_1 FROM BQT1.SmallA AS g_0) AS v_0, (SELECT g_1.IntKey AS c_0, g_1.StringKey AS c_1 FROM BQT1.SmallA AS g_1) AS v_1 ORDER BY v_0.c_1"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testInlineViewWithOnClause() throws Exception { String sql = "select abcd.efg from (select intkey as efg from bqt1.smalla) abcd inner join (select intnum from bqt1.smallb) b on (b.intnum = abcd.efg)"; //$NON-NLS-1$ String expected = "SELECT v_0.c_0 FROM (SELECT g_0.IntKey AS c_0 FROM BQT1.SmallA AS g_0) AS v_0 INNER JOIN (SELECT g_1.IntNum AS c_0 FROM BQT1.SmallB AS g_1) AS v_1 ON v_1.c_0 = v_0.c_0"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testCorrelatedRefernce1() throws Exception { String sql = "select intnum, stringnum from bqt1.smallb where intnum in (select stringnum || b.intnum from (select intnum from bqt1.smalla) b) "; //$NON-NLS-1$ String expected = "SELECT g_0.IntNum, g_0.StringNum FROM BQT1.SmallB AS g_0 WHERE convert(g_0.IntNum, string) IN (SELECT concat(g_0.StringNum, convert(v_0.c_0, string)) FROM (SELECT g_1.IntNum AS c_0 FROM BQT1.SmallA AS g_1) AS v_0)"; //$NON-NLS-1$ helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); }
@Test public void testKeepAliases() throws Exception { String sql = "select g.intkey as a, g.stringkey as b from BQT1.SmallA g, BQT1.SmallB ORDER BY a, b"; //$NON-NLS-1$ String expected = "SELECT g.IntKey AS c_0, g.StringKey AS c_1 FROM BQT1.SmallA AS g, BQT1.SmallB AS g_1 ORDER BY c_0, c_1"; //$NON-NLS-1$ AliasGenerator av = new AliasGenerator(true, false); Map<String, String> aliasMap = new HashMap<String, String>(); aliasMap.put("g", "g"); av.setAliasMapping(aliasMap); helpTest(sql, expected, RealMetadataFactory.exampleBQTCached(), av); }
@Test(expected=TeiidRuntimeException.class) public void testKeepAliases1() throws Exception { String sql = "select g_1.intkey as a, g_1.stringkey as b from BQT1.SmallA g_1, BQT1.SmallB ORDER BY a, b"; //$NON-NLS-1$ String expected = "SELECT g.IntKey AS c_0, g.StringKey AS c_1 FROM BQT1.SmallA AS g ORDER BY c_0, c_1"; //$NON-NLS-1$ AliasGenerator av = new AliasGenerator(true, false); Map<String, String> aliasMap = new HashMap<String, String>(); aliasMap.put("g_1", "g_1"); av.setAliasMapping(aliasMap); helpTest(sql, expected, RealMetadataFactory.exampleBQTCached(), av); }
@Test public void testNestedCommonTables() throws Exception { String sql = "WITH CTE0 (e1, e2) AS /*+ no_inline */ (SELECT e1, e2 FROM pm1.g2) SELECT g_0.e1, g_0.e2 FROM CTE0 AS g_0 WHERE g_0.e1 = (WITH CTE1 (e1) AS /*+ no_inline */ (SELECT g_0.e1 FROM pm1.g1 AS g_1) SELECT g_2.e1 FROM CTE1 AS g_2)"; String expected = "WITH CTE0 (e1, e2) AS /*+ no_inline */ (SELECT g_0.e1, g_0.e2 FROM pm1.g2 AS g_0) SELECT g_1.e1, g_1.e2 FROM CTE0 AS g_1 WHERE g_1.e1 = (WITH CTE1 (e1) AS /*+ no_inline */ (SELECT g_1.e1 FROM pm1.g1 AS g_2) SELECT g_3.e1 AS c_0 FROM CTE1 AS g_3 LIMIT 2)"; //$NON-NLS-1$ AliasGenerator av = new AliasGenerator(true, false); helpTest(sql, expected, RealMetadataFactory.example1Cached(), av); }
@Test public void testStripAliases1() throws Exception { String sql = "select intkey as a, stringkey as b from BQT1.SmallA ORDER BY a, b"; //$NON-NLS-1$ String expected = "SELECT BQT1.SmallA.IntKey, BQT1.SmallA.StringKey FROM BQT1.SmallA ORDER BY BQT1.SmallA.IntKey, BQT1.SmallA.StringKey"; //$NON-NLS-1$ Command command = helpTest(sql, expected, false, true, RealMetadataFactory.exampleBQTCached()); LanguageBridgeFactory lbf = new LanguageBridgeFactory(RealMetadataFactory.exampleBQTCached()); org.teiid.language.Command c = lbf.translate(command); assertEquals("SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY SmallA.IntKey, SmallA.StringKey", c.toString()); }
@Test public void testInlineViewOrderBy() throws Exception { String sql = "select intnum from (select intnum from bqt1.smallb) b order by b.intnum"; //$NON-NLS-1$ String expected = "SELECT v_0.c_0 FROM (SELECT g_0.IntNum AS c_0 FROM BQT1.SmallB AS g_0) AS v_0 ORDER BY c_0"; //$NON-NLS-1$ Command command = helpTest(sql, expected, true, false, RealMetadataFactory.exampleBQTCached()); LanguageBridgeFactory lbf = new LanguageBridgeFactory(RealMetadataFactory.exampleBQTCached()); org.teiid.language.Command c = lbf.translate(command); assertEquals("SELECT v_0.c_0 FROM (SELECT g_0.IntNum AS c_0 FROM SmallB AS g_0) AS v_0 ORDER BY v_0.c_0", c.toString()); }
@Test public void testOrderBySymbolName() throws Exception { String sql = "select e1 from pm1.g1 order by e1"; //$NON-NLS-1$ String expected = "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0"; //$NON-NLS-1$ Query command = (Query)helpTest(sql, expected, true, false, RealMetadataFactory.example1Cached()); assertEquals(((Symbol)command.getOrderBy().getSortKeys().get(0)).getName(), "c_0"); //$NON-NLS-1$ assertEquals(((Symbol)command.getProjectedSymbols().get(0)).getShortName(), "c_0"); //$NON-NLS-1$ }