Re: The correct source for foodmart test dataset (DB2 testing)

[Julian Hyde]

Using OSBI/foodmart-data is a smart idea. AFAIK it's the same data set.

Can you post the output from the tests to, say, pastebin?

By the way, just checking that you ran with -Dcalcite.test.db=DB2? If
you didn't it will have silently run against hsqldb.

On Fri, May 13, 2016 at 6:22 AM, Magnus Pierre  wrote:
> Hello,
> I’ve done the necessary code changes, added the db2 jar to the repository and 
> added a dependency to it in pom.xml.
> Since I could not find the actual sources for mondrian foodmart used to build 
> the test vm’s for Calcite
> https://github.com/vlsi/calcite-test-dataset 
> , I went ahead and downloaded 
> and used https://github.com/OSBI/foodmart-data 
>  instead
> Is there a difference in data / tables between the Calcite ”test data set” 
> and the mondrian foodmart data set?
>
> I can run the tests (my clone of the main), but I don’t understand the 
> output. (Three of the tests crashes and I see interesting output that I don’t 
> understand)
>
> Regards,
> Magnus
>
>

Re: Planner rule

[Julian Hyde]

If you are changing the semantics of the query I’m not sure that a planner rule 
is the right route. Planner rules need to preserve semantics.

However, you will be working with the relational algebra. Probably adding a 
Filter to the root RelNode of your query.

You might find the algebra builder[1] useful. Or you might not.

The cleanest way to add a WHERE clause is to define a view with the WHERE 
clause, write your query on that view, and the WHERE clause will be added 
during view expansion. Then you don’t need to change Calcite code at all.

Julian

[1] https://calcite.apache.org/news/2015/06/05/algebra-builder/ 



> On May 13, 2016, at 10:38 AM, Kiril Menshikov  wrote:
> 
> Hi,
> 
> How to modify query and add one where condition?
> 
> I suppose my queries can already contain where statement and might not 
> contain.
> 
> So as I understand, I should use HepPlanner?
> 
> Thanks,
> -Kiril

Planner rule

[Kiril Menshikov]

Hi,

How to modify query and add one where condition?

I suppose my queries can already contain where statement and might not contain.

So as I understand, I should use HepPlanner?

Thanks,
-Kiril

Re: How to check for SELECT DISTINCT ...?

[Julian Hyde]

By the time the query is translated into relational algebra, SELECT
DISTINCT will have been converted into an Aggregate with no aggregate
functions.

So, for instance,

  select distinct deptno from emp

and

  select deptno from emp group by deptno

will be indistinguishable if you look at the relational algebra.

You should write a rule that matches a Sort on top of an Aggregate
that uses the same keys.

Also, I don't know whether your system supports primary keys. But if,
for example, empno is unique, then you would want to be able to
evaluate

  select empno from emp order by empno

even though there is no explicit SELECT DISTINCT. You can use the
RelMdUniqueKeys metadata to figure out whether the sort key is already
unique.

Julian


On Fri, May 13, 2016 at 8:22 AM, Tzolov, Christian  wrote:
> Hi there,
>
> I am working on a Calcite adapter for a datastore that supports a limited 
> subset of SQL expressions. My goal is to push down the supported expression.
>
> This datastore supports "ORDER BY” only if the SELECT DISTINCT expression is 
> used.
>
> What would be the best way to check if the select DISTINCT keyword is used 
> from within my SortRule implementation?
>
> Thanks,
> Christian
>

How to check for SELECT DISTINCT ...?

[Tzolov, Christian]

Hi there, 

I am working on a Calcite adapter for a datastore that supports a limited 
subset of SQL expressions. My goal is to push down the supported expression. 

This datastore supports "ORDER BY” only if the SELECT DISTINCT expression is 
used. 

What would be the best way to check if the select DISTINCT keyword is used from 
within my SortRule implementation? 

Thanks,
Christian

The correct source for foodmart test dataset (DB2 testing)

[Magnus Pierre]

Hello, 
I’ve done the necessary code changes, added the db2 jar to the repository and 
added a dependency to it in pom.xml.
Since I could not find the actual sources for mondrian foodmart used to build 
the test vm’s for Calcite
https://github.com/vlsi/calcite-test-dataset 
, I went ahead and downloaded and 
used https://github.com/OSBI/foodmart-data 
 instead
Is there a difference in data / tables between the Calcite ”test data set” and 
the mondrian foodmart data set?

