[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

[Debbie Steigner (JIRA)]

Debbie Steigner created TEIID-4033:
--------------------------------------

             Summary: 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)