Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4893

Inconsistent behavior of SUBSTRING function

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Critical Critical
    • 9.3, 9.1.5, 9.2.3
    • 9.0.3
    • JDBC Connector
    • None

    • teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)

    • Hide

      1. Create "status" table in PostgreSQL DB:

      CREATE TABLE public.status(
  VDBName varchar(50) not null,
  VDBVersion integer not null,
  SchemaName varchar(50) not null,
  Name varchar(256) not null,
  TargetSchemaName varchar(50),
  TargetName varchar(256) not null,
  Valid boolean not null,
  LoadState varchar(25) not null,
  Cardinality bigint,
  Updated timestamp not null,
  LoadNumber bigint not null,
  PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
);

      2. Create table for materialized values of the "test_substr" view, also add some "materialized" values manually:

      CREATE TABLE public.mat_test_substr(
  str varchar(4000)
);
INSERT INTO public.mat_test_substr values ('http://www.test.com');

      3. Create stage table for the "test_substr" view:

      CREATE TABLE public.mat_test_substr_staging(
  str varchar(4000)
);

      4. Add postgresql database configuration in standalone-teiid.xml:

                      <datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true">
                    <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url>
                    <driver-class>org.postgresql.Driver</driver-class>
                    <driver>org.postgresql</driver>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                        <prefill>false</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                        <user-name>postgres</user-name>
                        <password>xxxxxx</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>select 0</check-valid-connection-sql>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>

      5. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

          <model name="test_pg">
        <property name="importer.useFullSchemaName" value="false"/>
	<property name="importer.tableTypes" value="TABLE,VIEW"/>
	<property name="importer.importKeys" value="false"/>
        <source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/>
    </model>

      6. Configure the myPg translator in test-vdb.xml:

          <translator name="myPg" type="postgresql">
        <property name="SupportsNativeQueries" value="true"/>
    </translator>

      7. Configure in test-vdb.xml the following virtual view:

          <model visible = "true" type = "VIRTUAL" name = "views">
        <metadata type = "DDL"><![CDATA[
          CREATE virtual view test_substr OPTIONS (
		MATERIALIZED 'TRUE', 
		UPDATABLE 'TRUE',
		MATERIALIZED_TABLE 'test_pg.mat_test_substr', 
		"teiid_rel:MATVIEW_STATUS_TABLE" 'test_pg.status',
		"teiid_rel:MATERIALIZED_STAGE_TABLE" 'test_pg.mat_test_substr_staging',
		"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute test_pg.native(''truncate table mat_test_substr_staging'');',
               	"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" '
					execute test_pg.native(''ALTER TABLE mat_test_substr RENAME TO mat_test_substr_temp;ALTER TABLE mat_test_substr_staging RENAME TO mat_test_substr;ALTER TABLE mat_test_substr_temp RENAME TO mat_test_substr_staging;'');
					' 
	  ) as 
	     select 'http://www.test.com' as str
        ]]>
        </metadata>
    </model>

      8. Running the following query:

      select SUBSTRING(str, 0, 6) from views.test_substr;;

      will return:

      expr1
---------
http:

      though this query:

      select SUBSTRING(str, 0, 6) from views.test_substr OPTION NOCACHE;;

      will return:

      expr1
---------
http:/

      checking the function on different DBs I got the following results:
      a) MSSQL:

      select substring('http://www.test.com', 0, 6);

      returned this:

      http:

      b) Oracle:

      select substr('http://www.test.com', 0, 6) from dual;

      returned this:

      http:/

      so it means that actually the substring function initially works differently on different DBs but choosing only one behavior on teiid can lead to such inconsistent behavior between materialized and not materialized values shown in the ticket. I don't know if it's real bug but can it be improved anyhow? What do you think? Or we should know that the SUBSTRING function works on teiid only by this way and that's all?

      Show
      1. Create "status" table in PostgreSQL DB: CREATE TABLE public . status ( VDBName varchar (50) not null , VDBVersion integer not null , SchemaName varchar (50) not null , Name varchar (256) not null , TargetSchemaName varchar (50), TargetName varchar (256) not null , Valid boolean not null , LoadState varchar (25) not null , Cardinality bigint , Updated timestamp not null , LoadNumber bigint not null , PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name ) ); 2. Create table for materialized values of the "test_substr" view, also add some "materialized" values manually: CREATE TABLE public .mat_test_substr( str varchar (4000) ); INSERT INTO public .mat_test_substr values ( 'http://www.test.com' ); 3. Create stage table for the "test_substr" view: CREATE TABLE public .mat_test_substr_staging( str varchar (4000) ); 4. Add postgresql database configuration in standalone-teiid.xml: <datasource jndi-name= "java:/test_pg" pool-name= "test_pg" enabled= "true" use-java-context= "true" > <connection-url> jdbc:postgresql://localhost:5432/test?charSet=utf8 </connection-url> <driver-class> org.postgresql.Driver </driver-class> <driver> org.postgresql </driver> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> <prefill> false </prefill> <use-strict-min> false </use-strict-min> <flush-strategy> FailingConnectionOnly </flush-strategy> </pool> <security> <user-name> postgres </user-name> <password> xxxxxx </password> </security> <validation> <check-valid-connection-sql> select 0 </check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 5. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: <model name= "test_pg" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test_pg" translator-name= "myPg" connection-jndi-name= "java:/test_pg" /> </model> 6. Configure the myPg translator in test-vdb.xml: <translator name= "myPg" type= "postgresql" > <property name= "SupportsNativeQueries" value= "true" /> </translator> 7. Configure in test-vdb.xml the following virtual view: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ CREATE virtual view test_substr OPTIONS ( MATERIALIZED 'TRUE' , UPDATABLE 'TRUE' , MATERIALIZED_TABLE 'test_pg.mat_test_substr' , "teiid_rel:MATVIEW_STATUS_TABLE" 'test_pg.status' , "teiid_rel:MATERIALIZED_STAGE_TABLE" 'test_pg.mat_test_substr_staging' , "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute test_pg.native(' 'truncate table mat_test_substr_staging' ');' , "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" ' execute test_pg.native( ''ALTER TABLE mat_test_substr RENAME TO mat_test_substr_temp;ALTER TABLE mat_test_substr_staging RENAME TO mat_test_substr;ALTER TABLE mat_test_substr_temp RENAME TO mat_test_substr_staging;' '); ' ) as select 'http://www.test.com' as str ]]> </metadata> </model> 8. Running the following query: select SUBSTRING (str, 0, 6) from views.test_substr;; will return: expr1 --------- http: though this query: select SUBSTRING (str, 0, 6) from views.test_substr OPTION NOCACHE;; will return: expr1 --------- http:/ checking the function on different DBs I got the following results: a) MSSQL: select substring ( 'http://www.test.com' , 0, 6); returned this: http: b) Oracle: select substr( 'http://www.test.com' , 0, 6) from dual ; returned this: http:/ so it means that actually the substring function initially works differently on different DBs but choosing only one behavior on teiid can lead to such inconsistent behavior between materialized and not materialized values shown in the ticket. I don't know if it's real bug but can it be improved anyhow? What do you think? Or we should know that the SUBSTRING function works on teiid only by this way and that's all?

      There is inconsistent behavior of SUBSTRING function in teiid and data sources.

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: