Issue Details (XML | Word | Printable)

Key: CORE-1224
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alexander Tyutik
Votes: 3
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Possible bug with CURRENT OF

Created: 23/Apr/07 12:57 PM   Updated: 20/Oct/09 03:35 AM
Component/s: None
Affects Version/s: 2.0.1
Fix Version/s: None


 Description  « Hide
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 T1.ID FROM TABLE1 T1
    JOIN TABLE2 T2 ON T2.ID = T1.ID
    INTO :ID
    AS CURSOR CUR
  DO
    DELETE FROM TABLE1 WHERE CURRENT OF CUR;
END

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 23/Apr/07 09:32 PM
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.

Dmitry Yemanov added a comment - 24/Apr/07 12:24 AM
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".

Adriano dos Santos Fernandes added a comment - 24/Apr/07 09:43 AM
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.

Dmitry Yemanov added a comment - 25/Apr/07 02:03 AM
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)?

Adriano dos Santos Fernandes added a comment - 25/Apr/07 07:48 PM
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;

Dmitry Yemanov added a comment - 26/Apr/07 03:23 AM
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.

Alexander Tyutik added a comment - 26/Apr/07 03:36 AM
Yet one problem. Server (2.0.1) crashes on next query:

EXECUTE BLOCK
AS
  DECLARE ID INTEGER;
  DECLARE CUR CURSOR FOR (SELECT TABLE1.ID FROM TABLE1, TABLE2 WHERE TABLE1.ID = 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)


Dmitry Yemanov added a comment - 26/Apr/07 03:52 AM
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".

Alexander Tyutik added a comment - 26/Apr/07 03:56 AM
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.

Dmitry Yemanov added a comment - 26/Apr/07 04:56 AM
I hope you understand that it can work for unions *only* if the table you delete from exists in all union parts.

Alexander Tyutik added a comment - 26/Apr/07 05:38 AM
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?

Alexander Tyutik added a comment - 26/Apr/07 05:45 AM
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

Axel Luessow added a comment - 26/Aug/08 01:09 PM
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.