Running alter-view on Teiid view is giving issues with it's column data types

[rajkota]

Hi,

I am working in Mark Addleman's team. I believe you might be having good idea about how we are using Teiid in our product .

I am getting below kind of errors from some of the views when I run 'alter view' on all our views.

   [stdout] (pool-11-thread-8) Caused by: org.teiid.jdbc.TeiidSQLException: TEIID30492 The definition for chorus_platform_chorus_metadata.tree_definition has the wrong type for column 5.  Expected boolean, but was string.

  [stdout] (pool-11-thread-8)           at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

                    at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

We are having view definition for tree_definition like below:

<models>

--------

<model name="chorus_platform_metadata_file">

                    <translator type="file" datasource="java:/CHORUS-PLATFORM-METADATA-FILE-DS" >

                              <property key="ExceptionIfFileNotFound" value="true" />

                    </translator>

          </model>

<model name="chorus_platform_chorus_metadata">

      <view name="tree_definition">

                              <columns>

                                        <column name="UI_AREA" type="varchar" />

                                        <column name="SECTION" type="varchar" />

                                        <column name="OBJECT_SEQ" type="varchar" />

                                        <column name="OBJECT_NAME" type="varchar" />

                                        <column name="LEVEL" type="boolean" />

                                        <column name="CONFIG" type="varchar" />

                                        <column name="LAZY" type="boolean" />

                              </columns>

                              <definition>

                                        SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL,

                                        CONFIG, LAZY

                                        FROM

                                        (call

                                        chorus_platform_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS

                                        f,

                                        TEXTTABLE(F.file

                                        COLUMNS

                                        UI_AREA string,

                                        SECTION string,

                                        OBJECT_SEQ string,

                                        OBJECT_NAME string,

                                        LEVEL boolean,

                                        CONFIG string,

                                        LAZY boolean

                                        HEADER 1) AS t

                              </definition>

                    </view>

   </model>

-----------

</models>

In above view, no need to have a boolean column in it's definition but TEIID is insisting to change it to boolean.

Here is the Alter View SQL string:

alter view chorus_platform_chorus_metadata.tree_definition as SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL, CONFIG, LAZY FROM (call chorus_platform_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS f, TEXTTABLE(F.file COLUMNS UI_AREA string, SECTION string, OBJECT_SEQ string, OBJECT_NAME string, LEVEL string, CONFIG string, LAZY boolean HEADER 1) AS t

In case if I change the data type of particular column for which TEIID is reporting issue as part of a view, sometimes surprisingly, it is giving the same issue again.

Querying to the view - 'select * from chorus_platform_chorus_metadata.tree_definition' - is working fine.

Can you please throw some thoughts here on the reasons for the above issue?

I modified TeiidEmbeddedPortfolio.java to include and run alter view statements by similuating same view, it worked fine.

I am attaching our log file here. Please take a look into it.

Thanks, Raj

[shawkins]

> In above view, no need to have a boolean column in it's definition but TEIID is insisting to change it to boolean.

I don't quite follow what you mean here.  That view has a boolean LEVEL column that is projected by a boolean value from the TEXTTABLE.  Do you not want one or both of those to not be boolean?

> Can you please throw some thoughts here on the reasons for the above issue?

The alter view shown above is projecting "LEVEL string" so if the view is defined with a boolean LEVEL column you would expect that alter to fail.  You are only changing the view definition, not the column types of the view.

Steve

[rajkota]

Hi Steve,

Please ignore above post.

We are getting issues ( like ‘The definition for ‘modelname.viewname’ has the wrong type for column 3.  Expected string, but was integer’) when we try to run the alter view  command.

Iteration 1:

Our view declaration is given below:

<view name="tree_definition_nolicense">

<columns>

<column name="UI_AREA" type="varchar"/>

<column name="SECTION" type="varchar"/>

<column name="OBJECT_SEQ" type="integer"/>

<column name="OBJECT_NAME" type="varchar"/>

<column name="LEVEL" type="integer"/>

<column name="CONFIG" type="varchar" />

<column name="LAZY" type="boolean"/>

</columns>

<definition>

SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL, CONFIG, LAZY

FROM

(call demodata_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS f,

TEXTTABLE(F.file

COLUMNS

UI_AREA string,

SECTION string,

OBJECT_SEQ integer,

OBJECT_NAME string,

  LEVEL integer,

CONFIG string,

LAZY boolean

HEADER 1) AS t

</definition>

</view>

Alter View command on above view defintion:

alter view demodata_ tree_definition_nolicense as

SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL, CONFIG, LAZY FROM (call

demodata_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS f, TEXTTABLE(F.file COLUMNS UI_AREA string,

SECTION string, OBJECT_SEQ integer, OBJECT_NAME string, LEVEL integer, CONFIG string, LAZY boolean HEADER 1) AS t

Received below error message on running above alter view command:

'TEIID30492 The definition for demodata_chorus_metadata.tree_definition_nolicense has the wrong type for column 3.  Expected string, but was integer' for request zGCcKfjSb+RI.0.  Exception type org.teiid.api.exception.query.QueryValidatorException thrown from org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:330). Enable more detailed logging to see the entire stacktrace.

With Column #3:

<column name="OBJECT_SEQ" type="integer"/>

Corrected the data type of column #3 in the same view and recycled JBoss:

Iteration 2:

Updated View:

<view name="tree_definition_nolicense">

<columns>

<column name="UI_AREA" type="varchar"/>

<column name="SECTION" type="varchar"/>

<column name="OBJECT_SEQ" type=" varchar"/>

<column name="OBJECT_NAME" type="varchar"/>

<column name="LEVEL" type="integer"/>

<column name="CONFIG" type="varchar" />

<column name="LAZY" type="boolean"/>

</columns>

<definition>

SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL, CONFIG, LAZY

FROM

(call demodata_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS f,

TEXTTABLE(F.file

COLUMNS

UI_AREA string,

SECTION string,

OBJECT_SEQ string,

OBJECT_NAME string,

LEVEL integer,

CONFIG string,

LAZY boolean

HEADER 1) AS t

</definition>

</view>

Received Error message on alter view:

TEIID30492 The definition for demodata_chorus_metadata.tree_definition_nolicense has the wrong type for column 5.  Expected boolean, but was integer.

With Column #5:

<column name="LEVEL" type="integer"/>

Corrected the data type of column #5 in the same view and recycled JBoss:

Iteration 3:

Updated View:

<view name="tree_definition_nolicense">

<columns>

<column name="UI_AREA" type="varchar"/>

<column name="SECTION" type="varchar"/>

<column name="OBJECT_SEQ" type=" varchar"/>

<column name="OBJECT_NAME" type="varchar"/>

<column name="LEVEL" type="boolean"/>

<column name="CONFIG" type="varchar" />

<column name="LAZY" type="boolean"/>

</columns>

<definition>

SELECT UI_AREA, SECTION, OBJECT_SEQ, OBJECT_NAME, LEVEL, CONFIG, LAZY

FROM

(call demodata_metadata_file.getTextFiles('metadata-treedefinition.txt'))AS f,

TEXTTABLE(F.file

COLUMNS

UI_AREA string,

SECTION string,

OBJECT_SEQ string,

OBJECT_NAME string,

LEVEL boolean,

CONFIG string,

LAZY boolean

HEADER 1) AS t

</definition>

</view>

Received error message on alter view:

TEIID30492 The definition for demodata_chorus_metadata.tree_definition_nolicense has the wrong type for column 6.  Expected boolean, but was string

With Column #6:

<column name="CONFIG" type="varchar" />

Corrected the data type of column #6 in the same view and recycled JBoss:

….

Like above, I had to set the data type as Boolean for all the columns in view in order to make alter view workable, which is something that is not behaving in expected manner.

And querying to above query is working fine as expected, giving issue with only running alter view.

Could you please help me here to over come from the above mentioned issue?

[shawkins]

Just focus on the first iteration.  You are showing a view/alter that both use an interger type for column 3.  Before considering the alter, you need to verify that you have indeed defined the view appropriately - there may be an issue in you custom xml metadata layer.  Get the metadata from SYS.COLUMNS for the OBJECT_SEQ column and verify that it is an integer.  If using a release before 8.0 you should also issue a query against the view to validate that it resolves correctly (Teiid 8.0 and later proactively validate view definitions on metadata load) and proceduces the results you expect. 

You can also verify that Teiid is determining the correct projected types for the view definition by issuing the view defintion query directly and inspecting the ResultSetMetadata.  If either the view or the direct query show the type String for the OBJECT_SEQ column, that would help narrow in.

Steve