Skip to content
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

Open
firebird-automations opened this issue Apr 23, 2007 · 15 comments
Open

Possible bug with CURRENT OF [CORE1224] #1648

firebird-automations opened this issue Apr 23, 2007 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I agree this is a bug, but to fix it needs some changes that break compatibility.
We don't use aliases of cursors in UPDATE/DELETE.

@firebird-automations
Copy link
Collaborator Author

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".

@firebird-automations
Copy link
Collaborator Author

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.
It should be the contrary.

This is the bug that prevents the improvement to be done without break compatibility.

@firebird-automations
Copy link
Collaborator Author

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.
Maybe it's worth to allow joins in general, but reject the ones that refer to a single table multiple times (under different aliases)?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Maybe... but in this case we extend the problem and it will be really difficult to support correct alias handling there.
Since nobody can use cursors with joins and DELETE/UPDATE currently, seems more appropriate IMHO to change the alias logic once.

I suppose there is not too much people doing this now:
for select ... from t a as cursor c
delete from t where current of c;

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Imagine this:

DECLARE CURSOR C FOR ( SELECT ... FROM TAB1 T1 JOIN TAB2 T2 ... );
...
DELETE FROM T2 WHERE CURRENT OF C;

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

Yet one problem. Server (2.0.1) crashes on next query:

EXECUTE BLOCK
AS
DECLARE ID INTEGER;
DECLARE CUR CURSOR FOR (SELECT http://TABLE1.ID FROM TABLE1, TABLE2 WHERE http://TABLE1.ID = http://TABLE2.ID);
BEGIN
OPEN CUR;

WHILE (1 = 1) DO
BEGIN
FETCH CUR INTO :ID;
DELETE FROM TABLE1 WHERE CURRENT OF CUR;
END

CLOSE CUR;
END

error is usual for me :-)

TUT (Client) Thu Apr 26 11:12:23 2007
C:\Program Files\Firebird\Firebird_2_0\bin\fbserver.exe: terminated abnormally (4294967295)

@firebird-automations
Copy link
Collaborator Author

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".

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

Also do not forget about queries with UNION (don't want compile too, even without aliases)

EXECUTE BLOCK
AS
DECLARE ID INTEGER;
BEGIN
FOR
SELECT ID FROM TABLE1
UNION ALL
SELECT ID FROM TABLE1
INTO :ID
AS CURSOR CUR
DO
DELETE FROM TABLE1 WHERE CURRENT OF CUR;
END

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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
AS
DECLARE DBKEY1 CHARACTER(8) CHARACTER SET OCTETS;
DECLARE DBKEY2 CHARACTER(8) CHARACTER SET OCTETS;
BEGIN
FOR
SELECT RDB$DB_KEY, NULL FROM TABLE1
UNION ALL
SELECT NULL, RDB$DB_KEY FROM TABLE2
INTO :DBKEY1, :DBKEY2
AS CURSOR CUR
DO BEGIN
IF (DBKEY1 IS NOT NULL) THEN
DELETE FROM TABLE1 WHERE RDB$DB_KEY = :DBKEY1;
IF (DBKEY2 IS NOT NULL) THEN
DELETE FROM TABLE2 WHERE RDB$DB_KEY = :DBKEY2;
END
END

I think such logic can be used for WHERE CURRENT OF too. What do you think?

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

PS This was equal to

EXECUTE BLOCK
AS
DECLARE ID INT;
BEGIN
FOR
SELECT ID FROM TABLE1
UNION ALL
SELECT ID FROM TABLE2
INTO :ID
AS CURSOR CUR
DO BEGIN
DELETE FROM TABLE1 WHERE CURRENT OF CUR;
DELETE FROM TABLE2 WHERE CURRENT OF CUR;
END
END

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11921 ] => Firebird [ 15005 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants