/** * Tests that resolving fails if there is no implicit conversion between the * type of the expression of the SubquerySetCriteria and the type of the * projected symbol of the subquery. */ @Test public void testSubQueryINClauseNoConversionFails(){ helpResolveException("select e1 from pm1.g1 where e1 in (select e2 from pm4.g1)"); }
@Test public void testLeadOffset() { //must be integer String sql = "SELECT LEAD(e1, 'a') over (order by e2) FROM pm1.g1"; helpResolveException(sql); }
@Test public void testVariableDeclarationAfterStatement() throws Exception{ String procedure = "CREATE VIRTUAL PROCEDURE "; //$NON-NLS-1$ procedure = procedure + "BEGIN\n"; //$NON-NLS-1$ procedure = procedure + "select * from pm1.g1 where pm1.g1.e1 = VARIABLES.X;\n"; //$NON-NLS-1$ procedure = procedure + "DECLARE string VARIABLES.X = 1;\n"; //$NON-NLS-1$ procedure = procedure + "END\n"; //$NON-NLS-1$ helpResolveException(procedure, "TEIID31118 Element \"VARIABLES.X\" is not defined by any relevant group."); //$NON-NLS-1$ }
@Test public void testNtileArg() { //must be integer String sql = "SELECT ntile('a') over (order by e2) FROM pm1.g1"; helpResolveException(sql); }
@Test public void testNthValueArgType() { //needs integer String sql = "SELECT Nth_Value('a','a') over (order by e2) FROM pm1.g1"; helpResolveException(sql); }
@Test public void testSubQueryINClauseTooManyColumns(){ String sql = "select e1 from pm1.g1 where e1 in (select e1, e2 from pm4.g1)"; //$NON-NLS-1$ //test this.helpResolveException(sql); }
/** * 'e5' is in X and Y */ @Test public void testNestedCorrelatedSubqueries4(){ String sql = "select X.e2 from pm4.g2 Y, pm4.g2 X where X.e2 = all (select e2 from pm4.g1 where e5 = e1)"; //$NON-NLS-1$ helpResolveException(sql, metadata, "TEIID31117 Element \"e5\" is ambiguous and should be qualified, at a single scope it exists in [pm4.g2 AS Y, pm4.g2 AS X]"); //$NON-NLS-1$ }
/** * the group g1 is not known to the order by clause of a union */ @Test public void testUnionOrderByFail() { helpResolveException("SELECT pm1.g1.e1 FROM pm1.g1 UNION SELECT pm1.g2.e1 FROM pm1.g2 ORDER BY g1.e1", "TEIID30086 ORDER BY expression 'g1.e1' cannot be used with a set query."); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testRowsUpdatedInProcedure(){ String sql = "CREATE VIRTUAL PROCEDURE " //$NON-NLS-1$ + "BEGIN " //$NON-NLS-1$ +"SELECT ROWS_UPDATED; " //$NON-NLS-1$ +"end "; //$NON-NLS-1$ helpResolveException(sql, metadata, "TEIID31118 Element \"ROWS_UPDATED\" is not defined by any relevant group."); //$NON-NLS-1$ }
/** * We could check to see if the expressions are evaluatable to a constant, but that seems unnecessary */ @Test public void testLookupWithoutConstant() throws Exception{ String sql = "SELECT lookup('pm1.g1', convert('e3', float), 'e2', e2) FROM pm1.g1"; //$NON-NLS-1$ helpResolveException(sql, metadata, "TEIID30095 The first three arguments for the LOOKUP function must be specified as constants."); //$NON-NLS-1$ }
/** * This is not correct behavior, but it is acceptable to prevent other issues */ @Test public void testTableAliasWithPeriodAmbiguous() throws Exception { String sql = "select \"pm1.g2\".*, pm1.g2.* from pm1.g1 as \"pm1.g2\", pm1.g2"; helpResolveException(sql); }
@Test public void testLookupFunctionFailBadElement() { String sql = "SELECT lookup('nosuch', 'elementhere', 'e2', e2) AS x FROM pm1.g1"; //$NON-NLS-1$ helpResolveException(sql); }
@Test public void testInsertWithQueryFails() { String sql = "Insert into pm1.g1 select 1, 2, 3, 4"; //$NON-NLS-1$ helpResolveException(sql, "TEIID30128 Cannot convert insert query expression projected symbol '3' of type java.lang.Integer to insert column 'pm1.g1.e3' of type java.lang.Boolean"); //$NON-NLS-1$ }
@Test public void testLeadDefault() { //must have same type String sql = "SELECT LEAD(e2, 1, 'a') over (order by e2) FROM pm1.g1"; helpResolveException(sql); }
@Test public void testNthValueArg() { //needs two args String sql = "SELECT Nth_Value('a') over (order by e2) FROM pm1.g1"; helpResolveException(sql); }
/** * should be the same as exec with too many params */ @Test public void testCallableStatementTooManyParameters() throws Exception { String sql = "{call pm4.spTest9(?, ?)}"; //$NON-NLS-1$ TestResolver.helpResolveException(sql, RealMetadataFactory.exampleBQTCached(), "TEIID31113 1 extra positional parameter(s) passed to pm4.spTest9."); //$NON-NLS-1$ }
@Test public void testFailPartiallyQualifiedGroup3() { metadata = RealMetadataFactory.example3(); helpResolveException("SELECT e1 FROM g2"); //$NON-NLS-1$ }
@Test public void testFailPartiallyQualifiedElement1() { metadata = RealMetadataFactory.example3(); helpResolveException("SELECT cat3.g1.e1 FROM pm1.cat1.cat2.cat3.g1, pm2.cat3.g1"); //$NON-NLS-1$ }
@Test public void testFailPartiallyQualifiedElement2() { metadata = RealMetadataFactory.example3(); helpResolveException("SELECT g1.e1 FROM pm1.cat1.cat2.cat3.g1, pm2.cat3.g1"); //$NON-NLS-1$ }
@Test public void testExecWithDuplicateNames() { MetadataStore metadataStore = new MetadataStore(); Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore); ColumnSet<Procedure> rs2 = RealMetadataFactory.createResultSet("rs2", new String[] { "in", "e2" }, new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ ProcedureParameter rs2p2 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ Procedure sq2 = RealMetadataFactory.createStoredProcedure("sq2", pm1, Arrays.asList(rs2p2)); //$NON-NLS-1$ sq2.setResultSet(rs2); QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "example1"); helpResolveException("select * from pm1.sq2", metadata, "TEIID30114 Cannot access procedure pm1.sq2 using table semantics since the parameter and result set column names are not all unique."); //$NON-NLS-1$ //$NON-NLS-2$ }