{"issue":{"milestone":null,"title":"[FRG-50] cannot use a subquery output as part of a comparison","created_at":"2011-12-30T03:50:44Z","user":{"login":"dynamobi-build","id":1123017,"url":"https://api.github.com/users/dynamobi-build","avatar_url":"https://secure.gravatar.com/avatar/cb215b3d786be20761071a562a56af64?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png","gravatar_id":"cb215b3d786be20761071a562a56af64"},"updated_at":"2011-12-30T03:50:47Z","labels":[{"name":"FRG","url":"https://api.github.com/repos/dynamobi/dynamobi/labels/FRG","color":"1f8f0d"},{"name":"Statement Processing","url":"https://api.github.com/repos/dynamobi/dynamobi/labels/Statement+Processing","color":"a61150"}],"state":"open","id":2686381,"closed_at":null,"pull_request":{"patch_url":null,"html_url":null,"diff_url":null},"html_url":"https://github.com/dynamobi/dynamobi/issues/820","body":"<b><i>[reporter=\"schoi\", created=\"Sun, 19 Feb 2006 23:55:07 -0500 (GMT-05:00)\"]</i></b>\nRepro:<br/>\n----------<br/>\ncreate schema s;<br/>\nset schema 's';<br/>\n<br/>\ncreate table t1(i integer primary key);<br/>\ninsert into t1 values(1);<br/>\n<br/>\nvalues(10 < (select count(*) from t1));<br/>\n<br/>\nError:<br/>\n---------<br/>\nError: From line 1, column 8 to line 1, column 37: Cannot apply '<' to arguments of type '<INTEGER> < <RECORDTYPE(BIGINT EXPR$0)>'. Supported form(s): '<COMPARABLE_TYPE> < <COMPARABLE_TYPE>' (state=,code=0)<br/>\nnet.sf.farrago.util.FarragoUtil$FarragoSqlException: From line 1, column 8 to line 1, column 37: Cannot apply '<' to arguments of type '<INTEGER> < <RECORDTYPE(BIGINT EXPR$0)>'. Supported form(s): '<COMPARABLE_TYPE> < <COMPARABLE_TYPE>'<br/>\n        at org.eigenbase.resource.EigenbaseResource$_Def10.ex(EigenbaseResource.java:718)<br/>\n        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:684)<br/>\n        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:671)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:2450)<br/>\n        at org.eigenbase.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:133)<br/>\n        at org.eigenbase.sql.type.ComparableOperandTypeChecker.checkOperandTypes(ComparableOperandTypeChecker.java:68)<br/>\n        at org.eigenbase.sql.SqlOperator.checkOperandTypes(SqlOperator.java:487)<br/>\n        at org.eigenbase.sql.SqlOperator.validateOperands(SqlOperator.java:412)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1033)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:798)<br/>\n        at org.eigenbase.sql.SqlNode.validateExpr(SqlNode.java:243)<br/>\n        at org.eigenbase.sql.SqlOperator.validateCall(SqlOperator.java:382)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:2666)<br/>\n        at org.eigenbase.sql.SqlCall.validate(SqlCall.java:131)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateValues(SqlValidatorImpl.java:2361)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:1827)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:1824)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:1978)<br/>\n        at org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:54)<br/>\n        at org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:76)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:480)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:471)<br/>\n        at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:153)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:448)<br/>\n        at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:273)<br/>\n        at net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:62)<br/>\n        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:594)<br/>\n        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:530)<br/>\n        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:769)<br/>\n        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:720)<br/>\n        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:154)<br/>\n        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)<br/>\n<br/>\n -----<br/>\n<br/>\nnote, oracle and sqlserver can both execute something like:<br/>\nselect * from t1 group by i having (10 < (select sum(i) from t1));","number":820,"url":"https://api.github.com/repos/dynamobi/dynamobi/issues/820","comments":3,"assignee":null},"comment":{"created_at":"2011-12-30T03:50:47Z","user":{"login":"dynamobi-build","id":1123017,"url":"https://api.github.com/users/dynamobi-build","avatar_url":"https://secure.gravatar.com/avatar/cb215b3d786be20761071a562a56af64?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png","gravatar_id":"cb215b3d786be20761071a562a56af64"},"updated_at":"2011-12-30T03:50:47Z","id":3310495,"body":"<b><i>[author=\"rchen\", created=\"Fri, 13 Oct 2006 10:57:33 -0500 (GMT-05:00)\"]</i></b>\nInteresting....this still gives an error<br/>\n<br/>\nvalues (10 < (select count(*) from t1));<br/>\nError: java.lang.AssertionError: Internal error: Conversion to relational algebra failed to preserve datatypes:<br/>\nvalidated type:<br/>\nRecordType(BOOLEAN EXPR$0) NOT NULL<br/>\nconverted type:<br/>\nRecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL<br/>\nrel:<br/>\nProjectRel(EXPR$0=[$0])<br/>\n  ProjectRel(EXPR$0=[<(10, $0)])<br/>\n    ProjectRel(EXPR$0=[$0])<br/>\n      AggregateRel(groupCount=[0], agg#0=[COUNT()])<br/>\n        ProjectRel($f0=[true])<br/>\n          TableAccessRel(table=[[LOCALDB, SALES, T1]]) (state=,code=0)<br/>\n","url":"https://api.github.com/repos/dynamobi/dynamobi/issues/comments/3310495"},"action":"created"}