DBUnit ignores column aliases, when creating dataset from query?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

DBUnit ignores column aliases, when creating dataset from query?

Laimonas Selenis-2
Hi,

Maybe someone could tell me if it is expected behavior or maybe a bug:

I have two datasets, which I want to compare with DBUnit: one defines as
XML file, the other I am trying to create using a query:

     dbConnection.createQueryTable("my_table", "SELECT ...");

Select goes over couple of tables, where the few of them has the same
column names, so I am using aliases to distinguish between them. The
query runs fine, but seems that while creating a dataset, DBUnit ignores
the column aliases and uses the original names instead. This clearly is
seen in DEBUG log:

     01.03.13 14:18:43,228 [ DEBUG] AbstractDatabaseConnection -
createQueryTable(resultName=my_table, sql=
             SELECT `table1`.`name`
                 , `table2`.`label` AS `table2_label`
                 , `table3`.`label`
             FROM `table1`
                 , `table2`
                 , `table3`
             WHERE `table1`.`id` = `table2`.`table1_id`
                 AND `table2`.`id` = `table3`.`table2_id`
         ) - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseConfig$Configurator -
Statement fetch size set to 100
     01.03.13 14:18:43,240 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=name) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=name) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     ....

As you see - `getValue(row=X)` uses `label` as name for `table2` and
`table3` columns and not `table2_label` and `label` as defined in query...

I am using MySQL database.

Thank you for your time!
Laimonas

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: DBUnit ignores column aliases, when creating dataset from query?

John Hurst-2
Hello,

As far as I know what you are trying to do should be fine. Column aliases should work as you expect.

I tried with an example from my current work code base and when I alias the column without changing my expected results dataset, my test fails. When I change the result dataset to match the column alias, it works. This supports your expected behavior. My code is also using IDatabaseConnection#createQueryTable(String tableName, String sql). I am using Oracle but that should make no difference.

Perhaps you could do a standalone test case?

Regards

John Hurst



On Sat, Mar 2, 2013 at 9:33 AM, Laimonas Selenis <[hidden email]> wrote:
Hi,

Maybe someone could tell me if it is expected behavior or maybe a bug:

I have two datasets, which I want to compare with DBUnit: one defines as
XML file, the other I am trying to create using a query:

     dbConnection.createQueryTable("my_table", "SELECT ...");

Select goes over couple of tables, where the few of them has the same
column names, so I am using aliases to distinguish between them. The
query runs fine, but seems that while creating a dataset, DBUnit ignores
the column aliases and uses the original names instead. This clearly is
seen in DEBUG log:

     01.03.13 14:18:43,228 [ DEBUG] AbstractDatabaseConnection -
createQueryTable(resultName=my_table, sql=
             SELECT `table1`.`name`
                 , `table2`.`label` AS `table2_label`
                 , `table3`.`label`
             FROM `table1`
                 , `table2`
                 , `table3`
             WHERE `table1`.`id` = `table2`.`table1_id`
                 AND `table2`.`id` = `table3`.`table2_id`
         ) - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseConfig$Configurator -
Statement fetch size set to 100
     01.03.13 14:18:43,240 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=name) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=name) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     ....

As you see - `getValue(row=X)` uses `label` as name for `table2` and
`table3` columns and not `table2_label` and `label` as defined in query...

I am using MySQL database.

Thank you for your time!
Laimonas

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: DBUnit ignores column aliases, when creating dataset from query?

Laimonas Selenis-2
Hi John,

Thanks for your reply.

I will try to do standalone test case when I will be back at work next
week and will respond with more details.

Regards,
Laimonas

On 02 March 2013 04:57:45, John Hurst wrote:

