{"issue":{"milestone":null,"title":"[LDB-145] getting column not found error for query with subquery in where clause","created_at":"2011-12-30T02:18:54Z","user":{"login":"dynamobi-build","id":1123017,"url":"https://api.github.com/users/dynamobi-build","gravatar_id":"cb215b3d786be20761071a562a56af64","avatar_url":"https://secure.gravatar.com/avatar/cb215b3d786be20761071a562a56af64?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"labels":[{"name":"LDB","url":"https://api.github.com/repos/dynamobi/dynamobi/labels/LDB","color":"e2b9bd"}],"updated_at":"2011-12-30T02:18:57Z","state":"closed","id":2685346,"closed_at":"2011-12-30T02:18:55Z","html_url":"https://github.com/dynamobi/dynamobi/issues/176","pull_request":{"patch_url":null,"html_url":null,"diff_url":null},"body":"<b><i>[reporter=\"elin\", created=\"Fri, 13 Oct 2006 10:53:04 -0500 (GMT-05:00)\"]</i></b>\ncut-n-paste from my email:<br/>\n<br/>\nHere's what the query in question:<br/>\nSELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY<br/>\nFROM LINEITEM L, PART<br/>\nWHERE<br/>\n    P_PARTKEY = L.L_PARTKEY AND<br/>\n    P_BRAND = 'Brand#23' AND<br/>\n    P_CONTAINER = 'MED BOX' AND<br/>\n    L.L_QUANTITY <<br/>\n        (SELECT 0.2 * AVG(L_QUANTITY)<br/>\n         FROM LINEITEM L1<br/>\n         GROUP BY L_PARTKEY = P_PARTKEY);<br/>\n<br/>\nThe error complains about not being able to find the column P_PARTKEY in<br/>\nthe last line, but there exists only one column in PART which is called<br/>\nP_PARTKEY, all columns across all tables in the tpch schema are uniquely<br/>\nnamed. <br/>\n<br/>\n-------------------------------------------------------------<br/>\nHere's a more detailed stack trace:<br/>\n<br/>\nError: From line 10, column 31 to line 10, column 39: Column 'P_PARTKEY'<br/>\nnot found in any table (state=,code=0)<br/>\nnet.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: From line 10,<br/>\ncolumn 31 to line 10, column 39: Column 'P_PARTKEY' not found in any table<br/>\n        at<br/>\norg.eigenbase.resource.EigenbaseResource$_Def10.ex(EigenbaseResource.java:793)<br/>\n        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:729)<br/>\n        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:716)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:2798)<br/>\n        at<br/>\norg.eigenbase.sql.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:110)<br/>\n        at<br/>\norg.eigenbase.sql.validate.ListScope.findQualifyingTableName(ListScope.java:141)<br/>\n        at<br/>\norg.eigenbase.sql.validate.ListScope.findQualifyingTableName(ListScope.java:141)<br/>\n        at<br/>\norg.eigenbase.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:176)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:346)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:312)<br/>\n        at org.eigenbase.sql.SqlIdentifier.accept(SqlIdentifier.java:349)<br/>\n        at<br/>\norg.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:151)<br/>\n        at<br/>\norg.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:114)<br/>\n        at org.eigenbase.sql.SqlOperator.acceptCall(SqlOperator.java:672)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visitScoped(SqlValidatorUtil.java:335)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:63)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:40)<br/>\n        at org.eigenbase.sql.SqlCall.accept(SqlCall.java:186)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:324)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:312)<br/>\n        at org.eigenbase.sql.SqlNodeList.accept(SqlNodeList.java:152)<br/>\n        at<br/>\norg.eigenbase.sql.validate.AggregatingSelectScope.<init>(AggregatingSelectScope.java:64)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1624)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1556)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1850)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1602)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1556)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:530)<br/>\n        at<br/>\norg.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:357)<br/>\n        at<br/>\nnet.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:66)<br/>\n        at<br/>\nnet.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:778)<br/>\n        at<br/>\nnet.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:705)<br/>\n        at<br/>\nnet.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:939)<br/>\n        at<br/>\nnet.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:866)<br/>\n        at<br/>\nnet.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)<br/>\n        at<br/>\nnet.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)<br/>\n        at sqlline.SqlLine$Commands.execute(Unknown Source)<br/>\n        at sqlline.SqlLine$Commands.sql(Unknown Source)<br/>\n        at sqlline.SqlLine.dispatch(Unknown Source)<br/>\n        at sqlline.SqlLine.begin(Unknown Source)<br/>\n        at sqlline.SqlLine.mainWithInputRedirection(Unknown Source)<br/>\n        at sqlline.SqlLine.main(Unknown Source)<br/>\n<br/>\n-------------------------<br/>\n<br/>\nTo reproduce you can load in the tables by running ant test<br/>\n-Dtestfile=test.xml -Dbh.test-names=tpch.createIndexes  from<br/>\nopen/luciddb/test/sql/tpch before running the query above.","number":176,"url":"https://api.github.com/repos/dynamobi/dynamobi/issues/176","comments":1,"assignee":null},"comment":{"created_at":"2011-12-30T02:18:57Z","user":{"login":"dynamobi-build","id":1123017,"url":"https://api.github.com/users/dynamobi-build","gravatar_id":"cb215b3d786be20761071a562a56af64","avatar_url":"https://secure.gravatar.com/avatar/cb215b3d786be20761071a562a56af64?d=https://a248.e.akamai.net/assets.github.com%2Fimages%2Fgravatars%2Fgravatar-140.png"},"updated_at":"2011-12-30T02:18:57Z","id":3308877,"body":"<b><i>[author=\"jvs\", created=\"Fri, 13 Oct 2006 11:00:21 -0500 (GMT-05:00)\"]</i></b>\nThis is a cool bug (GROUP BY on predicate), but note that the original query 17.sql (below from BB archives) doesn't have that.  Liz, you may have just been playing around with it, but just in case:  original queries are under bb/main/server/Test/tpcd.<br/>\n<br/>\nSELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY<br/>\nFROM LINEITEM, PART<br/>\nWHERE<br/>\n    P_PARTKEY = L_PARTKEY AND<br/>\n    P_BRAND = 'Brand#23' AND<br/>\n    P_CONTAINER = 'MED BOX' AND<br/>\n    L_QUANTITY <<br/>\n\t(SELECT 0.2 * AVG(L_QUANTITY)<br/>\n\tFROM LINEITEM L1<br/>\n\tWHERE L_PARTKEY = P_PARTKEY);<br/>\n","url":"https://api.github.com/repos/dynamobi/dynamobi/issues/comments/3308877"},"action":"created"}