Details
Description
The easiest way to reproduce this is by creating a table in a database that contains reserved words as column names.
PostgreSQL Example:
CREATE TABLE reserverdwords
(
id integer,
"select" character varying(20),
"From" character varying(20)
)
- First thing to note is that you must quote reserved words to use it as a column name, so case is important.
Next, to demonstrate one problem that this can introduce, create a new Transformation.
Add the table input step to the canvas. Connect it to the database that you created the reservedwords table in.
Press the "Get SQL select statement" button.
Choose the reservedwords table.
When prompted, answer YES to include field names.
Preview the query, notice it fails to execute.
The problem is that the reserved words are quoted, but the case of the From field is not correct.
Other scenarios exist that relate to this problem as well. One is an inconsistency when generating SQL for a table output step from source fields that have reserved words.
Create a Transformation with a Data Grid and a TableOutputStep (connect to postgres).
Add fields in the data grid for FROM and FROM2.
in the Table output Step, generate the SQL for the fields coming from the data grid.
Notice that the quoted field "from" is used and FROM2 is left unquoted and all caps.
It is strange that when quoting a field, the case is modified. However, the field that wasn't quoted, the case was left intact. There isn't an error at this point, but the behavior is odd.