You should join the list so that you get replies, and so I don’t have to moderate each of your questions through.
Well, I’ve learned something about DB2 SQL. I’d never heard of a database generating composite table aliases. We should fix the adapter so we generate the correct SQL for DB2. The fix is probably to make the result() method’s behavior depend on dialect.getDatabaseProduct(). Can you log a JIRA case please? Since you have access to DB2, could you also run Calcite’s whole test suite against DB2? Let’s find out what other issues are there. Add a “DB2" entry to enum CalciteAssert.DatabaseInstance and run with “-Dcalcite.test.db=DB2”. Julian > On May 11, 2016, at 1:50 PM, Magnus Pierre <[email protected]> wrote: > > 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 > > >
