Issue Details (XML | Word | Printable)

Key: CORE-4694
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

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

"Column unknown" error while preparing a recursive query if the recursive part contains ALIASED datasource in the join with anchor table

Created: 18/Feb/15 03:34 PM   Updated: 23/Sep/15 11:33 AM
Component/s: Engine
Affects Version/s: 3.0 Alpha 1, 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

QA Status: Done successfully
Test Details: Fixed on 3.0 since rev 60747, 2015-02-20 16:56


 Description  « Hide
DDL:
====
show version;

recreate table xcall_stack (
    xcall_id int
    ,xcaller_id int
);
commit;

set planonly;

with recursive
r as (
    select c.xcall_id
    from xcall_stack c
    where c.xcaller_id is null

    UNION ALL

    select
           c.xcall_id
    from xcall_stack c
    join r
      AS h -- <<<<<<<<<<<<<<<<<<<<<< ::: NB ::: `r` is aliased
      on c.xcaller_id = h.xcall_id
)
select r.xcall_id
from r;


Test on 2.5 (passed OK):
=========

ISQL Version: WI-V2.5.4.26838 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.4.26838 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.4.26838 Firebird 2.5/tcp (csprog)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.4.26838 Firebird 2.5/tcp (csprog)/P12"
on disk structure version 11.2

PLAN (R C NATURAL)
PLAN (R C NATURAL)

Test on 3.0:
=========

ISQL Version: WI-T3.0.0.31655 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31655 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31655 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31655 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0
Statement failed, SQLSTATE = 42S22
Dynamic SQL Error
-SQL error code = -206
-Column unknown
-H.XCALL_ID
-At line 17, column 6
At line 26 in file rec-broken.sql


PS. No error message if recursive part looks like this:
. . .
    UNION ALL

    select c.xcall_id
    from xcall_stack c
    join r -- <<<<<<<<<<<<<<<<<<<<<<<<<<< NO alias here for `r`
      on c.xcaller_id = r.xcall_id
. . .

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 18/Feb/15 03:50 PM
PS. At least on WI-T3.0.0.31529 this query still worked OK

Dmitry Yemanov added a comment - 20/Feb/15 04:54 PM
This was a transient snapshot-only bug, so I don't set the version information in the ticket.