[jira] [Created] (CALCITE-1605) Wrong result/plan for correlated subquery with windowing

[Vineet Garg (JIRA)]

Vineet Garg created CALCITE-1605:


 Summary: Wrong result/plan for correlated subquery with windowing
 Key: CALCITE-1605
 URL: https://issues.apache.org/jira/browse/CALCITE-1605
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Vineet Garg
Assignee: Julian Hyde


Calcite produces wrong plan, hence wrong results for correlated subquery which 
contains windowing function. 

Query
{code}select * from emps where empid-1 IN (select min(e.empid) over () from 
emps e where e.empid =emps.empid); {code}

Expected
{code} 0 rows {code}

Actual
{noformat}
+---+--+++--+---+-+-+-+--+
| EMPNO | NAME | DEPTNO | GENDER | CITY | EMPID | AGE | SLACKER | MANAGER | 
JOINEDAT |
+---+--+++--+---+-+-+-+--+
| 110   | John | 40 | M  | Vancouver | 2 | null | false   | true
| 2002-05-03 |
| 130   | Alice | 40 | F  | Vancouver | 2 | null | false   | true   
 | 2007-01-01 |
+---+--+++--+---+-+-+-+--+
{noformat}





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Created] (CALCITE-1604) Add missing support for system JDBC functions

[Laurent Goujon (JIRA)]

Laurent Goujon created CALCITE-1604:
---

 Summary: Add missing support for system JDBC functions
 Key: CALCITE-1604
 URL: https://issues.apache.org/jira/browse/CALCITE-1604
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Laurent Goujon
Assignee: Julian Hyde
Priority: Minor


Calcite advertises support for all system functions (DATABASE, IFNULL and USER) 
but implementations are missing for all of them

It would be a nice-to have to extend the support so that all functions are 
covered.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Re: RexNode representation of IN w/expr list

[Jess Balint]

Thanks for the hint. I've got this rel tree now:

  LogicalProject(F_8=[$2], F_7=[$0], F_11=[$8])
LogicalFilter(condition=[IN($8, {
LogicalValues(tuples=[[{ 100 }, { 200 }, { 300 }]])
})])
  LogicalJoin(condition=[=($0, $7)], joinType=[inner])
JdbcTableScan(table=[[PUBLIC, EMPLOYEES]])
JdbcTableScan(table=[[PUBLIC, SALARIES]])

Does this look right? I used RexSubQuery.in() and passed this straight to
relBuilder.filter(). I converted this to SqlNode but it won't unparse as
the SqlBasicCall(IN) only has one operand but is unparsed due to IN being a
binary operator. The operand is the "$8" input ref (this is due to
RexSubQuery returning only one operand and not the rel). Any ideas on where
the issue is here?

Thanks a lot.

Jess

On Wed, Jan 25, 2017 at 4:32 PM, Jess Balint  wrote:

> Thanks for the hint. I've got this rel tree now:
>
>
> On Mon, Jan 23, 2017 at 1:18 PM, Julian Hyde  wrote:
>
>> “IN” with a list of scalar values is just syntactic sugar. If you use
>> RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x =
>> 1 or x = 2’ long-hand.
>>
>> If you want to, you can instead use RelBuilder to build the equivalent of
>> ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node
>> for that. That formulation is semantically equivalent to the OR but
>> structurally quite different, and will tend to be optimized differently.
>>
>> Julian
>>
>>
>> > On Jan 23, 2017, at 8:36 AM, Jess Balint  wrote:
>> >
>> > Hi,
>> >
>> > I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want
>> to
>> > use IN with the scalar expr list form, not a subquery. How should this
>> be
>> > represented by a RexNode? I tried using SqlToRelConverter and it parses
>> as
>> > "x = 1 OR x = 2 ". When I used the expr list form through RelBuilder
>> > (flat list of arguments with LHS first) and passed it to
>> RelToSqlConverter,
>> > I get "x IN 1 IN 2" due to this code in SqlImplementor:
>> >
>> >if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
>> >  // In RexNode trees, OR and AND have any number of children;
>> >  // SqlCall requires exactly 2. So, convert to a left-deep
>> binary
>> > tree.
>> >  return createLeftCall(op, nodeList);
>> >}
>> >
>> > I am wondering if one of the following is true:
>> > * IN w/expr list is not intended to be represented in RexNode form,
>> hence
>> > the conversion by SqlToRelConverter creates an OR tree
>> > * I am representing the arguments incorrectly w/something like call(IN,
>> > field("x"), literal(1), literal(2)) and need to use a specialized
>> RexNode
>> > structure
>> > * RelToSqlConverter is lacking the proper handling of the IN operator
>> >
>> > Any hints/thoughts appreciated.
>> >
>> > Thanks.
>> > Jess
>>
>>
>

Re: RexNode representation of IN w/expr list

[Jess Balint]

Thanks for the hint. I've got this rel tree now:


On Mon, Jan 23, 2017 at 1:18 PM, Julian Hyde  wrote:

