Understand. Just joined… :) Just a comment on your reasoning around aliases.
Aliases are the safest, and most practical route for all databases, and leaves no room to the database to do crazy interpretations of what you try to achieve. I fully understand that aliases are not for everyone, but being system generated SQL that will go against databases and not humans, the use of aliases will save query length (provided the aliases are shorter than table names), allow for returning duplications of column names since the name of the column will be alias.column-name, it will avoid cross join behavior commonly seen in for instance Teradata when mixing aliases and non-aliases. Not to mention that it could simplify the processing done by Calcite, and hopefully also remove a set of error conditions for specific databases (like DB2) where things are more strict than in others. Aliases has been required to be supported if I remember correctly since SQL-1999 so it should be a fairly safe feature to use for almost all JDBC databases. I have never seen automatic generation of aliases for table names as described. (But I must admit that for the last 15 or so years I’ve used aliases at all cases so that does not tell much) Another aspect is that unqualified table references need to be evaluated by the dbms against the current namespace (schema) that the user uses. If I have a table in my currently used namespace (schema) with the same name as the ”automatic alias” in a query, how will the database know if I am referring to my table in the current namespace which is unqualified, or an in query alias for the table in the from? Of course these are just my observations and I have complete trust in you guys knowing far,far more than me about database internals and how a database interprets the SQL language. I’m just a user even though I used to write some dbms engine code (object relational things) back in the days. I’ll open a JIRA late today, and will do the testing requested. Regards, Magnus -------- 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 > > >
