[TEIID-5519] Update and Delete with WHERE clause not working for delimited flat file(header true)

Type: Bug
Resolution: Can't Do
Priority: Major
Fix Version/s: None
Affects Version/s: 9.1.3
Component/s: None
Labels:
None

Steps to Reproduce:
Hide

1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';

where 'flatfileDeli' is connection name.
It throws error:
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports'

It works fine and update records as expected
Show
1.create header true file on ftp say TRANSACTION_DTL_new.txt 2.File contain data given below: acct_number,product_type,prod_desc 123,Sports,sports related prod 124,Beauty,beauty related prod 3.Now execute UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type= 'sports' where TRANSACTION_DTL_new.acct_number= '123' ; where 'flatfileDeli' is connection name. It throws error: TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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. 4.But if execute query - UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type= 'sports' It works fine and update records as expected

Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
Query -

UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';

Error Message -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

Manoj Majumdar (Inactive) created issue - 2018/10/25 7:59 AM

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:00 AM

Description

Original: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:java}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

New: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:java}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:00 AM

Description

Original: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:java}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

New: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:01 AM

Steps to Reproduce

Original: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:java}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:java}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

New: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:sql}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:sql}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:02 AM

Steps to Reproduce

Original: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:sql}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:sql}
update flatfileDeli.TRANSACTION_DTL_new set TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

New: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:03 AM

Description

Original: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.Acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

New: Hi Team,

I have observed that in case of header true delimited flatfiles for FTP/SFTP, we are unable to execute update/delete query with where clause. We are able to execute simple queries(without where clause) on Header True files succesfully. Adding to the same, we are able to execute queries succesfully in case of Delimited Header false flat files and Fixed width Flat files.

Given below is the query along with error message.
*Query* -
{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';
{code}

*Error Message* -
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

Manoj Majumdar (Inactive) made changes - 2018/10/25 8:04 AM

Steps to Reproduce

Original: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET Acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

New: 1.create header true file on ftp say TRANSACTION_DTL_new.txt

2.File contain data given below:
acct_number,product_type,prod_desc
123,Sports,sports related prod
124,Beauty,beauty related prod

3.Now execute

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports' where TRANSACTION_DTL_new.acct_number='123';
{code}

where 'flatfileDeli' is connection name.
*It throws error:*
TEIID30253 Source UPDATE or DELETE command "UPDATE flatfileDeli.TRANSACTION_DTL_new SET acct_number = ? WHERE flatfileDeli.TRANSACTION_DTL_new.product_type = ?" 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.

4.But if execute query -

{code:sql}
UPDATE flatfileDeli.TRANSACTION_DTL_new SET TRANSACTION_DTL_new.product_type='sports'
{code}

It works fine and update records as expected

Steven Hawkins made changes - 2018/10/26 11:18 AM

Resolution		New: Incomplete Description [ 4 ]
Status	Original: Open [ 1 ]	New: Resolved [ 5 ]

Assignee:: Steven Hawkins

Reporter:: Manoj Majumdar (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Created:: 2018/10/25 7:59 AM

Updated:: 2018/10/26 11:18 AM

Resolved:: 2018/10/26 11:18 AM

Details

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates