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

Update statement doesn't work correctly for data sources in some cases

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Do
    • Icon: Blocker Blocker
    • None
    • 9.0.3
    • Query Engine
    • None
    • Hide

      1. Create two schemas in MySQL, say, "test" and "test_int".

      2. Create test tables (two "contacttests" and one "updatetest" tables) with test data in the schemas accordingly:

      CREATE TABLE test.contacttest (
  id int DEFAULT NULL,
  salutation varchar(4000) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test.contacttest (id, salutation) VALUES (1, 'Mr.');
INSERT INTO test.contacttest (id, salutation) VALUES (2, 'Mrs.');

CREATE TABLE test_int.contacttest (
  id int DEFAULT NULL,
  salutation varchar(4000) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test_int.contacttest (id, salutation) VALUES (1, 'Mr.');
INSERT INTO test_int.contacttest (id, salutation) VALUES (2, 'Mrs.');

CREATE TABLE test_int.updatetest (
  id int DEFAULT NULL,
  prefix varchar(4000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test_int.updatetest (id, prefix) VALUES (1, 'Monsieur');

      3. Add MySQL database configurations in standalone-teiid.xml:

                 <datasource jndi-name="java:/test_int" pool-name="test_int" enabled="true" use-java-context="true">
                    <connection-url>jdbc:mysql://localhost:3306/test_int?zeroDateTimeBehavior=convertToNull</connection-url>
                    <driver>mysql</driver>
                    <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                    </pool>
                    <security>
                        <user-name>root</user-name>
                        <password>xxxxxx</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>
                <datasource jndi-name="java:/test" pool-name="test" enabled="true" use-java-context="true">
                    <connection-url>jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull</connection-url>
                    <driver>mysql</driver>
                    <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                    </pool>
                    <security>
                        <user-name>root</user-name>
                        <password>xxxxxx</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>

      4. Add in test-vdb.xml java:/test_int and java:/test configured in previous step as data sources:

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

      5. Run the test queries mentioned in description field:

      UPDATE test_int.contacttest
SET test_int.contacttest.salutation = ( 
 select 
 test_int.updatetest.prefix
 from test_int.updatetest
 where test_int.updatetest.id = test_int.contacttest.id
 );

UPDATE test.contacttest
SET test.contacttest.salutation = ( 
 select 
 test_int.updatetest.prefix
 from test_int.updatetest
 where test_int.updatetest.id = test.contacttest.id
 );
      Show
      1. Create two schemas in MySQL, say, "test" and "test_int". 2. Create test tables (two "contacttests" and one "updatetest" tables) with test data in the schemas accordingly: CREATE TABLE test.contacttest ( id int DEFAULT NULL , salutation varchar (4000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test.contacttest (id, salutation) VALUES (1, 'Mr.' ); INSERT INTO test.contacttest (id, salutation) VALUES (2, 'Mrs.' ); CREATE TABLE test_int.contacttest ( id int DEFAULT NULL , salutation varchar (4000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test_int.contacttest (id, salutation) VALUES (1, 'Mr.' ); INSERT INTO test_int.contacttest (id, salutation) VALUES (2, 'Mrs.' ); CREATE TABLE test_int.updatetest ( id int DEFAULT NULL , prefix varchar (4000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test_int.updatetest (id, prefix ) VALUES (1, 'Monsieur' ); 3. Add MySQL database configurations in standalone-teiid.xml: <datasource jndi-name= "java:/test_int" pool-name= "test_int" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/test_int?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> root </user-name> <password> xxxxxx </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> <datasource jndi-name= "java:/test" pool-name= "test" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> root </user-name> <password> xxxxxx </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 4. Add in test-vdb.xml java:/test_int and java:/test configured in previous step as data sources: <model name= "test_int" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test_int" translator-name= "mylobs" connection-jndi-name= "java:/test_int" /> </model> <model name= "test" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test" translator-name= "mylobs" connection-jndi-name= "java:/test" /> </model> 5. Run the test queries mentioned in description field: UPDATE test_int.contacttest SET test_int.contacttest.salutation = ( select test_int.updatetest. prefix from test_int.updatetest where test_int.updatetest.id = test_int.contacttest.id ); UPDATE test.contacttest SET test.contacttest.salutation = ( select test_int.updatetest. prefix from test_int.updatetest where test_int.updatetest.id = test.contacttest.id );

      Running the following query:

      UPDATE test_int.contacttest
SET test_int.contacttest.salutation = ( 
 select 
 test_int.updatetest.prefix
 from test_int.updatetest
 where test_int.updatetest.id = test_int.contacttest.id
 );

      works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:

      UPDATE test.contacttest
SET test.contacttest.salutation = ( 
 select 
 test_int.updatetest.prefix
 from test_int.updatetest
 where test_int.updatetest.id = test.contacttest.id
 );

      will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:

      Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates. 
SQLState:  50000
ErrorCode: 30253

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

              Created:
              Updated:
              Resolved: