dynamobi/dynamobi — Issue #176: [LDB-145] getting column not found error for query with subquery in where clause
Author: dynamobi-build
Created: 2011-12-30 02:18:54+00:00
State: open
Labels: LDB

[reporter="elin", created="Fri, 13 Oct 2006 10:53:04 -0500 (GMT-05:00)"]
cut-n-paste from my email:

Here's what the query in question:

SELECT SUM(L_EXTENDEDPRICE) / 7.0 AS AVG_YEARLY

FROM LINEITEM L, PART

WHERE

P_PARTKEY = L.L_PARTKEY AND

P_BRAND = 'Brand#23' AND

P_CONTAINER = 'MED BOX' AND

L.L_QUANTITY <

(SELECT 0.2 * AVG(L_QUANTITY)

FROM LINEITEM L1

GROUP BY L_PARTKEY = P_PARTKEY);

The error complains about not being able to find the column P_PARTKEY in

the last line, but there exists only one column in PART which is called

P_PARTKEY, all columns across all tables in the tpch schema are uniquely

named.

-------------------------------------------------------------

Here's a more detailed stack trace:

Error: From line 10, column 31 to line 10, column 39: Column 'P_PARTKEY'

not found in any table (state=,code=0)

net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: From line 10,

column 31 to line 10, column 39: Column 'P_PARTKEY' not found in any table

at

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

at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:729)

at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:716)

at

org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:2798)

at

org.eigenbase.sql.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:110)

at

org.eigenbase.sql.validate.ListScope.findQualifyingTableName(ListScope.java:141)

at

org.eigenbase.sql.validate.ListScope.findQualifyingTableName(ListScope.java:141)

at

org.eigenbase.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:176)

at

org.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:346)

at

org.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:312)

at org.eigenbase.sql.SqlIdentifier.accept(SqlIdentifier.java:349)

at

org.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:151)

at

org.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:114)

at org.eigenbase.sql.SqlOperator.acceptCall(SqlOperator.java:672)

at

org.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visitScoped(SqlValidatorUtil.java:335)

at

org.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:63)

at

org.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:40)

at org.eigenbase.sql.SqlCall.accept(SqlCall.java:186)

at

org.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:324)

at

org.eigenbase.sql.validate.SqlValidatorUtil$DeepCopier.visit(SqlValidatorUtil.java:312)

at org.eigenbase.sql.SqlNodeList.accept(SqlNodeList.java:152)

at

org.eigenbase.sql.validate.AggregatingSelectScope.(AggregatingSelectScope.java:64)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1624)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1556)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1850)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerSubqueries(SqlValidatorImpl.java:1857)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerOperandSubqueries(SqlValidatorImpl.java:1907)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1602)

at

org.eigenbase.sql.validate.SqlValidatorImpl.registerQuery(SqlValidatorImpl.java:1556)

at

org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:530)

at

org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:357)

at

net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:66)

at

net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:778)

at

net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:705)

at

net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:939)

at

net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:866)

at

net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)

at

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

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)

-------------------------

To reproduce you can load in the tables by running ant test

-Dtestfile=test.xml -Dbh.test-names=tpch.createIndexes from

open/luciddb/test/sql/tpch before running the query above.