I can run the tests (my clone of the main), but I don’t understand the output. 
(Three of the tests crashes and I see interesting output that I don’t 
understand)

Regards,
Magnus

Re: Avatica error codes

[F21]

On 13/05/2016 5:30 PM, Julian Hyde wrote:

Avatica’s ErrorResponse currently has the same information as JDBC (String 
sqlState, int errorCode, String errorMessage). I don’t think it’s wise to add 
an errorName field, because it would be difficult to propagate it with a JDBC 
SQLException.

If the error being thrown has a sqlState, then the sqlState code is sufficient. 
You can look up the name, e.g.

   SqlState.BY_CODE.get(“01004”).name()

evaluates to “WARNING_STRING_DATA_RIGHT_TRUNCATION”.

If the error being thrown is not a standard sqlState, I don’t think there is a 
problem putting the error name in the sqlState field. SQLException doesn’t 
check whether the code is valid, or even that it is 5 chars long.

Julian


Ah that makes sense with the ErrorResponse being tied to SQLException. 
Unfortunately, as a client of the avatica server, I am unable to call 
`SqlState.BY_CODE.get(“01004”).name()` and will still need to maintain 
my own map of error codes to the exception name.


Maybe it's possible to get an AST representation of 
https://github.com/apache/phoenix/blob/a0504fba12363eaa27ea3fd224671e92cb11a468/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java 
to manipulate it into a language agnostic format, but I think that's 
over engineering things for now.


The best solution is probably to maintain my own map and get a diff of 
SQLExceptionCode.java on every release and add/remove error codes.

Limit without order_by badly unparsed

[Emmanuel Bastien]

Hello,

I would like to open a Jira issue to contribute a very simple fix on how to
unparse order_by clause with an empty column list.

The context of the problem is the validation of queries with a limit clause
but no order_by (e.g. "SELECT c FROM t LIMIT 2").

The problem does not show up at parsing time but when running a validator.
It seems that the generated AST includes an order_by operand with an empty
column list. My assumption when fixing the unparsing is that this AST is
legit but one might argue otherwise.

The fix and associated test case is available here:
https://github.com/ebastien/calcite/commit/c47e9da09bd7305d310af5650a378ca1301053a8

I would be happy to create a PR but it seems that I am not allowed to open
a Jira issue (username ebastien). What is the procedure to do so?

Kind regards,
Emmanuel

Re: Avatica error codes

[Julian Hyde]

> On May 12, 2016, at 10:49 PM, F21  wrote:
> 
> @Julian: Your commits for CALCITE-1230 looks great! I assume those codes are 
> going to be exceptions returned by avatica when it's processing the request 
> before handing off to phoenix or some other processor. Is that correct?

It’s mainly reference data. If someone is implementing an Avatica server, when 
they throw an error, they are strongly encouraged to populate its sqlState 
value if it corresponds to a standard error. This table will make it easier for 
those implementers.

But Avatica will just pass through whatever sqlState value it gets.

> 
> I am also wrapping up the golang driver. It should be ready for release soon. 
> One of the things that might be useful is to have an machine readable 
> "error_name" in the error response. For example, a phoenix error_name might 
> be "transaction_conflict_exception" or "select_column_num_in_unionall_diffs". 
> Reason for this is that "error_message" provides a user readable message, 
> however, if we want to be able to say, retry if it's a transaction conflict, 
> we would need to check that error_code is 523. Comparing against a bunch of 
> error codes like 523 isn't as nice as doing error.ErrorName == 
> "transaction_conflict_exception".
> 
> I currently have a map mapping all of phoenix's error codes to a machine 
> readable name like "transaction_conflict_exception" to achieve this in my 
> driver, but this might be very difficult to maintain.
> 
> This is probably something that phoenix  needs to expose if this were to be 
> implemented.
> 
> Let me know what you guys think.

Avatica’s ErrorResponse currently has the same information as JDBC (String 
sqlState, int errorCode, String errorMessage). I don’t think it’s wise to add 
an errorName field, because it would be difficult to propagate it with a JDBC 
SQLException.

If the error being thrown has a sqlState, then the sqlState code is sufficient. 
You can look up the name, e.g. 

  SqlState.BY_CODE.get(“01004”).name()

evaluates to “WARNING_STRING_DATA_RIGHT_TRUNCATION”.

If the error being thrown is not a standard sqlState, I don’t think there is a 
problem putting the error name in the sqlState field. SQLException doesn’t 
check whether the code is valid, or even that it is 5 chars long.

Julian