[FRG-334] attempting to insert into a foreign table gives a bad error message

[dynamobi-build]

[reporter="jvs", created="Tue, 19 Aug 2008 15:57:30 -0500 (GMT-05:00)"] 0: jdbc:farrago:> insert into hsqldb_demo.sales.dept values (40, 'Netops', null);
Error: java.lang.NullPointerException: null (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: java.lang.NullPointerException: null
        at net.sf.farrago.query.ReposDefaultValueFactory.isGeneratedAlways(ReposDefaultValueFactory.java:95)
        at org.eigenbase.sql2rel.SqlToRelConverter.convertColumnList(SqlToRelConverter.java:2664)
        at org.eigenbase.sql2rel.SqlToRelConverter.convertInsert(SqlToRelConverter.java:2595)
        at org.eigenbase.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:2528)
        at org.eigenbase.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:404)
        at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:270)
        at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:332)
        at net.sf.farrago.db.FarragoDatabase$2.initializeEntry(FarragoDatabase.java:912)
        at net.sf.farrago.util.FarragoObjectCache.tryPin(FarragoObjectCache.java:187)
        at net.sf.farrago.util.FarragoObjectCache.pin(FarragoObjectCache.java:146)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:941)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:787)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:1072)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:997)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:129)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:109)
        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)

[dynamobi-build]

[author="jvs", created="Tue, 19 Aug 2008 16:00:12 -0500 (GMT-05:00)"] Via a foreign table, it's only a little better:

0: jdbc:farrago:> create schema demo_schema;
No rows affected (0.093 seconds)
0: jdbc:farrago:> create foreign table demo_schema.dept(
. . . . . . . . > dno integer,
. . . . . . . . > dname char(20),
. . . . . . . . > loc char(20))
. . . . . . . . > server hsqldb_demo
. . . . . . . . > options(schema_name 'SALES', table_name 'DEPT');
No rows affected (0.292 seconds)
0: jdbc:farrago:> insert into demo_schema.dept values (40, 'Netops', null);
Error: Optimizer failed to find a valid physical implementation for relational expression rel#34:TableModificationRel.NONE(child=HepRelVertex#44,table=[LOCALDB, DEMO_SCHEMA, DEPT],operation=INSERT,updateColumnList=[],flattened=true); see trace for partially optimized plan. Details: reason is [Node's traits (NONE) do not match required traits (ITERATOR)]; while preparing statement [insert into demo_schema.dept values (40, 'Netops', null)]. (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: Optimizer failed to find a valid physical implementation for relational expression rel#34:TableModificationRel.NONE(child=HepRelVertex#44,table=[LOCALDB, DEMO_SCHEMA, DEPT],operation=INSERT,updateColumnList=[],flattened=true); see trace for partially optimized plan. Details: reason is [Node's traits (NONE) do not match required traits (ITERATOR)]; while preparing statement [insert into demo_schema.dept values (40, 'Netops', null)].
        at net.sf.farrago.resource.FarragoResource$_Def11.ex(FarragoResource.java:1979)
        at net.sf.farrago.query.FarragoPreparingStmt.optimize(FarragoPreparingStmt.java:868)
        at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:329)
        at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:332)
        at net.sf.farrago.db.FarragoDatabase$2.initializeEntry(FarragoDatabase.java:912)
        at net.sf.farrago.util.FarragoObjectCache.tryPin(FarragoObjectCache.java:187)
        at net.sf.farrago.util.FarragoObjectCache.pin(FarragoObjectCache.java:146)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:941)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:787)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:1072)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:997)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:129)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:109)
        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)

[dynamobi-build]

[author="jvs", created="Tue, 19 Aug 2008 16:09:38 -0500 (GMT-05:00)"] First one is easy to fix (just check for null returned from getCwmColumnSet(), and return false from isGeneratedAlways in that case).

Second one requires making the external table FarragoQueryColumnSet instance return SqlAccessType.READ_ONLY from interface method getAllowedAccess() in SqlValidatorTable. But some kinds of foreign tables may support read/write access.