[FRG-115] having clause with between not working

[dynamobi-build]

[reporter="elin", created="Fri, 28 Apr 2006 14:41:02 -0500 (GMT-05:00)"] To reproduce:

create table t1(cola int primary key, colb int);
insert into t1 values (1,1),(2,2),(3,3);
-- this errors
select colb from t1 group by colb having colb between 1 and 3;

-- this works fine
select colb from t1 group by colb having colb > 1 and colb < 3;

Here's the stack trace:Error: From line 1, column 42 to line 1, column 45: Unknown identifier 'COLB' (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: From line 1, column 42 to line 1, column 45: Unknown identifier 'COLB'
        at org.eigenbase.resource.EigenbaseResource$_Def10.ex(EigenbaseResource.java:748)
        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:684)
        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)
        at org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:2557)
        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:875)
        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:803)
        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:978)
        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:803)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateHavingClause(SqlValidatorImpl.java:2157)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2012)
        at org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:54)
        at org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:76)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:483)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:474)
        at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:153)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:451)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:276)
        at net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:62)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:738)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:674)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:842)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:784)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:108)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:105)
        at sqlline.SqlLine$Commands.execute(Unknown Source)
        at sqlline.SqlLine$Commands.sql(Unknown Source)
        at sqlline.SqlLine.dispatch(Unknown Source)
        at sqlline.SqlLine.begin(Unknown Source)
        at sqlline.SqlLine.mainWithInputRedirection(Unknown Source)
        at sqlline.SqlLine.main(Unknown Source)
0: jdbc:farrago:>

[dynamobi-build]

[author="jhyde", created="Fri, 28 Apr 2006 18:57:48 -0500 (GMT-05:00)"] This is caused by the kind of scope being used. The AggregatingSelectScope is a child of SelectScope, and a scope's resolve method can only resolve unqualified identifiers if they are in the current scope.

That makes sense for correlated subqueries, for example

select * from emp where exists (select * from dept where deptno = sal)

is wrong and

select * from emp where exists (select * from dept where deptno = emp.sal)

is right, because is a reference to a correlating variable in an outer query.

However the HAVING clause is in the same query. I think what's needed is to distinguish between the two types of scope inheritance -- parent scope vs. delegating scope.

[dynamobi-build]

[author="jhyde", created="Wed, 14 Jun 2006 16:00:52 -0500 (GMT-05:00)"] Fixed in change 6908.

[dynamobi-build]

[author="elin", created="Mon, 3 Jul 2006 17:27:42 -0500 (GMT-05:00)"] For the same example above, an assertion error is produced both for this:
select colb from t1 group by colb having colb between 1 and 3;

-- as well as this, which used to work
select colb from t1 group by colb having colb > 1 and colb < 3;

Both work fine if the column in the having clause is qualified w/ the table name (t1.colb)

Since the having clause is in the same query, it doesn't seem like it should need to be qualified.

Here's the stack trace:
Error: java.lang.AssertionError: Internal error: Identifier 'COLB' is not a group expr (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: java.lang.AssertionError: Internal error: Identifier 'COLB' is not a group expr
        at org.eigenbase.util.Util.newInternal(Util.java:821)
        at org.eigenbase.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:1741)
        at org.eigenbase.sql2rel.SqlToRelConverter.access$900(SqlToRelConverter.java:58)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:2516)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:2121)
        at org.eigenbase.sql.SqlIdentifier.accept(SqlIdentifier.java:328)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:2440)
        at org.eigenbase.sql2rel.StandardConvertletTable.convertExpressionList(StandardConvertletTable.java:558)
        at org.eigenbase.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:547)
        at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
        at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown Source)
        at org.eigenbase.sql2rel.ReflectiveConvertletTable$1.convertCall(ReflectiveConvertletTable.java:105)
        at org.eigenbase.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:64)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:2504)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:2121)
        at org.eigenbase.sql.SqlCall.accept(SqlCall.java:178)
        at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:2440)
        at org.eigenbase.sql2rel.StandardConvertletTable.convertExpressionList(StandardConvertletTable.java:558)
        at org.eigenbase.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:547)
        at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
0: jdbc:farrago:>

[dynamobi-build]

[author="elin", created="Tue, 31 Oct 2006 15:28:40 -0500 (GMT-05:00)"] Issue seems to have been fixed.