> Hello,
>
> As far as I know what you are trying to do should be fine. Column
> aliases should work as you expect.
>
> I tried with an example from my current work code base and when I
> alias the column without changing my expected results dataset, my test
> fails. When I change the result dataset to match the column alias, it
> works. This supports your expected behavior. My code is also using
> IDatabaseConnection#createQueryTable(String tableName, String sql). I
> am using Oracle but that should make no difference.
>
> Perhaps you could do a standalone test case?
>
> Regards
>
> John Hurst
>
>
>
> On Sat, Mar 2, 2013 at 9:33 AM, Laimonas Selenis
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     Hi,
>
>     Maybe someone could tell me if it is expected behavior or maybe a bug:
>
>     I have two datasets, which I want to compare with DBUnit: one
>     defines as
>     XML file, the other I am trying to create using a query:
>
>          dbConnection.createQueryTable("my_table", "SELECT ...");
>
>     Select goes over couple of tables, where the few of them has the same
>     column names, so I am using aliases to distinguish between them. The
>     query runs fine, but seems that while creating a dataset, DBUnit
>     ignores
>     the column aliases and uses the original names instead. This
>     clearly is
>     seen in DEBUG log:
>
>          01.03.13 14:18:43,228 [ DEBUG] AbstractDatabaseConnection -
>     createQueryTable(resultName=my_table, sql=
>                  SELECT `table1`.`name`
>                      , `table2`.`label` AS `table2_label`
>                      , `table3`.`label`
>                  FROM `table1`
>                      , `table2`
>                      , `table3`
>                  WHERE `table1`.`id` = `table2`.`table1_id`
>                      AND `table2`.`id` = `table3`.`table2_id`
>              ) - start
>          01.03.13 14:18:43,228 [ DEBUG] DatabaseDataSourceConnection -
>     getConnection() - start
>          01.03.13 14:18:43,228 [ DEBUG] DatabaseConfig$Configurator -
>     Statement fetch size set to 100
>          01.03.13 14:18:43,240 [ DEBUG] DatabaseDataSourceConnection -
>     getConnection() - start
>          01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
>     'schemaName' from the ResultSetMetaData is empty-string and not
>     applicable hence. Will not try to lookup column properties via
>     DatabaseMetaData.getColumns.
>          01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
>     'schemaName' from the ResultSetMetaData is empty-string and not
>     applicable hence. Will not try to lookup column properties via
>     DatabaseMetaData.getColumns.
>          01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
>     'schemaName' from the ResultSetMetaData is empty-string and not
>     applicable hence. Will not try to lookup column properties via
>     DatabaseMetaData.getColumns.
>          01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=0, columnName=name) - start
>          01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=0, columnName=label) - start
>          01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=0, columnName=label) - start
>          01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=1, columnName=name) - start
>          01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=1, columnName=label) - start
>          01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
>     getValue(row=1, columnName=label) - start
>          ....
>
>     As you see - `getValue(row=X)` uses `label` as name for `table2` and
>     `table3` columns and not `table2_label` and `label` as defined in
>     query...
>
>     I am using MySQL database.
>
>     Thank you for your time!
>     Laimonas
>
>     ------------------------------------------------------------------------------
>     Everyone hates slow websites. So do we.
>     Make your web apps faster with AppDynamics
>     Download AppDynamics Lite for free today:
>     http://p.sf.net/sfu/appdyn_d2d_feb
>     _______________________________________________
>     dbunit-user mailing list
>     [hidden email]
>     <mailto:[hidden email]>
>     https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>
>
>
> --
> Life is interfering with my game
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
>
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: DBUnit ignores column aliases, when creating dataset from query?

Laimonas Selenis-2
In reply to this post by John Hurst-2
Hi,

I have created a standalone test (attached to this email and also as pastebin.com links below) and it proves the issues I am having:

[ ERROR] DbUnitAssert - junit.framework.ComparisonFailure: column mismatch (table=test_dataset) expected:<[label, [table2_]label]> but was:<[label, []label]>

What is more interesting, if I do replace the SQL part "SELECT <...> `table2`.`label` AS `table2_label`"  with following: "SELECT <...> CONCAT(`table2`.`label`) AS `table2_label`" - then the column alias is being used as expected and actual dataset matches the expected one. But if there is no function used - then the original column name is used instead of an alias...

I hope someone can clarify this behaviour?

Thanks!
Laimonas Selenis

P.S. the pastebin links:

Test source: http://pastebin.com/5VFMzh6h
Expected dataset: http://pastebin.com/QjunfbZR
DB Structure: http://pastebin.com/wcuWCR7J


On Sat, Mar 2, 2013 at 4:57 AM, John Hurst <[hidden email]> wrote:
Hello,

As far as I know what you are trying to do should be fine. Column aliases should work as you expect.

I tried with an example from my current work code base and when I alias the column without changing my expected results dataset, my test fails. When I change the result dataset to match the column alias, it works. This supports your expected behavior. My code is also using IDatabaseConnection#createQueryTable(String tableName, String sql). I am using Oracle but that should make no difference.

Perhaps you could do a standalone test case?

Regards

John Hurst



On Sat, Mar 2, 2013 at 9:33 AM, Laimonas Selenis <[hidden email]> wrote:
Hi,

Maybe someone could tell me if it is expected behavior or maybe a bug:

I have two datasets, which I want to compare with DBUnit: one defines as
XML file, the other I am trying to create using a query:

     dbConnection.createQueryTable("my_table", "SELECT ...");

Select goes over couple of tables, where the few of them has the same
column names, so I am using aliases to distinguish between them. The
query runs fine, but seems that while creating a dataset, DBUnit ignores
the column aliases and uses the original names instead. This clearly is
seen in DEBUG log:

     01.03.13 14:18:43,228 [ DEBUG] AbstractDatabaseConnection -
createQueryTable(resultName=my_table, sql=
             SELECT `table1`.`name`
                 , `table2`.`label` AS `table2_label`
                 , `table3`.`label`
             FROM `table1`
                 , `table2`
                 , `table3`
             WHERE `table1`.`id` = `table2`.`table1_id`
                 AND `table2`.`id` = `table3`.`table2_id`
         ) - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,228 [ DEBUG] DatabaseConfig$Configurator -
Statement fetch size set to 100
     01.03.13 14:18:43,240 [ DEBUG] DatabaseDataSourceConnection -
getConnection() - start
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] DatabaseTableMetaData - The
'schemaName' from the ResultSetMetaData is empty-string and not
applicable hence. Will not try to lookup column properties via
DatabaseMetaData.getColumns.
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=name) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=0, columnName=label) - start
     01.03.13 14:18:43,240 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=name) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     01.03.13 14:18:43,241 [ DEBUG] ForwardOnlyResultSetTable -
getValue(row=1, columnName=label) - start
     ....

As you see - `getValue(row=X)` uses `label` as name for `table2` and
`table3` columns and not `table2_label` and `label` as defined in query...

I am using MySQL database.

Thank you for your time!
Laimonas

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester  
Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the  
endpoint security space. For insight on selecting the right partner to
tackle endpoint security challenges, access the full report.
http://p.sf.net/sfu/symantec-dev2dev
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user

dataset-test.zip (2K) Download Attachment