dynamobi-build
commented
12 years ago [author="rchen", created="Fri, 28 Apr 2006 11:31:52 -0500 (GMT-05:00)"]
Perhaps the optimizer will have to be changed as well to recognize this kind of filters. This plan is obviously incorrect. No filtering is done. (Compare to the plan below it).
explain plan for select * from t1 where (select max(col1) from t3) = (select min(col1) from t2);
'column0'
'FennelToIteratorConverter'
' FennelCalcRel(expr#0..2=[{inputs}], expr#3=[IS NULL($t1)], expr#4=[IS NULL($t2)], expr#5=[=($t3, $t4)], COL1=[$t0], $condition=[$t5])'
' FennelCartesianProductRel(leftouterjoin=[false])'
' FennelCartesianProductRel(leftouterjoin=[false])'
'LcsRowScanRel(table=[[LOCALDB, SALES, T1]], projection=[_], clustered indexes=[[SYS$CLUSTEREDINDEX$SALES$T1$COL1]])'
'FennelRenameRel(fieldNames=[[EXPR$0]])'
' FennelAggRel(groupCount=[0], agg#0=[MAX(0)])'
' FennelRenameRel(fieldNames=[[$f0]])'
' LcsRowScanRel(table=[[LOCALDB, SALES, T3]], projection=[], clustered indexes=[[SYS$CLUSTEREDINDEX$SALES$T3$COL1]])'
' FennelRenameRel(fieldNames=[[EXPR$0]])'
'FennelAggRel(groupCount=[0], agg#0=[MIN(0)])'
' FennelRenameRel(fieldNames=[[$f0]])'
' LcsRowScanRel(table=[[LOCALDB, SALES, T2]], projection=[], clustered indexes=[[SYS$CLUSTEREDINDEX$SALES$T2$COL1]])'
explain plan for select * from t1 where 10 = 20;
'column0'
'IterCalcRel(expr#0=[{inputs}], expr#1=[10], expr#2=[20], expr#3=[=($t1, $t2)], COL1=[$t0], $condition=[$t3])'
' FennelToIteratorConverter'
' LcsRowScanRel(table=[[LOCALDB, SALES, T1]], projection=[], clustered indexes=[[SYS$CLUSTERED_INDEX$SALES$T1$COL1]])'
[reporter="rchen", created="Fri, 28 Apr 2006 10:59:00 -0500 (GMT-05:00)"] explain plan for select * from sales.emps where empno = (select min(empno) from sales.emps);
= <RECORDTYPE(INTEGER EXPR$0)>'. Supported form(s): ' = ' (state=,code=0)
Error: From line 1, column 49 to line 1, column 91: Cannot apply '=' to arguments of type '
Java stack:
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.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:133)
at org.eigenbase.sql.type.ComparableOperandTypeChecker.checkOperandTypes(ComparableOperandTypeChecker.java:68)
at org.eigenbase.sql.SqlOperator.checkOperandTypes(SqlOperator.java:496)
at org.eigenbase.sql.SqlOperator.validateOperands(SqlOperator.java:406)
at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1038)
at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:803)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:2129)
at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2010)
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 org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:209)
at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:230)
at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:257)
at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:718)
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)