dhis2-devs team mailing list archive

["jason.p.pickering" <1100706@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: dhis2
     Importance: Undecided
         Status: New

-- 
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