I have a table which has an mfd_id column and a btree index on that column. The records in that table are editable.
When I select some records in that table and then return those records to a results pane using SELECT * FROM CALL SELECTION([my_table], TRUE); the records are editable. If I add ORDER BY mfd_id to the end of that query, the results are editable. Sweet.
If instead I ORDER BY any other column, the results are not editable.
Is this by design? It would be great to be able to edit ordered records returned by simple queries.
I wonder why the BTREE on mfd_id is destroyed if we ORDER by another column.
What we have is instead a BTREEDUPNULL on the column(s) we have ORDERed by.
I would have expected the original BTREE to have survived the reordering, since reordering cannot make previously unique values non-unique.
To me this looks like a bug.
Having thought about it a bit more, I strongly doubt that this is a bug.
It might be a side effect of an efficient implementation of BTREE index in Radian, for example that index values are "co-monotonic" to position in physical storage. By "co-monotonic" (probably not a proper word) I just mean that as one increases, the other also increases. This (fairly loose) relation would mean never having to search backwards from a given index, only forwards (or vice versa), which would be a huge saving.
If something like that is true, then the same table sorted by a different column would in principle (and in fact, except by luck) be a different table. The co-monotonic relation between BTREE index value and stored position would break, so the BTREE index would become invalid.
Yes, the behavior is by design and is not a bug.
ORDER is free to expose no indexes on the result table. We do try to expose an index on the ordering criteria because that's useful, but even that is not a given because the ordering might be done on things that aren't even included in the result table (expressions), so there is nothing to tie the index to. In some cases we can preserve indexes on the original table, so we do that. In other cases, preserving indexes on the original table is too expensive, so we don't do that. We will consider preserving more indexes to make the result table of ORDER editable more often in the future. Maybe we can do something useful here under reasonable limitations (eg, just for tables in MAP files).
As ever, I understand little about the sausage-making going on when I'm manipulating data in 9. I've been trying to migrate my data manipulation workflows over from 8, and realize that it's not necessarily the goal to have 9 "do everything" that 8 does. For the most part I've been able to adjust to 9.
Certain things like this are a frustration. The query results contain the btree index column, free from joins and duplication. Ordering the records shouldn't (in my opinion) affect the ability to modify a row any more than ordering the underlying table by a non-indexed column should.
In any case, low priority, and certainly I've been enjoying the power of 9 in other areas. Thanks for the hard work.