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

UPDATE command with non-pushdown functions leads to TEIID30253 error

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.12.x
    • Fix Version/s: 9.1
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA
      teiid-9.0.3 on WildFly Full 9.0.2.Final

    • Steps to Reproduce:
      Hide

      1. Create textagg_test_2 table in a MySQL database:

      CREATE TABLE `textagg_test_2` (
        `id` varchar(4000) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      2. Configure a datasource to the MySQL database in standalone-teiid.xml.
      3. Configure a VDB in deployments folder containing a model with source to the MySQL datasource.

      4. Run the following queries:

      update test.textagg_test_2 set id =(select uuid());;
      
      update test.textagg_test_2 set id =(select now());;
      
      update test.textagg_test_2 set id =(select rand());;
      
      Show
      1. Create textagg_test_2 table in a MySQL database: CREATE TABLE `textagg_test_2` ( `id` varchar (4000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2. Configure a datasource to the MySQL database in standalone-teiid.xml. 3. Configure a VDB in deployments folder containing a model with source to the MySQL datasource. 4. Run the following queries: update test.textagg_test_2 set id =( select uuid());; update test.textagg_test_2 set id =( select now());; update test.textagg_test_2 set id =( select rand());;

      Description

      Running the following queries:

      update test.textagg_test_2 set id =(select uuid());;
      

      or

      update test.textagg_test_2 set id =(select rand());;
      

      leads to the following errors:

      TEIID30253 Source UPDATE or DELETE command "UPDATE test.textagg_test_2 SET id = (SELECT uuid() 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. 
      

      and

      TEIID30253 Source UPDATE or DELETE command "UPDATE test.textagg_test_2 SET id = convert((SELECT rand() LIMIT 2), string)" 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. 
      

      though the following query:

      update test.textagg_test_2 set id =(select now());;
      

      works without errors.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: