[FRG-50] cannot use a subquery output as part of a comparison

[dynamobi-build]

[reporter="schoi", created="Sun, 19 Feb 2006 23:55:07 -0500 (GMT-05:00)"]
Repro:

----------

create schema s;

set schema 's';



create table t1(i integer primary key);

insert into t1 values(1);



values(10 < (select count(*) from t1));



Error:

---------

Error: From line 1, column 8 to line 1, column 37: Cannot apply '<' to arguments of type ' < <RECORDTYPE(BIGINT EXPR$0)>'. Supported form(s): '<COMPARABLE_TYPE> < <COMPARABLE_TYPE>' (state=,code=0)

net.sf.farrago.util.FarragoUtil$FarragoSqlException: From line 1, column 8 to line 1, column 37: Cannot apply '<' to arguments of type ' < <RECORDTYPE(BIGINT EXPR$0)>'. Supported form(s): '<COMPARABLE_TYPE> < <COMPARABLE_TYPE>'

        at org.eigenbase.resource.EigenbaseResource$_Def10.ex(EigenbaseResource.java:718)

        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:2450)

        at org.eigenbase.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:133)

        at org.eigenbase.sql.type.ComparableOperandTypeChecker.checkOperandTypes(ComparableOperandTypeChecker.java:68)

        at org.eigenbase.sql.SqlOperator.checkOperandTypes(SqlOperator.java:487)

        at org.eigenbase.sql.SqlOperator.validateOperands(SqlOperator.java:412)

        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1033)

        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:798)

        at org.eigenbase.sql.SqlNode.validateExpr(SqlNode.java:243)

        at org.eigenbase.sql.SqlOperator.validateCall(SqlOperator.java:382)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:2666)

        at org.eigenbase.sql.SqlCall.validate(SqlCall.java:131)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateValues(SqlValidatorImpl.java:2361)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:1827)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:1824)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:1978)

        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:480)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:471)

        at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:153)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:448)

        at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:273)

        at net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:62)

        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:594)

        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:530)

        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:769)

        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:720)

        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:154)

        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)



 -----



note, oracle and sqlserver can both execute something like:

select * from t1 group by i having (10 < (select sum(i) from t1));

[dynamobi-build]

[author="jvs", created="Mon, 20 Feb 2006 00:22:41 -0500 (GMT-05:00)"]
Subqueries are out of scope for Burning Chrome, but it's worth filing validation problems like this since validation is supposed to be mostly working at this point. If you get as far as the optimizer telling you that it can't find an implementation, then in that case we already know about it.

[dynamobi-build]

[author="jvs", created="Mon, 31 Jul 2006 13:55:59 -0500 (GMT-05:00)"]
One of many subquery holes.

[dynamobi-build]

[author="rchen", created="Fri, 13 Oct 2006 10:57:33 -0500 (GMT-05:00)"]
Interesting....this still gives an error



values (10 < (select count(*) from t1));

Error: java.lang.AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes:

validated type:

RecordType(BOOLEAN EXPR$0) NOT NULL

converted type:

RecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL

rel:

ProjectRel(EXPR$0=[$0])

  ProjectRel(EXPR$0=[<(10, $0)])

    ProjectRel(EXPR$0=[$0])

      AggregateRel(groupCount=[0], agg#0=[COUNT()])

        ProjectRel($f0=[true])

          TableAccessRel(table=[[LOCALDB, SALES, T1]]) (state=,code=0)

[dynamobi-build]

[author="jvs", created="Sat, 3 Nov 2007 00:07:35 -0500 (GMT-05:00)"]
Similar case:



0: jdbc:luciddb:> select (i in (select j from a.t2)) from a.t1;

Error: java.lang.AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes:

validated type:

RecordType(BOOLEAN EXPR$0) NOT NULL

converted type:

RecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL

rel:

ProjectRel(EXPR$0=[true])

  JoinRel(condition=[=($2, $3)], joinType=[inner])

    ProjectRel($f0=[$0], $f1=[$1], $f2=[$0])

      TableAccessRel(table=[[LOCALDB, A, T1]])

    AggregateRel(groupCount=[1])

      ProjectRel(J=[$1])

        TableAccessRel(table=[[LOCALDB, A, T2]]) (state=,code=0)

net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: java.lang.AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes:

validated type:

RecordType(BOOLEAN EXPR$0) NOT NULL

converted type:

RecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL

rel:

ProjectRel(EXPR$0=[true])

  JoinRel(condition=[=($2, $3)], joinType=[inner])

    ProjectRel($f0=[$0], $f1=[$1], $f2=[$0])

      TableAccessRel(table=[[LOCALDB, A, T1]])

    AggregateRel(groupCount=[1])

      ProjectRel(J=[$1])

        TableAccessRel(table=[[LOCALDB, A, T2]])



        at org.eigenbase.util.Util.newInternal(Util.java:832)

        at org.eigenbase.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:260)

        at org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:330)

        at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:268)

        at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:297)

        at net.sf.farrago.db.FarragoDatabase$2.initializeEntry(FarragoDatabase.java:891)

        at net.sf.farrago.util.FarragoObjectCache.tryPin(FarragoObjectCache.java:187)

        at net.sf.farrago.util.FarragoObjectCache.pin(FarragoObjectCache.java:146)

        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:920)

        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:766)

        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:1018)

        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:945)

        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:115)

        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:113)

        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)

[dynamobi-build]

[author="zfong", created="Tue, 18 Mar 2008 16:51:09 -0500 (GMT-05:00)"]
The original issue described in this bug will be addressed by fixes being made to address FRG-306. However, it doesn't address the issue described in the comment above this one.

[dynamobi-build]

[author="zfong", created="Fri, 28 Mar 2008 13:06:07 -0500 (GMT-05:00)"]
The issue noted in the previous comment that's not addressed by the fix for FRG-306 is due to the boolean true literal that's constructed in SqlToRelConverter.Blackboard.convertExpression() having a non-nullable type. Whereas, the type from the result of the expression in the projection is expected to be nullable, since the subquery can return an empty result. So, convertExpression probably needs to be parameterized to explicitly cast the true literal to nullable, if the expression originates from the projection list.



However, it looks like there's a more fundamental problem with the manner in which the subquery in the project list is rewritten. If you do something like the following:



create table t1(a int);

insert into t1 values(1), (2);

create table t2(a int);

insert into t2 values(1);



select a, (a in (select a from t2)) from t1;



I would expect the select to returns 2 rows -- (1, true), (2, false). Instead, it only returns the row with true.



If I change the subquery to return no rows, then I would again expect 2 rows to be returned, but with the value null for the 2nd column. Instead, no rows are returned.

[dynamobi-build]

[author="zfong", created="Fri, 28 Mar 2008 13:08:54 -0500 (GMT-05:00)"]
Oops, btw, if you try the queries in the previous comment, you'll get the Type Mismatch assertion. I got past those by putting in a fix to avoid the assert. But then ran into the wrong query result issues.

[dynamobi-build]

[author="kvu", created="Tue, 27 May 2008 09:56:19 -0500 (GMT-05:00)"]
Referring to the set of queries in Zelaine's comments on 28/Mar/08 01:06 PM:

• I got the Type Mismatch assertion trying that set
  
  
• When the definition of t1 was changed to the below, I got past the assert and got the wrong result as described in the above comments:
  
  

create table t1 (a int primary key);