Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.3.0-incubating
    • Component/s: None
    • Labels:

      Description

      Phoenix allows updates to occur through a VIEW in certain, simple cases (i.e. when the WHERE clause of a VIEW contains only simple equality statements). It'd be useful if Calcite allowed for this as well. See http://phoenix.apache.org/views.html#Updatable_Views for more information.

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

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

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.3.0-incubating (2015-05-30).
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/bc45a2c0 .
        Hide
        julianhyde Julian Hyde added a comment - - edited

        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.

        Show
        julianhyde Julian Hyde added a comment - - edited 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 .
        Hide
        jamestaylor James Taylor added a comment -

        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?

        Show
        jamestaylor James Taylor added a comment - 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?
        Show
        julianhyde Julian Hyde added a comment - Complete at https://github.com/julianhyde/incubator-calcite/tree/505-modifiable-view . James Taylor or Maryann Xue can you please review?
        Hide
        julianhyde Julian Hyde added a comment -

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

        Show
        julianhyde Julian Hyde added a comment - Started work in https://github.com/julianhyde/incubator-calcite/tree/505 but don't think it will be done before 1.2.
        Hide
        julianhyde Julian Hyde added a comment -

        Thanks. That clarifies.

        Show
        julianhyde Julian Hyde added a comment - Thanks. That clarifies.
        Hide
        jamestaylor James Taylor added a comment -

        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;
        
        Show
        jamestaylor James Taylor added a comment - 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;
        Hide
        julianhyde Julian Hyde added a comment -

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

        Show
        julianhyde Julian Hyde added a comment - What happens? Does it insert only those rows that match the criteria? Does it silently drop the others, or does it give an error?
        Hide
        jamestaylor James Taylor added a comment -

        Phoenix prevents this.

        Show
        jamestaylor James Taylor added a comment - Phoenix prevents this.
        Hide
        julianhyde Julian Hyde added a comment - - edited

        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.

        Show
        julianhyde Julian Hyde added a comment - - edited 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.

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            jamestaylor James Taylor
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development