dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20764
Re: [Bug 1100706] Re: sql-view-duplicate-column-names
Hi Jason,
I've test your example query as below and nothing can pass the validation
before saving ;) and how is it going on your side?
On Mon, Jan 28, 2013 at 5:08 PM, jason.p.pickering <
1100706@xxxxxxxxxxxxxxxxxx> wrote:
> Hi Hieu,
> Thanks for taking a look at this.
>
> Will this work for something like
>
> SELECT de.*, ds.name FROM dataelement de
> INNER JOIN datasetmembers dsm on de.dataelementid = dsm.dataelementid
> INNER JOIN dataset ds on dsm.datasetid = ds.datasetid ?
>
> Best regards,
> Jason
>
>
> On Mon, Jan 28, 2013 at 12:59 PM, Hieu Duy Dang
> <hieu.hispvietnam@xxxxxxxxx>wrote:
>
> > ** Changed in: dhis2
> > Status: New => Fix Committed
> >
> > --
> > You received this bug notification because you are subscribed to the bug
> > report.
> > https://bugs.launchpad.net/bugs/1100706
> >
> > Title:
> > sql-view-duplicate-column-names
> >
> > Status in DHIS 2 - District Health Information Software:
> > Fix Committed
> >
> > 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
> >
>
> --
> You received this bug notification because you are a bug assignee.
> https://bugs.launchpad.net/bugs/1100706
>
> Title:
> sql-view-duplicate-column-names
>
> Status in DHIS 2 - District Health Information Software:
> Fix Committed
>
> 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
>
--
"*Expert By Chance*"
--
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:
Fix Committed
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
Follow ups
References