dynamobi/dynamobi — Issue #690: [FRG-182] union all of two tables with differing column name where the second table has a filter fails
Author: dynamobi-build
Created: 2011-12-30 03:43:25+00:00
State: open
Labels: FRG

[reporter="elin", created="Thu, 10 Aug 2006 15:12:41 -0500 (GMT-05:00)"]
a simple union all for two tables with the same datatypes, but with different column names works fine. The result will have the same column names as the first table. A filter on the 1st table also works fine. But if there is a filter on the 2nd table, an "

Optimizer failed to find a valid physical implementation for relational expression..." error is produced

=================

To reproduce:

create schema temp;

set schema 'temp';

create table tt (a int, b int, c int);

insert into tt values (1, 2, 3), (2,3,4);

create table t2 (a int, bb int, c int);

insert into t2 values (3, 4, 4), ( 2, 3, 3);

-- these work fine, first one gives column names a,b,c the second one a,bb,c

select * from tt union all select * from t2;

select * from t2 union all select * from tt;

-- this works fine too

(select * from tt where a=1) union all select * from t2;

-- gets:

+----+----+----+

| A | B | C |

+----+----+----+

| 1 | 2 | 3 |

| 3 | 4 | 4 |

| 2 | 3 | 3 |

+----+----+----+

-- fails here:

select * from t2 union all (select * from tt where a=1);

================

stack trace:

0: jdbc:luciddb:> select * from t2 union all (select * from tt where a=1);

Error: Optimizer failed to find a valid physical implementation for relational expression rel#7053:UnionRel.NONE(input#0=HepRelVertex#7046,input#1=HepRelVertex#7062,all=true); see trace for partially optimized plan (state=,code=0)

net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: Optimizer failed to find a valid physical implementation for relational expression rel#7053:UnionRel.NONE(input#0=HepRelVertex#7046,input#1=HepRelVertex#7062,all=true); see trace for partially optimized plan

at net.sf.farrago.resource.FarragoResource$_Def0.ex(FarragoResource.java:1512)

at net.sf.farrago.query.FarragoPreparingStmt.optimize(FarragoPreparingStmt.java:706)

at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:299)

at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:270)

at net.sf.farrago.db.FarragoDatabase$2.initializeEntry(FarragoDatabase.java:824)

at net.sf.farrago.util.FarragoObjectCache.pin(FarragoObjectCache.java:153)

at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:836)

at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:707)

at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:935)

at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)

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)

=========

partial optimization plan from the trace:

Aug 10, 2006 3:15:57 PM net.sf.farrago.db.FarragoDbSession prepare

INFO: select * from t2 union all (select * from tt where a=1)

Aug 10, 2006 3:15:57 PM net.sf.farrago.query.FarragoPreparingStmt optimize

SEVERE: Plan without full implementation

UnionRel(all=[true]): rowcount = 125.0, cumulative cost = 225.0

LcsRowScanRel(table=[[LOCALDB, TEMP, T2]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$TEMP$T2$A, SYS$CLUSTERED_INDEX$TEMP$T2$BB, SYS$CLUSTERED_INDEX$TEMP$T2$C]]): rowcount = 100.0, cumulative cost = 100.0

FennelCalcRel(expr#0..2=[{inputs}], expr#3=[1], expr#4=[=($t0, $t3)], proj#0..2=[{exprs}], $condition=[$t4]): rowcount = 25.0, cumulative cost = 125.0

LcsRowScanRel(table=[[LOCALDB, TEMP, TT]], projection=[*], clustered indexes=[[SYS$CLUSTERED_INDEX$TEMP$TT$A, SYS$CLUSTERED_INDEX$TEMP$TT$B, SYS$CLUSTERED_INDEX$TEMP$TT$C]]): rowcount = 100.0, cumulative cost = 100.0