Cannot edit this cell. Details: The query execution mechanism had problems with extracting ROWID's properly. This might be a bug in the application. You may want to report this.

[felipelalli]

Details

When I try to edit a cell in a view:

[03:49:25] Cannot edit this cell. Details: The query execution mechanism had problems with extracting ROWID's properly. This might be a bug in the application. You may want to report this.

Steps to reproduce

Open a view and try to edit a cell with number / date.

Operating system

Linux / Debian

SQLiteStudio version

3.4.4

[pawelsalawa]

Would you be willing to share the query used in the view?

[felipelalli]

SELECT t1.id AS table1_id,
           t2.id AS table2_id,
           t3.id AS table3_id,
           t4.*,
           t1.detail AS table1_detail,
           t1.title AS table1_title,
           t1.creation_date,
           t1.publish_date,
           t1.file_path,
           t1.image_path,
           t1.key,
           t1.labels,
           t2.raw_data
      FROM table1 t1
           JOIN
           table2 t2 ON t1.linked_to_t2 = t2.id
           JOIN
           table3 t3 ON t2.linked_to_t3 = t3.id
           JOIN
           v_full_table4 t4 ON t3.web_link = t4.link
     WHERE t1.status = 1
     ORDER BY t1.id DESC

But today I tried again and could not reproduce the bug anymore. I don't know why. I just restarted the application and now I am able to edit the row.

[felipelalli]

@pawelsalawa I found the issue. I was able to reproduce it.

The problem is when the view query another view:

CREATE VIEW v_unsent AS
    SELECT *
      FROM v_full_episodes
     WHERE upload_date IS NULL AND 
           schedule_date IS NULL;

This view I can't edit.

[pawelsalawa]

I guess (from its name) that the v_full_episodes is also a view? Could you share its query?

[felipelalli]

@pawelsalawa v_full_episodes is this view: https://github.com/pawelsalawa/sqlitestudio/issues/4831#issuecomment-1637006028

I just have changed some names for privacy.

[pawelsalawa]

Okay, got it. I'll try to reproduce it, but I'm not sure whether it will be possible just like that. Sometimes there are some specific circumstances.

I guess it won't be possible to share your database? Could be done privately to project's email support@sqlitestudio.pl if that helps.

[felipelalli]

Hi @pawelsalawa ! I'll send you an email. Thank you.

[felipelalli]

@pawelsalawa I found a case where an edit on view is not allowed and the query is not another view. I'll send privately to you.

[pawelsalawa]

It seems the problem is that the attempt to edit multi-level view is not allowed and currently this is expected. The real problem here is that the error message is misleading, because it doesn't state the problem underneath.

[felipelalli]

Ok! Sad. Thank you!

[pawelsalawa]

I'm back to this issue now and unfortunately I'm unable to find the database you sent me :( Is there a chance you can reproduce this still?

[felipelalli]

I sent you a private email.

[pawelsalawa]

Do you perhaps remember file name? I said I would keep it, so I wonder if I have it somewhere...

[pawelsalawa]

Okay, nevermind. I found it!

[pawelsalawa]

I've fixed the warning message for this particular case to clearly state, that multi-level View's result cannot be edited. This limitation is necessary, because in the past it turned out that people write humongous views, which use 30 other views and each of them use other or same views... In result this caused a lot of errors with identifying correct tables for which the value should be edited.

The actual solution for editing complex, multilevel views is to use INSTEAD OF triggers and support for that is planned in 3.5.0: #4628

[felipelalli]

Thank you! If I understood correctly, only the error message will change but the problem will not be fixed, is that it?

[pawelsalawa]

Yes, although it's not like I'm avoiding the problem. It's just there is a better solution to this problem of editing view's data as mentioned in #4628, but it is significantly bigger code change & effort, while 3.4.5 version will be mostly about bugfixing and to deliver it in foreseeable future, I should stick to bugfixing for this version and then 3.5.0 will bring bigger features.

[felipelalli]

Nice! Thank you so much and congrats for this amazing project and your big effort.