search

LucidDB / luciddb

DEFUNCT: See README
https://github.com/LucidDB/luciddb
Apache License 2.0
52 stars 24 forks source link

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

Open dynamobi-build opened 12 years ago

dynamobi-build commented 12 years ago

[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): ' < ' (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): ' < '
        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 commented 12 years ago

[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 commented 12 years ago

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

dynamobi-build commented 12 years ago

[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 commented 12 years ago

[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 commented 12 years ago

[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 commented 12 years ago

[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 commented 12 years ago

[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 commented 12 years ago

[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:


create table t1 (a int primary key);