dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

Confirmed by inserting the following sql view:

SELECT deg.name, de.name from dataelement de join
dataelementgroupmembers degm using(dataelementid) join dataelementgroup
deg using(dataelementgroupid)

We already have a validation function at the time of adding and editing
sql views through the "testSqlGrammar" method. It seems these kinds of
errors are not picked up.

-- 
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.
https://bugs.launchpad.net/bugs/1100706

Title:
  sql-view-duplicate-column-names

Status in DHIS 2 - District Health Information Software:
  New

Bug description:
  When creating a view like..

  SELECT woreda.name, health_post.name FROM _orgunitstructure ous INNER
  JOIN organisationunit woreda on ous.idlevel4 =
  woreda.organisationunitid INNER JOIN organisationunit health_post on
  ous.idlevel6 = health_post.organisationunitid;

  the name column is duplicated. Although this is valid SQL, it is not
  valid to materialize a table.

  SELECT woreda.name as woreda, health_post.name as health_post  FROM
  _orgunitstructure ous INNER JOIN organisationunit woreda on
  ous.idlevel4 = woreda.organisationunitid INNER JOIN organisationunit
  health_post on ous.idlevel6 = health_post.organisationunitid;

  The SQL View function will allow you to save the first view, but will
  throw an error silently in the logs (shown below) when the view is
  attempted to be materialized.

  
  Caused by: org.postgresql.util.PSQLException: ERROR: column "name" specified more than once
          at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
          at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
          at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
          at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
          at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:616)
          at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
          ... 112 more

  This is a sort of borderline bug, but at the very least, we need to
  report that something went wrong. This error is just swallowed and the
  user never sees that an error occurred.

To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1100706/+subscriptions