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

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

Closed
firebird-automations opened this issue Feb 18, 2015 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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

. . .

Commits: 3ff5513 FirebirdSQL/fbt-repository@273af87

====== Test Details ======

Fixed on 3.0 since rev 60747, 2015-02-20 16:56

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PS. At least on WI-T3.0.0.31529 this query still worked OK

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This was a transient snapshot-only bug, so I don't set the version information in the ticket.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Can`t prepare recursive query if recursive part contains ALIASED datasource in the join with anchor table => "Column unknown" error while preparing a recursive query if the recursive part contains ALIASED datasource in the join with anchor table

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Fix Version: 3.0 Alpha 2 [ 10560 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Version: 3.0 Alpha 2 [ 10560 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 2 [ 10560 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

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