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.

Sign up for GitHub

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

Jump to bottom

Subquery in CASE [CORE1200] #1625

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

Subquery in CASE [CORE1200] #1625

firebird-automations opened this issue Apr 8, 2007 · 8 comments
Assignees
@dyemanov
Labels
affect-version: 2.0.1 component: engine priority: minor type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Matyas Novak (logik)

Votes: 1

Complex query failed on error:
"ISC ERROR CODE:335544343
invalid request BLR at offset 282
context already in use (BLR error)"
The complex query contains subquery in case statement. If I replace
the subquery by constant no error is reported, same as if I try run
the subquery separately.

SQL Script:

CREATE TABLE registracnikategorie (
RegistracniKategorieID integer NOT NULL PRIMARY KEY,
Nazev VARCHAR(32) NOT NULL COLLATE WIN1250,
Rok NUMERIC(4) default 0000 ,
JednotkaID NUMERIC(18) NOT NULL,
Typ INTEGER default 0 Check (Typ IN (0,1,2)),
UNIQUE (JednotkaID,Rok,Nazev)
);

CREATE TABLE clenstvi (
ClenstviID integer NOT NULL PRIMARY KEY,
JednotkaID NUMERIC(18) NOT NULL,
OsobaID integer NOT NULL,
Typ integer NOT NULL,
Vznik DATE ,
Zanik DATE
);

select Nazev, RegistracniKategorieId
FROM RegistracniKategorie
WHERE
JednotkaID = :JednotkaID AND
Rok=:Rok AND
Typ <=
(CASE
(
SELECT MAX(Typ) FROM Clenstvi
WHERE OsobaID = :OsobaID AND
JednotkaID = :JednotkaID AND
(Vznik IS NULL OR EXTRACT(Year FROM Vznik) <= :Rok) AND
(Zanik IS NULL OR EXTRACT(Year FROM Zanik) >= :Rok)
)
WHEN 2 THEN IIF(EXISTS(SELECT RegistracniKategorieID FROM registracniKategorie WHERE JednotkaId = :JednotkaID AND Rok =:Rok AND Typ=1),2,3)
WHEN 3 THEN 3
ELSE 1
END
)
@firebird-automations
Copy link
Collaborator Author

Modified by: Matyas Novak (logik)

description: Complex query failed on error:
"ISC ERROR CODE:335544343
invalid request BLR at offset 282
context already in use (BLR error)"
The complex query contains subquery in case statement. If I replace
the subquery by constant no error is reported, same as if I try run
the subquery separately.

Tables:
CREATE TABLE registracnikategorie (
RegistracniKategorieID integer NOT NULL PRIMARY KEY,
Nazev VARCHAR(32) NOT NULL COLLATE WIN1250,
Rok NUMERIC(4) default 0000 ,
JednotkaID TJednotkaID NOT NULL,
Typ INTEGER default 0 Check (Typ IN (0,1,2));
UNIQUE (JednotkaID,Rok,Nazev)DATE CASCADE
);

CREATE TABLE clenstvi (
ClenstviID integer NOT NULL PRIMARY KEY,
JednotkaID TJednotkaID NOT NULL,
OsobaID integer NOT NULL,
Typ integer NOT NULL,
Vznik DATE ,
Zanik DATE
);

select Nazev, RegistracniKategorieId
FROM RegistracniKategorie
WHERE
JednotkaID = :JednotkaID AND
Rok=:Rok AND
Typ <=
(CASE
(
SELECT MAX(Typ) FROM Clenstvi
WHERE OsobaID = :OsobaID AND
JednotkaID = :JednotkaID AND
(Vznik IS NULL OR EXTRACT(Year FROM Vznik) <= :Rok) AND
(Zanik IS NULL OR EXTRACT(Year FROM Zanik) >= :Rok)
)
WHEN 2 THEN IIF(EXISTS(SELECT RegistracniKategorieID FROM registracniKategorie WHERE JednotkaId = :JednotkaID AND Rok =:Rok AND Typ=1),2,3)
WHEN 3 THEN 3
ELSE 1
END
)

=>

Complex query failed on error:
"ISC ERROR CODE:335544343
invalid request BLR at offset 282
context already in use (BLR error)"
The complex query contains subquery in case statement. If I replace
the subquery by constant no error is reported, same as if I try run
the subquery separately.

SQL Script:

CREATE TABLE registracnikategorie (
RegistracniKategorieID integer NOT NULL PRIMARY KEY,
Nazev VARCHAR(32) NOT NULL COLLATE WIN1250,
Rok NUMERIC(4) default 0000 ,
JednotkaID NUMERIC(18) NOT NULL,
Typ INTEGER default 0 Check (Typ IN (0,1,2)),
UNIQUE (JednotkaID,Rok,Nazev)
);

CREATE TABLE clenstvi (
ClenstviID integer NOT NULL PRIMARY KEY,
JednotkaID NUMERIC(18) NOT NULL,
OsobaID integer NOT NULL,
Typ integer NOT NULL,
Vznik DATE ,
Zanik DATE
);

select Nazev, RegistracniKategorieId
FROM RegistracniKategorie
WHERE
JednotkaID = :JednotkaID AND
Rok=:Rok AND
Typ <=
(CASE
(
SELECT MAX(Typ) FROM Clenstvi
WHERE OsobaID = :OsobaID AND
JednotkaID = :JednotkaID AND
(Vznik IS NULL OR EXTRACT(Year FROM Vznik) <= :Rok) AND
(Zanik IS NULL OR EXTRACT(Year FROM Zanik) >= :Rok)
)
WHEN 2 THEN IIF(EXISTS(SELECT RegistracniKategorieID FROM registracniKategorie WHERE JednotkaId = :JednotkaID AND Rok =:Rok AND Typ=1),2,3)
WHEN 3 THEN 3
ELSE 1
END
)

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The error the SQL is bad.

The SQL has 2 references to the "registracniKategorie" but without alias -- in order to have 2 distinct references, you need to use aliases.

This is expected behaviour.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Matyas Novak (logik)

I don't think so
1) If I omit inner subquery with clenstvi, query works, try:

select Nazev, RegistracniKategorieId
FROM RegistracniKategorie
WHERE
JednotkaID = :JednotkaID AND
Rok=:Rok AND
Typ <=
(CASE
(2)
WHEN 2 THEN IIF(EXISTS(SELECT RegistracniKategorieID FROM registracniKategorie WHERE JednotkaId = :JednotkaID AND Rok =:Rok AND Typ=1),2,3)
WHEN 3 THEN 3
ELSE 1
END
)

2) IMHO aliases in subqueries shouldn't be required, inner table should be preffered. I'm wrong?
(At least this query reports no error:
select * from registracnikategorie where typ = (select max(typ) from registracnikategorie)
)

3) The query dosen't work even with aliases are specified. , try:

select r1.Nazev, r1.RegistracniKategorieId
FROM RegistracniKategorie r1
WHERE
r1.JednotkaID = :JednotkaID AND
r1.Rok=:Rok AND
r1.Typ <=
(CASE
(
SELECT MAX(Clenstvi.Typ) FROM Clenstvi
WHERE Clenstvi.OsobaID = :OsobaID AND
Clenstvi.JednotkaID = :JednotkaID AND
(Clenstvi.Vznik IS NULL OR EXTRACT(Year FROM Clenstvi.Vznik) <= :Rok) AND
(Clenstvi.Zanik IS NULL OR EXTRACT(Year FROM Clenstvi.Zanik) >= :Rok)
)
WHEN 2 THEN IIF(EXISTS(SELECT r2.RegistracniKategorieID FROM registracniKategorie r2 WHERE r2.JednotkaId = :JednotkaID AND r2.Rok =:Rok AND r2.Typ=1),2,3)
WHEN 3 THEN 3
ELSE 1
END
)

Best regards
Matyas

PS: Even if the query was wrong, the error message should be different (clearer).

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

assignee: Dmitry Yemanov [ dimitr ]

resolution: Won't Fix [ 2 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Valderrama C. (robocop)

This entry has nothing to do with isql, hence I assigned it to "Engine" instead.

@firebird-automations
Copy link
Collaborator Author

Modified by: Claudio Valderrama C. (robocop)

Component: Engine [ 10000 ]

Component: ISQL [ 10003 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11749 ] => Firebird [ 15540 ]

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

@dyemanov dyemanov

Labels
affect-version: 2.0.1 component: engine priority: minor type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@dyemanov @firebird-automations