HAVING COUNT(*) NOT IN ( <Q> ) prevent record from appearing in outer resultset
when it should be there (<Q> = resultset without nulls)
---------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5165
URL: http://tracker.firebirdsql.org/browse/CORE-5165
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 4.0 Initial, 3.0 RC2, 2.5.5
Reporter: Pavel Zotov
(created as separate ticket after Adriano suggestion; text below was moved here
from CORE-5153)
Consider following sample:
set list on;
set count on;
select 1 as check_ok
from rdb$database r
group by r.rdb$relation_id
having count(*) not in (select -1 from rdb$database r2)
;
NOT-IN subquery has not NULLs so HAVING clause should NOT prevent from
returning one row.
But results differ on 2.5 vs (3.0 RC2 and 4.0 Unstable):
C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb25\bin\isql /:e25 -z -i
c5153x.sql
ISQL Version: WI-V2.5.6.26980 Firebird 2.5
Server version:
WI-V2.5.6.26980 Firebird 2.5
WI-V2.5.6.26980 Firebird 2.5/tcp (csprog)/P12
WI-V2.5.6.26980 Firebird 2.5/tcp (csprog)/P12
CHECK_OK 1
Records affected: 1
C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql /:e30 -z -i c5153x.sql
ISQL Version: WI-V3.0.0.32383 Firebird 3.0 Release Candidate 2
Server version:
WI-V3.0.0.32383 Firebird 3.0 Release Candidate 2
WI-V3.0.0.32383 Firebird 3.0 Release Candidate 2/tcp (csprog)/P13
WI-V3.0.0.32383 Firebird 3.0 Release Candidate 2/tcp (csprog)/P13
Records affected: 0
C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb40\isql /:e40 -z -i c5153x.sql
ISQL Version: WI-T4.0.0.32400 Firebird 4.0 Unstable
Server version:
WI-T4.0.0.32400 Firebird 4.0 Unstable
WI-T4.0.0.32400 Firebird 4.0 Unstable/tcp (csprog)/P13
WI-T4.0.0.32400 Firebird 4.0 Unstable/tcp (csprog)/P13
Records affected: 0
PS. Versions: WI-V3.0.0.32383 and WI-T4.0.0.32400 - were downloaded from
snapshot directory just now:
C:\1TMP>dir /-c Firebird-3.0.0.32383-0_Win32.7z Firebird-4.0.0.32400-0_Win32.7z
|findstr /i fir
23.03.2016 16:48 6562751 Firebird-3.0.0.32383-0_Win32.7z
23.03.2016 16:46 5401474 Firebird-4.0.0.32400-0_Win32.7z
Unfortunately, we have no more (or not yet ?) subsequent number of builds after
moving on git, so they are the same as before.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785351&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
