problems when using case, left join and grouping

[fabricebourdel]

Hi,

I maybe found something doing not the right result : is it a case that you

know about or not ?

when using a query with a few joins, 1 right join, a case instruction and a group by,

the result is not grouped correctly (not at all).

when transforming the right join in natural join or deleting the case instruction, it's ok.

Greets

[holger_becker]

Hi,

this is probably a known problem with joins and complex commands including group by.

The problem will be solved with version 7.6.5.14

(see http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1159719)

As a workaround you could use 'distinct' instead of 'group by'.

Thanks for reporting the problem and sorry for any inconvenience.

Kind regards

Holger

[fabricebourdel]

ok,

Thanks Holger for the response (and Lars too), but i prefer wait the next version, because in the case i had to send i small part of the database i use, it will need a few hours to extract the necessary datas, and i'm a bit short with time at the momenet.

Cheers

[fabricebourdel]

oups, forgottent to specify the version of maxdb used : linux maxdb 64 7.6.05.09

[lbreddemann]

Hi Fabrice,

please provide the explain plan output of the statement.

Was Queryrewrite active? (Parameter OPTIMIZE_QUERYREWRITE = OPERATOR).

regards,

Lars

[fabricebourdel]

Hi Lars,

The OPTIMIZE_QUERYREWRITE is on OPERATOR. I Checked if switching to NO OR STATEMENT changed something : the response is no.

Here is the query plan :

IMTR	IND_IMTR_FK_IMGR_PER_CALC	EQUAL CONDITION FOR INDEX	        24
	IMGR_PER_CALC	     (USED INDEX COLUMN)	
IMDO	IND_IMDO_FK_IMTR_ID	JOIN VIA INDEXED COLUMN	    182613
	IMTR_ID	     (USED INDEX COLUMN)	
SORELIPR	SORE_ID	JOIN VIA KEY COLUMN	      1382
SORE	SORE_ID	JOIN VIA KEY COLUMN	      4834
IDTC	IDTC_ID	JOIN VIA KEY COLUMN	     25041
SOREAC		JOIN VIA RANGE OF MULTIPLE KEY COLUMNS	      2106
	SORE_ID	     (USED KEY COLUMN)	
	ACAC_ID	     (USED KEY COLUMN)	
		     NO TEMPORARY RESULTS CREATED	
		     RESULT IS COPIED   , COSTVALUE IS	      4123

[fabricebourdel]

... and here is the query, for information (even if you don't have the tables on a test database) :

SELECT   
          IDTC.IDTC_TXT AS XIDENT_ET_PR,
          CASE SOREAC.AC_ID WHEN NULL THEN FALSE ELSE TRUE END AS IDENTCO
FROM      IMPORT_DONNEE               IMDO
JOIN      IMPORT_TRANS                IMTR      ON IMTR.IMTR_ID     = IMDO.IMTR_ID
                                               AND IMTR.COGR_ID     = 203
                                               AND IMTR.IMGR_PER_CALC = 20082410
JOIN      SOCIETE_REF_LINK_PR         SORELIPR  ON SORELIPR.SORE_ID = IMDO.SORE_ID
JOIN      SOCIETE_REF                 SORE      ON SORE.SORE_ID     = SORELIPR.SORE_ID_PR
JOIN      IMPORT_DONNEE_TEXTE_COMMUN  IDTC      ON IDTC.IDTC_ID     = SORE.IDTC_ID_XIDENT_ET_PR
left JOIN SOCIETE_REF_ACTIONNAIRE     SOREAC    ON SOREAC.SORE_ID   = SORE.SORE_ID
                                               AND SOREAC.ACAC_ID   = 83
                                               
GROUP BY  IDTC.IDTC_TXT,
          CASE SOREAC.AC_ID WHEN NULL THEN FALSE ELSE TRUE END

for information, the case statement use a field from the table SOCIETE_REF_ACTIONNAIRE ; using a table from a table not in the "left join" change nothing. Doing the left join on IMPORT_DONNEE_TEXTE_COMMUN and not then actual used table does nothing. Using a simple case statement of complex statement change nothing too.

Naturally, in this example, i could use distinct in replacement of the group by instruction ; also, the only thing that make the group by not beeing effective is the left join with a case statement...

Cheers

[lbreddemann]

Hi Fabrice,

I cannot reproduce the problem.

For easier comparison I used the demo HOTEL schema:

select
	 h.hno,
	 h.name,
	 case e.manager_eno
	 	WHEN NULL THEN 'no boss' 
		ELSE e.manager_eno 
		END AS BOSS,
	count(e.eno)
from hotel.hotel h left join hotel.employee e 
		on h.hno=e.hno and e.hno =10
group by h.hno, h.name, case e.manager_eno
	 	WHEN NULL THEN 'no boss' 
		ELSE e.manager_eno 
		END 

This statement correctly produces the output wanted.

Can you try to reproduce the issue with the HOTEL schema as well or can you provide an export of the tables involved so that I can reproduce it with your data?

regards,

Lars