Should the server allow people to insert (or upsert) rows into an updatable view that will not come out again?

create view female_emps as select * from emps where gender = 'F';
insert into female_emps (100, 'Leslie', 'M');

Oracle has CREATE VIEW ... WITH CHECK OPTION for this.

Phoenix prevents this.

What happens? Does it insert only those rows that match the criteria? Does it silently drop the others, or does it give an error?

Phoenix has a pretty conservative compile-time check that'll throw a SQLException if it's being set to something other than the constant value (or if we can't know what it's set to at compile time). For example, the following would throw:

upsert into female_emps select * from emps;

Phoenix will set the column value to the constant, though, when it's not present. For example, this would work (and set the gender column to 'F' for the upserted rows):

upsert into female_emps(emp_id, name) select emp_id, name from emps where salary > 10;

Thanks. That clarifies.

Started work in https://github.com/julianhyde/incubator-calcite/tree/505 but don't think it will be done before 1.2.

Complete at https://github.com/julianhyde/incubator-calcite/tree/505-modifiable-view. James Taylor or Maryann Xue can you please review?

Awesome, Julian Hyde. Thanks so much for adding support for this. Is there a good way, given an updatable view, of iterating through the columns and their "fixed" values?

Like this:

final Map<Integer, RexNode> projectMap = new HashMap<>();
final List<RexNode> filters = new ArrayList<>();
final RexNode constraint =
    modifiableView.getConstraint(rexBuilder, delegateRowType);
RelOptUtil.inferViewPredicates(projectMap, filters, constraint);

After executing this code, projectMap.get( i ) will contain a RexLiteral if column #i is constrained to a constant, null otherwise. You can see a use of this code in SqlToRelConverter#createSource.

Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/bc45a2c0.

Resolved in release 1.3.0-incubating (2015-05-30).