[FRG-212] analyze table unable to find optimized plan after switching to java calc

[dynamobi-build]

[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

[dynamobi-build]

[author="elin", created="Thu, 21 Sep 2006 20:07:05 -0500 (GMT-05:00)"] closed LER-1985 as duplicate

[dynamobi-build]

[author="jpham", created="Sun, 24 Sep 2006 15:24:01 -0500 (GMT-05:00)"] Seems to work as of Liz's change 7743. FlatFileColumnSet had been explicitly instantiating a FennelToIteratorConverterRel in order to encourage the optimizer to use the Java calc. [There was a generic CalcRel to be implemented as IterCalcRel.] But this could break due to the new FennelReshapeRel which would implement a CalcRel with FennelReshapeRel.

JVS notes that sometimes we want to catch warnings (when truncating strings). So far we have been quiet on warnings, but when we implement them, it may be good to have Java calc take care of them. I guess we'll cross that bridge when we get there. For now, I took out the explicit converter (also not needed due to luciddb preferring Java calc).