[ETL] When same concept used within QG/RQG and outside, ETL fails

[mahalakshme]

Need:

These kind of product issues leads to disruption in smooth working when suggesting/for implementing workarounds and delays expected delivery time

Steps to reproduce:

• Create a registration form
• Create a repeatable question group(RQG)/question group(QG) in that form
• Use the same concept within and outside the repeatable question group/question group(QG)
• The first run of ETL succeeds
• Edit something in the form
• In the second run it will throw an error like below:

You can check id-476380, prerelease, scheduled_job_run table - where I have tried for JNPCT Adoloscent UAT org

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table "cini".school_onsite_support_to_teachers rename column "Demo class details Please specify subject" to "Please specify subject";]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
	at org.avniproject.etl.repository.SchemaMetadataRepository.lambda$applyChanges$24(SchemaMetadataRepository.java:299)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:299)
	at jdk.internal.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
	at org.avniproject.etl.repository.SchemaMetadataRepository$$SpringCGLIB$$0.applyChanges(<generated>)
	at org.avniproject.etl.service.SchemaMigrationService.migrate(SchemaMigrationService.java:39)
	at jdk.internal.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:569)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
	at org.avniproject.etl.service.SchemaMigrationService$$SpringCGLIB$$0.migrate(<generated>)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:52)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35)
	at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: column "Please specify subject" of relation "school_onsite_support_to_teachers" already exists
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
	... 36 more

Probable root cause:

avni-etl/src/main/java/org/avniproject/etl/domain/metadata/ColumnMetadata.java

Line 115 in 6b7ff69

if (realColumn.getParentConceptUuid() != null) {

AC:

Even for the above setup ETL should not fail

Probable technical solution: (Below are suggestions, devs can decide)

• Fix for repeatable question group: Do not create columns for repeatable question group concepts in parent table.
• Fix for question group: This will fix for repeatable question group also, but doing the above fix as well helps in keeping the structure clean and less confusions.
  if (realColumn.getParentConceptUuid() != null) {
  should have probably been
  if (realColumn.getParentConceptUuid() != null || getParentConceptUuid() != null) {

Bundle file with that case - ETL will fail -

Collectives for Integrated Livelihood Initiatives (CInI).zip