[teiid-issues] [JBoss JIRA] (TEIID-4033) View model - named attributes in mapping statement are not automatically mapped to the corresponding column in the table definition

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4033?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-4033.
-----------------------------------
    Resolution: Rejected


When defining a view it is expected that the the declared columns and the columns of the query expression match positionally.  You can confirm this behavior with other databases, such as h2, postgresql, etc.

> View model - named attributes in mapping statement are not automatically mapped to the corresponding column in the table definition
> -----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-4033
>                 URL: https://issues.jboss.org/browse/TEIID-4033
>             Project: Teiid
>          Issue Type: Bug
>          Components: VDB
>    Affects Versions: 8.7.2.6_2
>         Environment: Dynamic VDB:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <vdb name="dollar" version="1">
> <description/>
> <property name="validationDateTime" value="Tue Mar 08 07:37:23 CST 2016"/>
> <property name="validationVersion" value="8.7.3"/>
> <model name="SOURCE_MODEL">
> <source connection-jndi-name="Debbie2" name="Debbie2" translator-name="postgresql"/>
> <metadata type="DDL"><![CDATA[
> CREATE FOREIGN TABLE mytable (
> 	id integer NOT NULL OPTIONS(NAMEINSOURCE '"id"', NATIVE_TYPE 'int4', CASE_SENSITIVE 'FALSE', FIXED_LENGTH 'TRUE', SEARCHABLE 'ALL_EXCEPT_LIKE'),
> 	name string(40) OPTIONS(NAMEINSOURCE '"name"', NATIVE_TYPE 'text'),
> 	age integer OPTIONS(NAMEINSOURCE '"age"', NATIVE_TYPE 'int4', CASE_SENSITIVE 'FALSE', FIXED_LENGTH 'TRUE', SEARCHABLE 'ALL_EXCEPT_LIKE'),
> 	address string(200) OPTIONS(NAMEINSOURCE '"address"', NATIVE_TYPE 'text'),
> 	CONSTRAINT pk_mytableid PRIMARY KEY(id)
> ) OPTIONS(NAMEINSOURCE '"public"."mytable"', CARDINALITY '3')
> ]]></metadata>
> </model>
> <model name="VIEW_MODEL" type="VIRTUAL">
> <metadata type="DDL"><![CDATA[
> CREATE VIEW MY_VIEW (
> 	ID integer NOT NULL,
> 	NAME string(40),
> 	ADDRESS string(200),
> 	CONSTRAINT FKI_MY_VIEW PRIMARY KEY(ID)
> ) 
> AS
> 	SELECT "ID" as ID, "ADDRESS" as ADDRESS, "NAME" as NAME FROM SOURCE_MODEL.mytable;
> ]]></metadata>
> </model>
> </vdb>
>            Reporter: Debbie Steigner
>            Assignee: Barry LaFond
>
> Unexpected sequential dependency between virtual table column order and mapping column sequence.
> The TEIID statement to create a virtual table goes like:
> CREATE VIEW TargetModelViewName (
> ColumnA …							1
> ColumnB …							2
> ColumnC …) …							3
> AS SELECT 
> SourceModel.SourceTable.ColX AS ColumnA,			1
> SourceModel.SourceTable.ColY AS ColumnB,			2
> SourceModel.SourceTable.ColZ AS ColumnC;			3
> In any 4th+ generation query language a positional dependency is not expected, and therefore the following statement should also work, but fails in the TEIID 
> CREATE VIEW TargetModelViewName (
> ColumnA …							1
> ColumnB …							2
> ColumnC …) …							3
> AS SELECT 
> SourceModel.SourceTable.ColX AS ColumnA,			1
> SourceModel.SourceTable.ColZ AS ColumnC,			3
> SourceModel.SourceTable.ColY AS ColumnB;			2



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