Query hangs on return of lowest row value Teiid 8.1.0 final

[mikebailey]

Teiid 8.1 Query Hangs

I have a query that hangs when the return value is the lowest value from the table.  Overall it is a bit complicated so let me explain.

There is a healthstate table with 4 rows and 3 columns.  columns are name, severity, color.  Severity is a value of 1,2,3,10.

There is a policy table that has a nubmer of rows with a ruletype column that matches the name column of the health state table. There is a rulename column that matches the below state columns.

There is a generated table with random numbers for columns that provide the value to be used. i.e. there is a cpubusy column that has a percent value, the policy table will have a cpubusy row with a compare value.

Here is the query:

select a.timeevent as HEALTHTIME,

       CASE when a.cpubusy > j.rulevalue then (select h.severity from demo_policy_metadata.chorus_health_state h where h.name = j.ruletype) else 3 end as HEALTHSTATEFKEY,

          primary_key('demo.demo_lpar_health_state', 'timeevent',a.timeevent, 'sysplex',a.sysplex, 'sysid',a.sysid) as OBJFKEYXML,

          primary_key('demo_policy_metadata.demoLparPolicy','rulename',j.rulename, 'ruletype', j.ruletype) as POLICYFKEYXML

          from demo_policy_metadata.demoLparPolicy j, demo.demo_lpar_health_state a

          where j.comparefield = 'cpubusy'

The select for the CASE statement should return a value from the health state. The value should be either a 1 or a 2.

This query works when the value is 2; or if I add 'and' clauses to reduce the result set to a single row. i.e. "and j.ruletype = 'Danger' and a.sysid = 'USILCA31'"

or if i remove the 'primary_key' columns. 

With the query as is and a return of a '1' (lowest value from table)  from the select in the CASE - the query just hangs.

The 'primary_key()' returns an XML string built from the passed in values. nothing overly exciting, just consistent XML.

I have a log with debug and a heap dump for review.

In the file 'server.log' line 3826 is where the debug for the query starts. line 4390 is where it seems to hang.

The dump is 450mb and will not upload. Is there an alternative process?

[mikebailey]

Additional explanation on the tables in the query:

demo_policy_metadata.chorus_health_state is a H2 table built at startup.  I have tested with a ‘*.txt’ version with the same results.

demo_policy_metadata.demoLparPolicy is a ‘*.txt’ file

1. demo.demo_lpar_health_state is java code translator

[rareddy]

Mike,

Is it possible for you to give us testcase along with the VDB and limited set of data that needs?

Thanks

Ramesh..

[mikebailey]

I will see what i can do.  I have tried to use a set of *.txt files for the data, but then the query works.

[mikebailey]

While working on a portable testcase two things were discovered. The 'hang' appears to be a 'loop' or race condition. The CPU goes up to about 20% and stay's there. Also, by rewriting the query the condition goes away.

I moved the join out of the case statement and simplified the case statement which causes this to work normally.

New query.

select    a.timeevent as HEALTHTIME, 

                                        CASE when a.cpubusy > j.rulevalue then h.severity  else 3 end as           HEALTHSTATEFKEY,

                                                            primary_key('demo.demo_lpar_health_state', 'timeevent',a.timeevent, 'sysplex',a.sysplex, 'sysid',a.sysid) as OBJFKEYXML,

                                                            primary_key('demo_policy_metadata.demoLparPolicy','rulename',j.rulename, 'ruletype', j.ruletype) as POLICYFKEYXML

                                                            from demo_policy_metadata.demoLparPolicy j, demo.demo_lpar_health_state a, demo_policy_metadata.chorus_health_state h

                                                            where j.comparefield = 'cpubusy' and h.name = j.ruletype

I will continue to work on the portable testcase, but the urgency of this is lower now for me.