JBoss Community

Re: Problem with Human Task query TasksAssignedAsPotentialOwnerByStatus

created by Fred DERNBACH in jBPM - View the full discussion

I confirm the issue. Oracle does not receive proper binding variable for status list. below is the Oracle trace of the hibernate query. See in bold.

SQL_ID 1fnn7qr6kqp5y, child number 1

-------------------------------------

select user2_.id as id175_0_, user1_.id as id175_1_, task0_.id as col_0_0_,

task0_.processInstanceId as col_1_0_, names5_.text as col_2_0_, subjects3_.text as col_3_0_,

descriptio4_.text as col_4_0_, task0_.status as col_5_0_, task0_.priority as col_6_0_,

task0_.skipable as col_7_0_, user2_.id as col_8_0_, user1_.id as col_9_0_, task0_.createdOn as

col_10_0_, task0_.activationTime as col_11_0_, task0_.expirationTime as col_12_0_,

task0_.processId as col_13_0_, task0_.processSessionId as col_14_0_ from Task task0_ left outer

join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id left outer join

OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id left outer join I18NText

subjects3_ on task0_.id=subjects3_.Task_Subjects_Id left outer join I18NText descriptio4_ on

task0_.id=descriptio4_.Task_Descriptions_Id left outer join I18NText names5_ on