> “IN” with a list of scalar values is just syntactic sugar. If you use
> RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x =
> 1 or x = 2’ long-hand.
>
> If you want to, you can instead use RelBuilder to build the equivalent of
> ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node
> for that. That formulation is semantically equivalent to the OR but
> structurally quite different, and will tend to be optimized differently.
>
> Julian
>
>
> > On Jan 23, 2017, at 8:36 AM, Jess Balint  wrote:
> >
> > Hi,
> >
> > I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want
> to
> > use IN with the scalar expr list form, not a subquery. How should this be
> > represented by a RexNode? I tried using SqlToRelConverter and it parses
> as
> > "x = 1 OR x = 2 ". When I used the expr list form through RelBuilder
> > (flat list of arguments with LHS first) and passed it to
> RelToSqlConverter,
> > I get "x IN 1 IN 2" due to this code in SqlImplementor:
> >
> >if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
> >  // In RexNode trees, OR and AND have any number of children;
> >  // SqlCall requires exactly 2. So, convert to a left-deep binary
> > tree.
> >  return createLeftCall(op, nodeList);
> >}
> >
> > I am wondering if one of the following is true:
> > * IN w/expr list is not intended to be represented in RexNode form, hence
> > the conversion by SqlToRelConverter creates an OR tree
> > * I am representing the arguments incorrectly w/something like call(IN,
> > field("x"), literal(1), literal(2)) and need to use a specialized RexNode
> > structure
> > * RelToSqlConverter is lacking the proper handling of the IN operator
> >
> > Any hints/thoughts appreciated.
> >
> > Thanks.
> > Jess
>
>

Fwd: Help promote ApacheCon

[Julian Hyde]

It would be great if had an "ApacheCon: Big Data" promotion on our web site. 
(For an example of how it would look, see the ApacheCon image on the LHS of 
http://httpd.apache.org/ .) 

Would anyone like to do this?

Julian




> Begin forwarded message:
> 
> From: Rich Bowen 
> Subject: Help promote ApacheCon
> Date: January 25, 2017 at 1:17:07 PM PST
> To: members 
> Reply-To: memb...@apache.org
> 
> In the past, we have requested that all ASF projects incorporate a
> snippet of HTML in pages on their project website to help us promote
> ApacheCon. In the last few years, we have not communicated this to new
> projects, or reminded older projects, and so it's actually fairly
> uncommon now, with, I think, less than 15 of our projects doing this.
> 
> The process for including this promotion on your project website may be
> seen here: http://www.apache.org/events/README.txt
> 
> The image is available in two sizes, so that it will probably fit into
> whatever site layout you have. The image itself is updated with each
> event, so that it's always current. (Yes, we have not done a great job
> of this in the past, but we'll be much more consistent with this going
> forward.)
> 
> You can see an example of the image in use, at http://httpd.apache.org/
> 
> We would greatly appreciate it if more project sites could take this
> simple step to help promote our upcoming event.
> 
> Thanks!
> 
> -- 
> Rich Bowen - rbo...@apache.org
> VP, Conferences
> http://apachecon.com/ - @apachecon
> 

Re: fetching columns from select clause

[Julian Hyde]

If you are doing adding filters / projects for the purposes of access control, 
you should consider creating views. Your users will write queries on the views 
and be transformed to queries on the base tables.

For other kinds of algebraic transforms — especially manipulations that 
preserve semantics — I suggest that you use planner rules as much as possible. 
There are a lot of existing rules, and they are well tested. You will be able 
to achieve a fair amount by manipulating expressions on FilterableTable but you 
will start seeing diminishing returns.

Hopefully that gives you an idea where to start.

Julian


> On Jan 25, 2017, at 10:40 AM, ravi katkar  wrote:
> 
> Hi Julian,
> 
> 
> i want to retrieve columns names in calcite adapter code so as to rewrite
> the select clause query. I want to add few sudo columns in select and
> rewrite the query for my requirement.
> e:g from FilterableTable type we can access the where clause filters and
> manipulate the filters, in similar line i want to access the select clause
> and rewrite the query.
> 
> Thanks,
> Ravi Katkar
> 
> On Wed, Jan 25, 2017 at 9:59 PM, Julian Hyde  wrote:
> 
>> Have you tried PreparedStatement.getMetaData()?
>> 
>>> On Jan 25, 2017, at 5:11 AM, ravi katkar 
>> wrote:
>>> 
>>> Hi list,
>>> 
>>> I am working on POC for our requirement, majorly relying on the CSV
>>> adapter.
>>> 
>>> I am looking way to retrieve the  columns from the select clause from the
>>> sql -query in csv adapter.
>>> 
>>> Eg: select firstname, lastname,dept from Employee where empid= 10;
>>> 
>>> i want to retrive  columns firstname, lastname, dept from the query.
>>> 
>>> Thanks,
>>> Ravi K
>> 
>> 

Re: fetching columns from select clause

[ravi katkar]

Hi Julian,


i want to retrieve columns names in calcite adapter code so as to rewrite
the select clause query. I want to add few sudo columns in select and
rewrite the query for my requirement.
e:g from FilterableTable type we can access the where clause filters and
manipulate the filters, in similar line i want to access the select clause
and rewrite the query.

Thanks,
Ravi Katkar

On Wed, Jan 25, 2017 at 9:59 PM, Julian Hyde  wrote:

> Have you tried PreparedStatement.getMetaData()?
>
> > On Jan 25, 2017, at 5:11 AM, ravi katkar 
> wrote:
> >
> > Hi list,
> >
> > I am working on POC for our requirement, majorly relying on the CSV
> > adapter.
> >
> > I am looking way to retrieve the  columns from the select clause from the
> > sql -query in csv adapter.
> >
> > Eg: select firstname, lastname,dept from Employee where empid= 10;
> >
> > i want to retrive  columns firstname, lastname, dept from the query.
> >
> > Thanks,
> > Ravi K
>
>