[teiid-issues] [JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb

[Bram Gadeyne (JIRA)]

Bram Gadeyne created TEIID-5096:
-----------------------------------

             Summary: Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
                 Key: TEIID-5096
                 URL: https://issues.jboss.org/browse/TEIID-5096
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 9.3.3
            Reporter: Bram Gadeyne
            Assignee: Steven Hawkins


Hi,

I've created a vdb that is depoyed using a vdb-ddl.ddl file.

The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.


{code:sql}
CREATE VIEW tv_retrieve_monvals(
	admissionid	integer not null,
	VariableID	integer not null,
	Datetime	timestamp not null,
	Entertime	timestamp not null,
	varvalue	double not null,
	primary key (admissionid, VariableID, Datetime)
)
AS
SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
FROM (
	SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value", 
		ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang 
	FROM izisprod.P_GeneralData AS gd 
	INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON 
		gd.PatientID = mv.PatientID AND 
		bitand(mv.Status, 8) = 8 AND 
		bitand(mv.Status, 2) <> 2 
	WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5) 
	UNION 
	SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value", 
		ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang 
	FROM iziswh.P_GeneralData AS gd 
	INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON 
		gd.PatientID = mv.PatientID AND 
		bitand(mv.Status, 8) = 8 AND 
		bitand(mv.Status, 2) <> 2
) AS v
WHERE v.rang = 1;
{code}

Removing the /*+ MAKDEP */ references resolves the issue.

I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.




--
This message was sent by Atlassian JIRA
(v7.5.0#75005)