Hello Julian,
Not very convenient to not be part of the mailing list when asking questions,
good that the calcite-dev archives list responses. :)
But here is my claim:
The query:
SELECT *
FROM "GOSALESMR"."PRODUCT_SURVEY_RESULTS"
INNER JOIN "GOSALESMR"."PRODUCT_SURVEY_TOPIC" ON
"PRODUCT_SURVEY_RESULTS"."PRODUCT_TOPIC_CODE"
= "PRODUCT_SURVEY_TOPIC".”PRODUCT_TOPIC_CODE”
Using squirrel returns: (as well as with Drill)
Error: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703,
SQLERRMC=PRODUCT_SURVEY_RESULTS.PRODUCT_TOPIC_CODE, DRIVER=4.13.127
SQLState: 42703
ErrorCode: -206
Error: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098,
SQLERRMC=2;-206;42703;PRODUCT_SURVEY_RESULTS.PRODUCT_TOPIC_CODE, DRIVER=4.13.127
SQLState: 56098
ErrorCode: -727
In Drill:
org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The
JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM
"GOSALESMR"."PRODUCT_SURVEY_RESULTS" INNER JOIN
"GOSALESMR"."PRODUCT_SURVEY_TOPIC" ON
"PRODUCT_SURVEY_RESULTS"."PRODUCT_TOPIC_CODE" =
"PRODUCT_SURVEY_TOPIC"."PRODUCT_TOPIC_CODE" plugin gs_db2 Fragment 0:0 [Error
Id: 9e5c6edc-8572-4de2-b9ad-dce8a74589b1 on mpie-mbp.lan:31010]
(com.ibm.db2.jcc.am.SqlSyntaxErrorException) DB2 SQL Error: SQLCODE=-206,
SQLSTATE=42703, SQLERRMC=PRODUCT_SURVEY_RESULTS.PRODUCT_TOPIC_CODE,
DRIVER=4.13.127 com.ibm.db2.jcc.am.id.a():677 com.ibm.db2.jcc.am.id.a():60
com.ibm.db2.jcc.am.id.a():127 com.ibm.db2.jcc.am.no.c():2653
com.ibm.db2.jcc.am.no.d():2641 com.ibm.db2.jcc.am.no.a():2090
com.ibm.db2.jcc.t4.cb.h():141 com.ibm.db2.jcc.t4.cb.b():41
com.ibm.db2.jcc.t4.q.a():32 com.ibm.db2.jcc.t4.sb.i():135
com.ibm.db2.jcc.am.no.ib():2059 com.ibm.db2.jcc.am.no.a():3130
com.ibm.db2.jcc.am.no.a():688 com.ibm.db2.jcc.am.no.executeQuery():672
org.apache.commons.dbcp.DelegatingStatement.executeQuery():208
org.apache.commons.dbcp.DelegatingStatement.executeQuery():208
org.apache.drill.exec.store.jdbc.JdbcRecordReader.setup():177
org.apache.drill.exec.physical.impl.ScanBatch.():108
org.apache.drill.exec.physical.impl.ScanBatch.():136
org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():40
org.apache.drill.exec.store.jdbc.JdbcBatchCreator.getBatch():33
org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():148
org.apache.drill.exec.physical.impl.ImplCreator.getChildren():171
org.apache.drill.exec.physical.impl.ImplCreator.getRecordBatch():128
org.apache.drill.exec.physical.impl.ImplCreator.getChildren():171
org.apache.drill.exec.physical.impl.ImplCreator.getRootExec():101
org.apache.drill.exec.physical.impl.ImplCreator.getExec():79
org.apache.drill.exec.work.fragment.FragmentExecutor.run():231
org.apache.drill.common.SelfCleaningRunnable.run():38
java.util.concurrent.ThreadPoolExecutor.runWorker():1145
java.util.concurrent.ThreadPoolExecutor$Worker.run():615
java.lang.Thread.run():745
With aliases in Squirrel:
SELECT * FROM "GOSALESMR"."PRODUCT_SURVEY_RESULTS" AS "PRODUCT_SURVEY_RESULTS"
INNER JOIN "GOSALESMR"."PRODUCT_SURVEY_TOPIC" AS "PRODUCT_SURVEY_TOPIC"
ON "PRODUCT_SURVEY_RESULTS"."PRODUCT_TOPIC_CODE" =
"PRODUCT_SURVEY_TOPIC".”PRODUCT_TOPIC_CODE"
Returns all data with no errors or warnings.
SELECT * FROM "GOSALESMR"."PRODUCT_SURVEY_RESULTS" AS PRODUCT_SURVEY_RESULTS
INNER JOIN "GOSALESMR"."PRODUCT_SURVEY_TOPIC" AS PRODUCT_SURVEY_TOPIC
ON "PRODUCT_SURVEY_RESULTS"."PRODUCT_TOPIC_CODE" =
"PRODUCT_SURVEY_TOPIC"."PRODUCT_TOPIC_CODE"
Returns all data with no errors or warnings.
I distinctly remember this being the same behavior for both Netezza and
Teradata for instance. (even though it was a few years since I really did lots
of SQL on these systems). I can test Netezza but have no access to a TD box
currently.
Regards,
Magnus
—————————————
This is a response to:
—————————————
We originally generated an alias for every table in the FROM clause. But we
discovered that
the aliases were not necessary and made the query less easy to understand. For
example, the
query you mentioned earlier would become
SELECT *
FROM "GOSALESMR".”PRODUCT_SURVEY_RESULTS” AS T0
INNER JOIN "GOSALESMR"."PRODUCT_SURVEY_TOPIC” AS T1 ON T0."PRODUCT_TOPIC_CODE"
= T1.”PRODUCT_TOPIC_CODE"
So, we add aliases only if the table aliases that are automatically generated
by the target
system’s SQL validator are not unique.
This is the code that assigns aliases only if they are necessary:
public Result result(SqlNode node, Collection<Clause> clauses, RelNode rel) {
final String alias2 = SqlValidatorUtil.getAlias(node, -1);
final String alias3 = alias2 != null ? alias2 : "t";
final String alias4 =
SqlValidatorUtil.uniquify(
alias3, aliasSet, SqlValidatorUtil.EXPR_SUGGESTER);
final String alias5 = alias2 == null || !alias2.equals(alias4) ? alias4
: null;
return new Result(node, clauses, alias5,
Collections.singletonList(Pair.of(alias4, rel.getRowType())));
}
You assert that
SELECT *
FROM "GOSALESMR"."PRODUCT_SURVEY_RESULTS"
INNER JOIN "GOSALESMR"."PRODUCT_SURVEY_TOPIC" ON
"PRODUCT_SURVEY_RESULTS"."PRODUCT_TOPIC_CODE"
= "PRODUCT_SURVEY_TOPIC".”PRODUCT_TOPIC_CODE”
is not correct, but I disagree. I think it is fine. My understanding is that
the table "GOSALESMR".”PRODUCT_SURVEY_TOPIC”
will implicitly have an alias “PRODUCT_SURVEY_TOPIC” on all SQL systems,
including DB2.
If my understanding is wrong, then we do indeed have a problem. Does that query
fail to validate,
or give wrong results?
Julian
