[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

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: Feature Request 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