[teiid-issues] [JBoss JIRA] (TEIID-4427) Migration to Teiid 9.0.0 - Materialized views error: Expected integer, but was bigdecimal

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4427?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13290180#comment-13290180 ] 

Steven Hawkins commented on TEIID-4427:
---------------------------------------

Looking at this more, I see the same behavior in 8.11.x - that is we do validate the expected types, and I can get an exception during planning like:

org.teiid.api.exception.query.QueryValidatorException: The definition for pm1.G2 has the wrong type for column 1.  Expected integer, but was bigdecimal.
	at org.teiid.query.resolver.QueryResolver.validateProjectedSymbols(QueryResolver.java:487)
	at org.teiid.query.resolver.QueryResolver.validateProjectedSymbols(QueryResolver.java:471)
	at org.teiid.query.resolver.QueryResolver.resolveView(QueryResolver.java:440)
	at org.teiid.query.optimizer.relational.RelationalPlanner.resolveVirtualGroup(RelationalPlanner.java:1832)
	at org.teiid.query.optimizer.relational.RelationalPlanner.buildTree(RelationalPlanner.java:1226)
	at org.teiid.query.optimizer.relational.RelationalPlanner.createQueryPlan(RelationalPlanner.java:1121)

The ddl is roughly, which has the wider bigdecimal type in the materialization target:

CREATE FOREIGN TABLE G1(e1 bigdecimal, e2 varchar);
CREATE VIEW G2 OPTIONS (MATERIALIZED 'true', MATERIALIZED_TABLE 'pm1.G1') AS SELECT 1 as e1, e2 FROM pm1.G1;

With TEIID-3999 the exception will occur during deployment.  So it looks like we'll need more details on your scenario.

> Migration to Teiid 9.0.0 - Materialized views error: Expected integer, but was bigdecimal
> -----------------------------------------------------------------------------------------
>
>                 Key: TEIID-4427
>                 URL: https://issues.jboss.org/browse/TEIID-4427
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 9.0
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 9.1, 9.0.4
>
>
> After migrating to Teiid 9.0.0, we are facing issues with existing views materialized on Oracle server.
> when fetching an Oracle materialized view that contains a column using date function like : "Month" or "Year" or "TIMESTAMPDIFF", we are getting the error : Expected integer, but was bigdecimal.
> If we go back to Teiid 8.11.3, the same materialized views fetch without any problem.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)