New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Possible bug with CURRENT OF [CORE1224] #1648
Comments
Commented by: @asfernandes I agree this is a bug, but to fix it needs some changes that break compatibility. |
Commented by: @dyemanov Adriano, I'm not sure I understand your point. Why do you speak about aliases? Looking at dsql/pass1.cpp, comment at line 3715 clearly shows why this cursor reference doesn't work. We just don't support WHERE CURRENT OF for joins. So I'd change the ticket type to "improvement". |
Commented by: @asfernandes Yes, ok. But you may see that even for one table with alias, the DELETE should use the table name and can't use the alias. This is the bug that prevents the improvement to be done without break compatibility. |
Commented by: @dyemanov Ah, I see the point now, thanks. I agree it's a problem. But DELETE FROM <table alias> would look weird, to say at least. |
Commented by: @asfernandes Maybe... but in this case we extend the problem and it will be really difficult to support correct alias handling there. I suppose there is not too much people doing this now: |
Commented by: @dyemanov Imagine this: DECLARE CURSOR C FOR ( SELECT ... FROM TAB1 T1 JOIN TAB2 T2 ... ); I don't like such SQL, as it's completely unreadable. Using aliases in DELETE/UPDATE violates the standard as well. So I'm against it. I had a look at MSSQL. It doesn't allow aliases in DELETE/UPDATE. It does treat join-based cursors as updateable and allows DELETE/UPDATE to reference any of the joined tables. But if some table is referenced multiple times in the cursor, DELETE/UPDATE will work only against its first instance (and an appropriate warning is thrown). I'd want us to either follow the MSSQL's way or disallow DELETE/UPDATE against cursors having ambiguous table references (as I suggested earlier). It behaves consistently and doesn't cause any compatibility issues. |
Commented by: Alexander Tyutik (tut) Yet one problem. Server (2.0.1) crashes on next query: EXECUTE BLOCK WHILE (1 = 1) DO CLOSE CUR; error is usual for me :-) TUT (Client) Thu Apr 26 11:12:23 2007 |
Commented by: @dyemanov First, please don't mix different bugs in a single ticket. Second, v2.1 doesn't crash but throws "no current record for fetch operation". |
Commented by: Alexander Tyutik (tut) Also do not forget about queries with UNION (don't want compile too, even without aliases) EXECUTE BLOCK Also it would be nice if all of this queries will works with views too, bacause for tables we can use RDB$DB_KEY instead of CURSOR, and for views it can be not so easy. |
Commented by: @dyemanov I hope you understand that it can work for unions *only* if the table you delete from exists in all union parts. |
Commented by: Alexander Tyutik (tut) I don't want to use such queries in my practice. I only want you kill all possible holes. From other side what if we will look at this query such way: EXECUTE BLOCK I think such logic can be used for WHERE CURRENT OF too. What do you think? |
Commented by: Alexander Tyutik (tut) PS This was equal to EXECUTE BLOCK |
Modified by: @pcisarWorkflow: jira [ 11921 ] => Firebird [ 15005 ] |
Commented by: Axel Luessow (visualx) At the moment, I am trying to move from MS SQL Server to embeded Firebird. Unfortunately, MFC's CDatabase requires CURRENT OF CUR for DELETE and UPDATE operations, thus this bug is the single blocking problem I am facing. |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Submitted by: Alexander Tyutik (tut)
Votes: 3
I've got next error:
Invalid cursor reference. Relation TABLE1 is not found in cursor CUR.
on next query:
EXECUTE BLOCK
AS
DECLARE ID INTEGER;
BEGIN
FOR SELECT http://T1.ID FROM TABLE1 T1
JOIN TABLE2 T2 ON http://T2.ID = http://T1.ID
INTO :ID
AS CURSOR CUR
DO
DELETE FROM TABLE1 WHERE CURRENT OF CUR;
END
The text was updated successfully, but these errors were encountered: