Parsing errors in Designer "Error in parsing of sql - Encountered " "new" "new "" at line 4, column 75"

[rareddy]

Usecase: simple view with insert/updates, where insert is going to insert values into multiple tables. For example view table is joining A, B on id column.

While working on a case what we observed if select transformation is designed with implicit join like

select * from A, B where A.id = B.id

then in the Insert tab, I use

FOR EACH ROW
BEGIN ATOMIC
  INSERT INTO A (id, x, y) values ("NEW".id, "NEW".x, "NEW".y);
  INSERT INTO B (id, x, y) values ("NEW".id, "NEW".x, "NEW".y);
END

I would see an invisible validation error like

Description Resource Path Location Type

Error in parsing of sql - Encountered " "new" "new "" at line 4, column 75.
Was expecting one of:
  "char" ...
  "cast" ...
  "convert" ...
  "any" ...
  "array_agg" ...
  "case" ...
  "exists" ...
  "false" ...
  "hour" ...
  "insert" ...
  "left" .

..

However, when I design the SELECT transformation like

select * from A INNER JOIN B ON A.id = B.id

the error goes way. I think Teiid does not like implicit relations when working with insert/updates, works well with JOIN based semantics.

I am hoping there is an explanation, and this will serve as guide for somebody to not waste their time chasing this issue.

[shawkins]

I'm not sure what you are seeing.  Teiid doesn't care what the view body is when an instead of trigger is used.  It's not clear that the error message even matches the trigger sql.

[rareddy]

Yes, the error message never matched the line, nor there was Line #4 in the query. In fact the query was validated fine in the insert and select tab.

based on your comment it must some other lingering cause, which seems to go away with updates to select transformation.