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
    • 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.

            [TEIID-4893] Inconsistent behavior of SUBSTRING function

            Dmitrii Pogorelov added a comment -

            rhn-engineering-shawkins thx a lot for the quick fix!

            Dmitrii Pogorelov added a comment - rhn-engineering-shawkins thx a lot for the quick fix!

            Steven Hawkins added a comment -

            Added rewrite logic and updated the postgresql translator to match the behavior of the teiid substring function.

            Steven Hawkins added a comment - Added rewrite logic and updated the postgresql translator to match the behavior of the teiid substring function.

            Dmitrii Pogorelov added a comment -

            I'll wait for your official solution then. Thx!

            Dmitrii Pogorelov added a comment - I'll wait for your official solution then. Thx!

            Steven Hawkins added a comment -

            Yes, the current function modifier just translates into substring(val from x [, for y]). It's possible to update that into a case statement instead that handles the 0 and negative cases as well.

            Steven Hawkins added a comment - Yes, the current function modifier just translates into substring(val from x [, for y] ). It's possible to update that into a case statement instead that handles the 0 and negative cases as well.

            Dmitrii Pogorelov added a comment -

            rhn-engineering-shawkins that is SUBSTRING function modifier should be added for PostgreSQL translator to treat 0/negative indexes for SUBSTRING function, correct?

            Dmitrii Pogorelov added a comment - rhn-engineering-shawkins that is SUBSTRING function modifier should be added for PostgreSQL translator to treat 0/negative indexes for SUBSTRING function, correct?

            Steven Hawkins added a comment -

            > Or we should know that the SUBSTRING function works on teiid only by this way and that's all?

            The core problem is with using a non-positive index. Since substring and the associated source functions aren't standard, there isn't consistent handling. We do though assume that the Teiid behavior is the defacto correct behavior and translator logic when possible should compensate. In this case the postgres translator is not accounting for 0/negative indexes and should be updated to account for that.

            In general use 1 based indexing in string functions when possible.

            Steven Hawkins added a comment - > Or we should know that the SUBSTRING function works on teiid only by this way and that's all? The core problem is with using a non-positive index. Since substring and the associated source functions aren't standard, there isn't consistent handling. We do though assume that the Teiid behavior is the defacto correct behavior and translator logic when possible should compensate. In this case the postgres translator is not accounting for 0/negative indexes and should be updated to account for that. In general use 1 based indexing in string functions when possible.

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

                Created:
                Updated:
                Resolved: