[Squirrel-sql-develop] Incorrect SQL code generated for retrieving data from a specific table in the Content pane

[martin v. d. <mar...@mo...>]

Hi,

I noticed SQuirreL is generating an incorrect SQL SELECT statement when
selecting the Content panel for a specific table in the Objects tab.
This has been reported many times before, but it is not fixed
correctly/permanently for all RDBMS in 3.7 or latest snapshot 20160613_2107.
See: https://sourceforge.net/p/squirrel-sql/bugs/1183/
     https://sourceforge.net/p/squirrel-sql/bugs/1210/

SQuirreL is generating an SQL in the format of:
  select
tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage
from "sys"."columns" tbl
Which produces errors/warnings (in any RDBMS as default and null are
reserved keywords):

2016-06-23 16:10:30,352 [Thread-5] WARN 
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab 
- Failed to execute content SQL: select
tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage
from "sys"."columns" tbl
java.sql.SQLException: syntax error, unexpected DEFAULT in: "select
tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl."
    at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2602)
    at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2350)
    at
nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:507)
    at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:345)
    at
nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createResultSet(ContentsTab.java:357)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:252)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:131)
    at
net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.lang.Thread.run(Thread.java:745)
2016-06-23 16:10:30,353 [Thread-5] WARN 
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab 
- Failed to execute content SQL: select
tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage
from "sys"."columns" tbl
    at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2602)
    at
nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2350)
    at
nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:507)
    at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:345)
    at
nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createResultSet(ContentsTab.java:357)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:257)
    at
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:131)
    at
net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)
    at java.lang.Thread.run(Thread.java:745)


The used SQL select query generates wrong SQL for columns which are:
- reserved keywords (such as default, null, user),
- contain a character which has a special meaning in expressions (such
as -, +, /, *, _, %) or
- columns which exist multiple times but with different case such as:
create table test_column_names ("abc" int, "ABC" int, "aBc" int);
insert into test_column_names values (1, 11, 111);
insert into test_column_names values (2, 12, 112);
select * from test_column_names;
abc    ABC    aBc
1    11    111
2    12    112

For this table SQuirreL would generate the query:
 select tbl.abc, tbl.ABC, tbl.aBc from "sys"."test_column_names" tbl
which results in an incorrect output:
abc    abc    abc
1    1    1
2    2    2

Note: this output is for MonetDB (www.monetdb.org) which uses lowercase
names as default case.
Other RDBMS usually use UPPERcase as default case. For those the output
would be:
ABC    ABC    ABC
11    11    11
12    12    12


I suggest to correct the select query code generation (when viewing data
in Content pane) by:
- add double quote's to all column names (as is done for the schema name
and the table/view name)
- eliminate the alias: tbl  as it is not needed in this single table
query. It only makes the query text longer.

So for the above test_column_names example the generated select query
would become:
 select "abc", "ABC", "aBc" from "sys"."test_column_names"
which will give the correct output i.e. same as  select * from
"sys"."test_column_names" output.
This is standard SQL and should work for all SQL-compliant RDBMS.

Also it would not need to fallback to the select * from
"sys"."test_column_names" (so sending two queries to the RDBMS as is
added in 3.7)
and not write any errors/warnings to the log file.


Hope this helps in resolving the Content problem and improving SQuirreL.


Thanks for the great SQuirreL tool.

Ciao,
Martin van Dinther