task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_ where (task0_.archived=0

)and((organizati6_.id=:1 )or(organi

Plan hash value: 2720357931

---------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 40 (100)| |

|* 1 | FILTER | | | | | |

|* 2 | HASH JOIN OUTER | | 1 | 7594 | 40 (5)| 00:00:04 |

|* 3 | HASH JOIN OUTER | | 1 | 5450 | 33 (7)| 00:00:04 |

|* 4 | HASH JOIN OUTER | | 1 | 3306 | 25 (4)| 00:00:03 |

| 5 | NESTED LOOPS | | 1 | 1162 | 18 (6)| 00:00:02 |

|* 6 | HASH JOIN | | 1 | 1033 | 17 (6)| 00:00:02 |

| 7 | NESTED LOOPS OUTER | | 1 | 891 | 9 (0)| 00:00:01 |

| 8 | NESTED LOOPS OUTER| | 1 | 762 | 8 (0)| 00:00:01 |

|* 9 | TABLE ACCESS FULL| TASK | 1 | 633 | 7 (0)| 00:00:01 |

|* 10 | INDEX UNIQUE SCAN| SYS_C00144479 | 1 | 129 | 1 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | SYS_C00144479 | 1 | 129 | 1 (0)| 00:00:01 |

| 12 | SORT UNIQUE | | 10 | 1420 | 7 (0)| 00:00:01 |

|* 13 | TABLE ACCESS FULL | PEOPLEASSIGNMENTS_POTOWNERS | 10 | 1420 | 7 (0)| 00:00:01 |

|* 14 | INDEX UNIQUE SCAN | SYS_C00144479 | 1 | 129 | 1 (0)| 00:00:01 |

| 15 | TABLE ACCESS FULL | I18NTEXT | 30 | 64320 | 7 (0)| 00:00:01 |

| 16 | TABLE ACCESS FULL | I18NTEXT | 30 | 64320 | 7 (0)| 00:00:01 |

| 17 | TABLE ACCESS FULL | I18NTEXT | 30 | 64320 | 7 (0)| 00:00:01 |

| 18 | SORT AGGREGATE | | 1 | 13 | | |

|* 19 | TABLE ACCESS FULL | I18NTEXT | 1 | 13 | 7 (0)| 00:00:01 |

| 20 | SORT AGGREGATE | | 1 | 13 | | |

|* 21 | TABLE ACCESS FULL | I18NTEXT | 1 | 13 | 7 (0)| 00:00:01 |

| 22 | SORT AGGREGATE | | 1 | 13 | | |

|* 23 | TABLE ACCESS FULL | I18NTEXT | 1 | 13 | 7 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1B69B172

9 - SEL$1B69B172 / TASK0_@SEL$2

10 - SEL$1B69B172 / USER2_@SEL$3

11 - SEL$1B69B172 / USER1_@SEL$1

13 - SEL$1B69B172 / POTENTIALO7_@SEL$12

14 - SEL$1B69B172 / ORGANIZATI6_@SEL$11

15 - SEL$1B69B172 / NAMES5_@SEL$9

16 - SEL$1B69B172 / DESCRIPTIO4_@SEL$7

17 - SEL$1B69B172 / SUBJECTS3_@SEL$5

18 - SEL$13

19 - SEL$13 / NAMES8_@SEL$13

20 - SEL$14

21 - SEL$14 / SUBJECTS9_@SEL$14

22 - SEL$15

23 - SEL$15 / DESCRIPTIO10_@SEL$15

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

OPT_PARAM('_optimizer_connect_by_cost_based' 'false')

OPT_PARAM('star_transformation_enabled' 'temp_disable')

OPT_PARAM('optimizer_index_caching' 50)

ALL_ROWS

OUTLINE_LEAF(@"SEL$13")

OUTLINE_LEAF(@"SEL$14")

OUTLINE_LEAF(@"SEL$15")

OUTLINE_LEAF(@"SEL$1B69B172")

UNNEST(@"SEL$12")

OUTLINE(@"SEL$13")

OUTLINE(@"SEL$14")

OUTLINE(@"SEL$15")

OUTLINE(@"SEL$48266A83")

MERGE(@"SEL$B97648DD")

OUTLINE(@"SEL$12")

OUTLINE(@"SEL$11")

OUTLINE(@"SEL$B97648DD")

MERGE(@"SEL$096E5AED")

MERGE(@"SEL$9")

OUTLINE(@"SEL$10")

OUTLINE(@"SEL$096E5AED")

MERGE(@"SEL$15E987C1")

MERGE(@"SEL$7")

OUTLINE(@"SEL$9")

OUTLINE(@"SEL$8")

OUTLINE(@"SEL$15E987C1")

MERGE(@"SEL$5")

MERGE(@"SEL$7237DA6D")

OUTLINE(@"SEL$7")

OUTLINE(@"SEL$6")

OUTLINE(@"SEL$5")

OUTLINE(@"SEL$7237DA6D")

MERGE(@"SEL$3")

MERGE(@"SEL$58A6D7F6")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$3")

OUTLINE(@"SEL$58A6D7F6")

MERGE(@"SEL$1")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$1")

FULL(@"SEL$1B69B172" "TASK0_"@"SEL$2")

INDEX(@"SEL$1B69B172" "USER2_"@"SEL$3" ("ORGANIZATIONALENTITY"."ID"))

INDEX(@"SEL$1B69B172" "USER1_"@"SEL$1" ("ORGANIZATIONALENTITY"."ID"))

FULL(@"SEL$1B69B172" "POTENTIALO7_"@"SEL$12")

INDEX(@"SEL$1B69B172" "ORGANIZATI6_"@"SEL$11" ("ORGANIZATIONALENTITY"."ID"))

FULL(@"SEL$1B69B172" "NAMES5_"@"SEL$9")

FULL(@"SEL$1B69B172" "DESCRIPTIO4_"@"SEL$7")

FULL(@"SEL$1B69B172" "SUBJECTS3_"@"SEL$5")

LEADING(@"SEL$1B69B172" "TASK0_"@"SEL$2" "USER2_"@"SEL$3" "USER1_"@"SEL$1"

"POTENTIALO7_"@"SEL$12" "ORGANIZATI6_"@"SEL$11" "NAMES5_"@"SEL$9" "DESCRIPTIO4_"@"SEL$7"

"SUBJECTS3_"@"SEL$5")

USE_NL(@"SEL$1B69B172" "USER2_"@"SEL$3")

USE_NL(@"SEL$1B69B172" "USER1_"@"SEL$1")

USE_HASH(@"SEL$1B69B172" "POTENTIALO7_"@"SEL$12")

USE_NL(@"SEL$1B69B172" "ORGANIZATI6_"@"SEL$11")

USE_HASH(@"SEL$1B69B172" "NAMES5_"@"SEL$9")

USE_HASH(@"SEL$1B69B172" "DESCRIPTIO4_"@"SEL$7")

USE_HASH(@"SEL$1B69B172" "SUBJECTS3_"@"SEL$5")

FULL(@"SEL$15" "DESCRIPTIO10_"@"SEL$15")

FULL(@"SEL$14" "SUBJECTS9_"@"SEL$14")

FULL(@"SEL$13" "NAMES8_"@"SEL$13")

END_OUTLINE_DATA

Peeked Binds (identified by position):

--------------------------------------

1 - :1 (VARCHAR2(30), CSID=46): 'ITCH'

2 - :2 (VARCHAR2(30), CSID=46): 'SECURITY_GLOBAL'

3 - :3 (VARCHAR2(30), CSID=46): 'SECURITY_AUTHORITY'

7 - :7 (RAW(30)): aced00057e7200146f72672e6a62706d2e7461736b2e537461747573000000000000000012000078720

00e6a6176612e6c616e672e456e756d000000000000000012000078707400055265616479

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter((("NAMES5_"."LANGUAGE"=:4 OR =0) AND ("SUBJECTS3_"."LANGUAGE"=:5 OR =0) AND

("DESCRIPTIO4_"."LANGUAGE"=:6 OR =0)))

2 - access("TASK0_"."ID"="SUBJECTS3_"."TASK_SUBJECTS_ID")

3 - access("TASK0_"."ID"="DESCRIPTIO4_"."TASK_DESCRIPTIONS_ID")

4 - access("TASK0_"."ID"="NAMES5_"."TASK_NAMES_ID")

6 - access("TASK0_"."ID"="POTENTIALO7_"."TASK_ID")

9 - filter(("TASK0_"."EXPIRATIONTIME" IS NULL AND "TASK0_"."ARCHIVED"=0 AND

"TASK0_"."STATUS"=RAWTOHEX(:7)))

10 - access("TASK0_"."ACTUALOWNER_ID"="USER2_"."ID")

11 - access("TASK0_"."CREATEDBY_ID"="USER1_"."ID")

13 - filter(("POTENTIALO7_"."ENTITY_ID"=:1 OR "POTENTIALO7_"."ENTITY_ID"=:2 OR

"POTENTIALO7_"."ENTITY_ID"=:3))

14 - access("ORGANIZATI6_"."ID"="POTENTIALO7_"."ENTITY_ID")

filter(("ORGANIZATI6_"."ID"=:1 OR "ORGANIZATI6_"."ID"=:2 OR "ORGANIZATI6_"."ID"=:3))

19 - filter("NAMES8_"."TASK_NAMES_ID"=:B1)

21 - filter("SUBJECTS9_"."TASK_SUBJECTS_ID"=:B1)

23 - filter("DESCRIPTIO10_"."TASK_DESCRIPTIONS_ID"=:B1)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TEXT"[LOB,4000], "NAMES5_"."TEXT"[LOB,4000],

"USER1_"."ID"[VARCHAR2,255], "TASK0_"."PRIORITY"[NUMBER,22],

"TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

"TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

"TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

"TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255], "USER2_"."ID"[VARCHAR2,255],

"SUBJECTS3_"."TEXT"[LOB,4000]

2 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TEXT"[LOB,4000],

"NAMES5_"."TEXT"[LOB,4000], "USER1_"."ID"[VARCHAR2,255], "TASK0_"."PRIORITY"[NUMBER,22],

"TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

"TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

"TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

"TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255], "USER2_"."ID"[VARCHAR2,255],

"NAMES5_"."LANGUAGE"[VARCHAR2,255], "DESCRIPTIO4_"."LANGUAGE"[VARCHAR2,255],

"SUBJECTS3_"."LANGUAGE"[VARCHAR2,255], "SUBJECTS3_"."TEXT"[LOB,4000]

3 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TASK_DESCRIPTIONS_ID"[NUMBER,22],

"NAMES5_"."TEXT"[LOB,4000], "NAMES5_"."TASK_NAMES_ID"[NUMBER,22],

"ORGANIZATI6_"."ID"[VARCHAR2,255], "POTENTIALO7_"."TASK_ID"[NUMBER,22],

"USER1_"."ID"[VARCHAR2,255], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

"TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

"TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

"TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

"TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

"TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

"USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

"POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255], "ORGANIZATI6_".ROWID[ROWID,10],

"NAMES5_"."LANGUAGE"[VARCHAR2,255], "DESCRIPTIO4_"."LANGUAGE"[VARCHAR2,255],

"DESCRIPTIO4_"."TEXT"[LOB,4000]

4 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "NAMES5_"."TASK_NAMES_ID"[NUMBER,22],