deeply nested where-in query hangs (cycles) indefinitely after upgrading to 2.4.240

Ouverte
#4,318 22 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
45/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
Calme
Stack technique
java, sql
Domaine
databases

Piste de recherche

Commencez par exécuter la requête fournie sur les définitions de T_OBJECT et T_PACKAGE, puis comparez le comportement entre H2 2.3.232 et 2.4. Examinez le chemin d’exécution répétée via Query.java, ConditionInQuery.java et Select.java. La tâche est terminée lorsque vous avez reproduit la requête qui ne retourne pas avec des données d’arbre représentatives et confirmé qu’elle se termine sans régression du comportement de l’ancienne version.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

this is the query:

select t0.OBJECT_ID, t0.EA_GUID, t0.ALIAS, t0.PDATA1, t0.PDATA5, t0.VISIBILITY,
	t0.NAME, t0.STEREOTYPE, t0.TPOS, t0.OBJECT_TYPE, t0.STATUS,
	t0.Version, t0.Phase, t0.PARENTID, t0.PACKAGE_ID, t0.CLASSIFIER,
	(case when t0.object_type = 'Package' then 1 else 2 end),
	t0.AUTHOR, t0.CREATEDDATE, t0.MODIFIEDDATE
from T_OBJECT t0
where t0.STEREOTYPE = ?
and t0.PACKAGE_ID in (
	select t0.PACKAGE_ID from T_PACKAGE t0 where t0.EA_GUID in (
		select t0.EA_GUID from T_OBJECT t0 where t0.STEREOTYPE = ? and t0.PACKAGE_ID in (
			select t0.PACKAGE_ID from T_PACKAGE t0 where (
				t0.PACKAGE_ID in (
					select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PACKAGE_ID in (?)
				) or t0.PACKAGE_ID in (
					select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
						select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PACKAGE_ID in (?)
					)
				) or t0.PACKAGE_ID in (
					select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
						select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
							select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PACKAGE_ID in (?)
						)
					)
				) or t0.PACKAGE_ID in (
					select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
						select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
							select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
								select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PACKAGE_ID in (?)
							)
						)
					)
				) or t0.PACKAGE_ID in (
					select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
						select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
							select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
								select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PARENT_ID in (
									select t0.PACKAGE_ID from T_PACKAGE t0 where t0.PACKAGE_ID in (?)
								)
							)
						)
					)
				)
			)
		)
	)
) and t0.EA_GUID = ?

these are the table definitions:

CREATE TABLE "T_OBJECT"
(
	"OBJECT_ID" NUMBER(10) NOT NULL,
	"OBJECT_TYPE" VARCHAR2(255 CHAR),
	"DIAGRAM_ID" NUMBER(10) DEFAULT 0,
	"NAME" VARCHAR2(255 CHAR),
	"ALIAS" VARCHAR2(255 CHAR),
	"AUTHOR" VARCHAR2(255 CHAR),
	"VERSION" VARCHAR2(50 CHAR) DEFAULT '1.0',
	"NOTE" CLOB,
	"PACKAGE_ID" NUMBER(10) DEFAULT 0  NOT NULL,
	"STEREOTYPE" VARCHAR2(255 CHAR),
	"STATUS" VARCHAR2(50 CHAR),
	"PDATA1" VARCHAR2(255 CHAR),
	"PDATA2" CLOB,
	"PDATA3" CLOB,
	"PDATA4" CLOB,
	"PDATA5" VARCHAR2(255 CHAR),
	"PHASE" VARCHAR2(50 CHAR),
	"SCOPE" VARCHAR2(25 CHAR),
	"CLASSIFIER" NUMBER(10),
	"EA_GUID" VARCHAR2(40 CHAR) NOT NULL,
	"PARENTID" NUMBER(10),
	"CLASSIFIER_GUID" VARCHAR2(40 CHAR),
	"TPOS" NUMBER(10),
	CONSTRAINT "PK_OBJECT" PRIMARY KEY ("OBJECT_ID"),
	CONSTRAINT "UQ_OBJ_EAGUID" UNIQUE ("EA_GUID")
);

CREATE TABLE "T_PACKAGE"
(
	"PACKAGE_ID" NUMBER(10) NOT NULL,
	"NAME" VARCHAR2(255 CHAR),
	"PARENT_ID" NUMBER(10) DEFAULT 0  NOT NULL,
	"CREATEDDATE" DATE DEFAULT SYSDATE,
	"MODIFIEDDATE" DATE DEFAULT SYSDATE,
	"NOTES" CLOB,
	"EA_GUID" VARCHAR2(40 CHAR) NOT NULL,
	"VERSION" VARCHAR2(50 CHAR),
	"NAMESPACE" VARCHAR2(50 CHAR),
	"TPOS" NUMBER(10),
	CONSTRAINT "PK_PACKAGE" PRIMARY KEY ("PACKAGE_ID"),
	CONSTRAINT "UQ_PACKAGE_EAGUID" UNIQUE ("EA_GUID")
);

and this is the stack trace captured at one moment during this never-returning org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:130) call:

at org.h2.engine.SessionLocal.getSnapshotDataModificationId(SessionLocal.java:809)
at org.h2.command.query.Query.getMaxDataModificationId(Query.java:905)
at org.h2.command.query.Query.query(Query.java:535)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.condition.ConditionAndOrN.getValue(ConditionAndOrN.java:140)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.condition.ConditionAndOr.getValue(ConditionAndOr.java:110)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68)
at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55)
at org.h2.expression.condition.ConditionAndOrN.getValue(ConditionAndOrN.java:129)
at org.h2.expression.Expression.getBooleanValue(Expression.java:344)
at org.h2.command.query.Select.isConditionMet(Select.java:438)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1868)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:749)
at org.h2.command.query.Select.queryWithoutCache(Select.java:873)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:214)
at org.h2.command.query.Query.query(Query.java:548)
at org.h2.command.query.Query.query(Query.java:510)
at org.h2.command.CommandContainer.query(CommandContainer.java:222)
at org.h2.command.Command.executeQuery(Command.java:198)
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:130)
at io.ebean.datasource.pool.ExtendedPreparedStatement.executeQuery(ExtendedPreparedStatement.java:111)

Yes, I know the query is extremely ugly, but this is a schema of an external application (Enterprise Architect) which we need to work with. These two tables store a tree structure of deeply nested packages and objects inside them and the goal of this query is to find all object in a subtree of this tree, beginning at some package and testing up to 5 levels deep.

This query have been working without any problems up to the last version (2.3.232), but started hanging after we upgraded to spring boot 4.0, which brings in H2 v2.4.

I'm not sure how helpful this report will be since this query only hangs with certain data, which I don't know how to extract during the debug session, but I'll gladly provide more info if you tell me what you need and how I can get it.

Langage dominant
Java
Étoiles
4.6k
Forks
1.3k
Merge moyen
2 j 1 h
PR mergées (30 j)
61

Préparer son environnement

Ce projet ne fournit ni conteneur de développement, ni Dockerfile, ni guide de contribution : l'installation est à votre charge. Commencez par son README, et consultez notre guide de la première contribution pour les étapes générales.

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de h2database/h2database

Toutes les issues de h2database/h2database

Issues similaires

Plus d'issues Java

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.