Symptom
- A join query with group by clause may return an incorrect result set if a scalar/vector aggregate operator is placed under a join operator.
- here is the query which returns 0 row, but it should return 2 rows
if bs_11 of TEST_BS and pt_01 of TEST_PT are tinyint, smallint, numeric(9) or decimal(9).
1> select BS.bs_04 , BS.bs_11
2> from TEST_PT PT , TEST_BS BS
3> where BS.bs_11 = 17
4> and BS.bs_11 = PT.pt_01
5> and PT.pt_02 = 3
6> group by BS.bs_04, BS.bs_11
7> go
bs_04 bs_11
------ -----
(0 rows affected)
if bs_11 of TEST_BS and pt_01 of TEST_PT are int, numeric(10) or decimal(10), the above query returns 2 rows correctly.
1> select BS.bs_04 , BS.bs_11
2> from TEST_PT PT , TEST_BS BS
3> where BS.bs_11 = 17
4> and BS.bs_11 = PT.pt_01
5> and PT.pt_02 = 3
6> group by BS.bs_04, BS.bs_11
7> go
bs_04 bs_11
------ -----------
010 17
009 17
(2 rows affected)
Read more...
Environment
Adaptive Server Enterprise (ASE):
- SAP Adaptive Server Enterprise (ASE) 15.7
- SAP Adaptive Server Enterprise (ASE) 16.0
Platforms:
- AIX 64bit
- HP-UX on IA64 64bit
- Linux on Power 64bit
- Linux on x86 64 64bit
- Solaris on Sparc 64bit
- Solaris on x86 64 64bit
- Windows on x64 64bit
Product
Keywords
Hash aggregate, join operator, scalar, vector, HashvectAgg operator, NLJ operator, CR726439, CR#726439, 726439 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.