[jira] Created: (DERBY-4226) Column updatability wrong with explicit FOR UPDATE OF <column-list>

Column updatability wrong with explicit FOR UPDATE OF <column-list>
-------------------------------------------------------------------

                 Key: DERBY-4226
                 URL: https://issues.apache.org/jira/browse/DERBY-4226
             Project: Derby
          Issue Type: Bug
          Components: JDBC, SQL
            Reporter: Dag H. Wanvik
            Priority: Minor


The first of the two queries below allow updating column 3 in the result set;

    rs.updateInt(3, <new-value>)

 the second query does saying, giving 42X31 (embedded) and XJ124 (client).
It seems they should have the same updatability for column 3:

SELECT id,a,id from t1 for update
SELECT id,a,id from t1 for update of id

This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.

Both queries allow the first column (first instance of id) to be updated.

In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.

It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.

Note that any expressions dependent on the column are *not* recomputed, i.e.

     select id, i+4

will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Summary: Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>  (was: Column updatability wrong with explicit FOR UPDATE OF <column-list>)

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
>  the second query does saying, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Attachment: repro.diff

Uploading a patch to SURTest.java (repro.diff) which shows the issue.

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
>  the second query does saying, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Description:
The first of the two queries below allow updating column 3 in the result set;

    rs.updateInt(3, <new-value>)

the second query fails, giving 42X31 (embedded) and XJ124 (client).
It seems they should have the same updatability for column 3:

SELECT id,a,id from t1 for update
SELECT id,a,id from t1 for update of id

This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.

Both queries allow the first column (first instance of id) to be updated.

In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.

It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.

Note that any expressions dependent on the column are *not* recomputed, i.e.

     select id, i+4

will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

  was:
The first of the two queries below allow updating column 3 in the result set;

    rs.updateInt(3, <new-value>)

 the second query does saying, giving 42X31 (embedded) and XJ124 (client).
It seems they should have the same updatability for column 3:

SELECT id,a,id from t1 for update
SELECT id,a,id from t1 for update of id

This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.

Both queries allow the first column (first instance of id) to be updated.

In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.

It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.

Note that any expressions dependent on the column are *not* recomputed, i.e.

     select id, i+4

will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
> the second query fails, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Affects Version/s: 10.2.1.6
                       10.2.2.0
                       10.3.1.4
                       10.3.2.1
                       10.3.3.0
                       10.4.1.3
                       10.4.2.0
                       10.5.1.1

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
> the second query fails, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Affects Version/s: 10.1.1.0
                       10.1.2.1
                       10.1.3.1

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
> the second query fails, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12708568#action_12708568 ]

Dag H. Wanvik commented on DERBY-4226:
--------------------------------------

Found this behavior back to 10.1 for forward only result sets; in 10.0 result sets were not updatable.
From 10.2 and later, it applies to scroll insensitive result sets as well.

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
> the second query fails, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4226:
---------------------------------

    Issue & fix info: [Repro attached]
             Urgency: Normal

Triaged for 10.5.2, "repro attached" and setting "normal" urgency.

> Column updatability wrong in corner case with explicit FOR UPDATE OF <column-list>
> ----------------------------------------------------------------------------------
>
>                 Key: DERBY-4226
>                 URL: https://issues.apache.org/jira/browse/DERBY-4226
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
>            Reporter: Dag H. Wanvik
>            Priority: Minor
>         Attachments: repro.diff
>
>
> The first of the two queries below allow updating column 3 in the result set;
>     rs.updateInt(3, <new-value>)
> the second query fails, giving 42X31 (embedded) and XJ124 (client).
> It seems they should have the same updatability for column 3:
> SELECT id,a,id from t1 for update
> SELECT id,a,id from t1 for update of id
> This is the case both for TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE result sets.
> Both queries allow the first column (first instance of id) to be updated.
> In the cases that work, if the rs is scroll insensitive, *both* instances of id are updated and will show the new value if read back after updateRow has been performed.
> It is not obvious, perhaps, if updating column 3 should be allowed (if so, only if column 1 is not updated?)
> or, maybe the behavior should be that only the column that is actually updated (in a scrollable insensitive result set) should be updated, i.e. any other instances would not reflect the change unless they are themselves updated.
> Note that any expressions dependent on the column are *not* recomputed, i.e.
>      select id, i+4
> will not show a changed value of id+4 in an updatable scrollable result set, if id is updated.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.