sql-view-duplicate-column-names

Bug #1100706 reported by jason.p.pickering on 2013-01-17
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
DHIS
Undecided
Hieu Duy Dang

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.

Changed in dhis2:
assignee: nobody → Hieu Duy Dang (hieu-hispvietnam)
milestone: none → 2.11

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.

Changed in dhis2:
status: New → Fix Committed

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
<email address hidden>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
>

Download full text (6.2 KiB)

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 <
<email address hidden>> 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
> <email address hidden>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(Abs...

Read more...

Download full text (9.4 KiB)

If it does not pass, then I think it really is fixed! Good work Hieu.

On Tue, Jan 29, 2013 at 6:33 AM, Hieu Duy Dang
<email address hidden>wrote:

> 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 <
> <email address hidden>> 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
> > <email address hidden>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.NativeMeth...

Read more...

Download full text (12.9 KiB)

If this facebook then I will like your sentence ;)

On Tue, Jan 29, 2013 at 11:25 AM, jason.p.pickering <
<email address hidden>> wrote:

> If it does not pass, then I think it really is fixed! Good work Hieu.
>
>
> On Tue, Jan 29, 2013 at 6:33 AM, Hieu Duy Dang
> <email address hidden>wrote:
>
> > 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 <
> > <email address hidden>> 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
> > > <email address hidden>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
> > ...

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers