SQL aliases and KNIME tables

KNIME Analytics Platform
#1

Dear all,

This is something that changed in KNIME 2.4.2: When I now use the database reader and a SQL statement such as "Select ID as Foo from Molecules" the output table has the original db table name as the column name (i.e. ID) and NOT the alias name (i.e. Foo).

This is was certainly not the case with KNIME 2.3.1. Is this a bug? Is there a way to overcome this in KNIME 2.4.2?

Thanks in advance,

George P.

#2

Hi George,

Are you quoting Foo?  I find both of the following forms work in 2.4.2:

SELECT ID AS "Foo" FROM Molecules

and

SELECT ID "Foo" FROM Molecules

 

Kind regards

James

#3

Hi James,

Thanks for the fast reply. I've tried all sorts of quotes including ticks, ``, and still nothing.

I'm using mysql and the latest mysql driver.

What db are you using?

 

Regards,

George

#4

It must have something to do with the new "Run SQL without configure" feature:

When I use an alias without unchecking this box I get this error:

ERROR Database Reader CODING PROBLEM DataSpec generated by configure does not match spec after execution.

ERROR Database Reader DataSpec generated by configure does not match spec after execution.

However the node executes fine - no aliased column name though :(

 

George

#5

I am mainly using Oracle - with the ojdbc14.jar driver...

#6

Interestingly, as James pointed out above, sql aliases do work with Oracle.

I would like to report this as a possible bug for KNIME 2.4.2 and MySQL.

Regards,

George

#7

Hi George, Hi James,

Thank you both for reporting these problems: a) I need to look into the alias issue reported under Oracle comparing KNIME 2.3.1 with 2.4.2, is this correct? b) The second problem, CODING ERROR, is a warning reported by the framework if the underlying spec generated by configure does not match the one created by execute. I assume, the underlying table has not changed from configure to execute, so I treat this as a bug of the actual node implementation.

Thanks, Thomas

#8

Hi Thomas,

This is the detailed report.

I am using the standard KNIME 2.4.2 on WinXP and the java-mysql driver 5.1.16. In the database reader, I use com.mysql.jdbc.Driver and a local mysql instance of chEMBL db v11.

The SQL command is simply:

SELECT canonical_smiles as George FROM compound_structures limit 100

I leave the box "Run SQL query without configure" checked.

The output is a column with the name "canonical_smiles", i.e. the alias was not passed on the output KNIME table as expected.

When I run the same SQL statement with the box "Run SQL query without configure" UN-checked the node returns the same data (canonical_smiles) but also throws an error:

ERROR Database Reader CODING PROBLEM DataSpec generated by configure does not match spec after execution.

ERROR Database Reader DataSpec generated by configure does not match spec after execution.

This error does not occur if I remove the alias part from the sql command:

SELECT canonical_smiles FROM compound_structures limit 100

Please note the aliases work fine with Oracle dbs.

Also note that aliases used to work in MySQL databases in KNIME 2.3.1.

Many thanks,

George

#9

After some more investigations, it turns out that the mysql driver implementation has changed in the meantime which is also reported here. At the same time, we decided to not wrap SQL queries into additional 'SELECT * FROM (...) table_alias' clauses which caused this problem to appear at first place. I see two workarounds a) you manually wrap the SQL statement or b) you append ?useOldAliasMetadataBehavior=true to the end of the database driver URL.

#10

That's great Thomas, many thanks.

It worked with ?useOldAliasMetadataBehavior=true appended at the end of the URL.

Regards,

George