dynamobi/dynamobi — Issue #660: [FRG-212] analyze table unable to find optimized plan after switching to java calc
Author: dynamobi-build
Created: 2011-12-30 03:41:45+00:00
State: open
Labels: FRG

[reporter="elin", created="Thu, 21 Sep 2006 17:57:50 -0500 (GMT-05:00)"]
To repro:

create schema udptest;

set schema 'udptest';

create server ff_server

foreign data wrapper sys_file_wrapper

options(

directory '/home2/elin/open/luciddb/test/sql/udr/udp/csv-copy',

file_extension '.csv',

ctrl_file_extension '.bcp',

field_delimiter ',',

line_delimiter '\n',

quote_char '"',

escape_char '',

with_header 'yes',

num_rows_scan '9'

);

create foreign table udptest.emp(

EMPNO numeric(5, 0),

FNAME varchar(20),

LNAME varchar(20),

SEX char(1),

DEPTNO integer,

MANAGER numeric(5, 0),

LOCID char(2),

SAL integer,

COMMISION integer,

HOBBY varchar(20)

)

server ff_server

options (

SCHEMA_NAME 'BCP',

table_name 'EMP'

);

analyze table EMP estimate statistics for all columns SAM\PLE 100 PERCENT;

-- trace

0: jdbc:luciddb:> analyze table EMP estimate statistics for all columns SAMPLE 100 PERCENT;

Error: Optimizer failed to find a valid physical implementation for relational expression rel#286:FennelToIteratorConverter.ITERATOR(child=HepRelVertex#293); 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#286:FennelToIteratorConverter.ITERATOR(child=HepRelVertex#293); see trace for partially optimized plan

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

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

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

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

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:937)

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

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

at net.sf.farrago.ddl.DdlAnalyzeStmt.updateColumnStats(DdlAnalyzeStmt.java:227)

at net.sf.farrago.ddl.DdlAnalyzeStmt.analyzeTable(DdlAnalyzeStmt.java:175)

at net.sf.farrago.ddl.DdlAnalyzeStmt.preValidate(DdlAnalyzeStmt.java:120)

at net.sf.farrago.ddl.DdlValidator.validate(DdlValidator.java:792)

at net.sf.farrago.db.FarragoDbSession.validateDdl(FarragoDbSession.java:981)

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

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

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(Ljava.lang.String;Z)Z(Unknown Source)

at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)

at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)

at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)

0: jdbc:luciddb:>

--- partial plan from trace:

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession prepare

INFO: analyze table EMP estimate statistics for all columns SAMPLE 100 PERC\ENT

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession commitImpl

INFO: commit

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession prepare

INFO: select count(*) from "LOCALDB"."UDPTEST"."EMP"

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession commitImpl

INFO: commit

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession prepare

INFO: select "EMPNO" , count(*) from "LOCALDB"."UDPTEST"."EMP" group by "E\MPNO" order by "EMPNO"

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession commitImpl

INFO: commit

Sep 21, 2006 5:48:16 PM net.sf.farrago.db.FarragoDbSession prepare

INFO: select "FNAME" , count(*) from "LOCALDB"."UDPTEST"."EMP" group by "F\NAME" order by "FNAME"

Sep 21, 2006 5:48:17 PM net.sf.farrago.query.FarragoPreparingStmt optimize

SEVERE: Plan without full implementation

FennelToIteratorConverter: rowcount = 1.1, cumulative cost = 37.40000000000\0006

FennelSortRel(key=[[0]], discardDuplicates=[false]): rowcount = 1.1, cumu\lative cost = 36.300000000000004

FennelRenameRel(fieldNames=[[FNAME, EXPR$1]]): rowcount = 1.1, cumulati\ve cost = 35.2

LhxAggRel(groupCount=[1], agg#0=[COUNT()]): rowcount = 1.1, cumulativ\e cost = 34.1

FennelReshapeRel(projection=[[1]], outputRowType=[RecordType(VARCHA\R(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" $f0) NO\T NULL]): rowcount = 11.0, cumulative cost = 33.0

FennelToIteratorConverter: rowcount = 11.0, cumulative cost = 22.\0

FlatFileFennelRel(table=[[LOCALDB, UDPTEST, EMP]]): rowcount = \11.0, cumulative cost = 11.0

----------

related test: udp.cr_source