Download it now!
Bug 116890 - Firebird: empty columns gone with prepared statement
Summary: Firebird: empty columns gone with prepared statement
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0 target:6.0.4
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-08 21:58 UTC by Gerhard Schaber
Modified: 2018-04-19 12:54 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample database (6.55 KB, application/vnd.sun.xml.base)
2018-04-08 21:58 UTC, Gerhard Schaber
Details
bt with debug symbols (13.60 KB, text/plain)
2018-04-10 05:23 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Schaber 2018-04-08 21:58:07 UTC
Created attachment 141215 [details]
Sample database

- Open the attached test file.
- Run the included macro ExportDataExcelvorlage.
- It is supposed to create an Excel file with the same number of columns as the query "MitgliederVerbandExcelvorlage", but it removes most of the empty columns, and instead adds one named "CONSTANT". oPrepStatement.Columns only returns 10 columns.

It would be good to have at least a workaround for this.

By the way, the same code works perfectly with HSQLDB.
Comment 1 Gerhard Schaber 2018-04-08 22:13:23 UTC
Some of the columns in the query have been set to NULL intentionally.
Comment 2 Gerhard Schaber 2018-04-08 22:28:30 UTC
Tested with:
Version: 6.1.0.0.alpha0+
Build ID: d39a8e791618a40328c0f90bece3cc246dcf57f7
CPU threads: 8; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-05_23:46:48
Locale: de-AT (de_AT); Calc: group

and with 6.0.3.2.
Comment 3 Alex Thurgood 2018-04-09 07:25:41 UTC
Confirming with

Version: 6.0.3.2
Build ID: 8f48d515416608e3a835360314dac7e47fd0b821
Threads CPU : 4; OS : Mac OS X 10.13.4; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 4 Gerhard Schaber 2018-04-09 07:27:11 UTC
By the way, in the UI all columns are displayed correctly.
Comment 5 Julien Nabet 2018-04-09 07:42:26 UTC
Tamas: I haven't tried it for the moment (I'll be able to after my daytime job) but thought you might be interested too since it concerns Firebird.
Comment 6 Gerhard Schaber 2018-04-09 11:49:55 UTC
I have managed to make it work. Usually I use the results of an executed query to get the columns. I am not sure why I used the columns of the prepared statement here.

Does not work:
oResult = oPrepStatement.executeQuery
ccols = oPrepStatement.Columns

Does work:
oResult = oPrepStatement.executeQuery
ccols = oResult.Columns

I have fixed by macros and everything is working for me, now. I might make sense to keep this open, since the expected behavior is that the columns returned by the prepared statements are the same as the ones of the result of the executed query.
Comment 7 Gerhard Schaber 2018-04-09 12:11:26 UTC
Correction, when I use the columns of the result set, the number of the columns is correct, but the empty ones are all named CONSTANT<n> where <n> is empty for the first empty column, and an increasing number for every other empty column.
Comment 8 Julien Nabet 2018-04-09 21:03:39 UTC
On pc Debian x6-64 with master sources updated today, I could reproduce this.
I noticed that in /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/ResultSetMetaData.cxx, connectivity::firebird::OResultSetMetaData::getColumnName, I could already retrieve "CONSTANT" so I think it seems to come from firebird engine. But perhaps I'm wrong.
I also noticed the difference between sqlname and aliasname for CONSTANT, eg: 
(gdb) p m_pSqlda->sqlvar[column-1].sqlname
$34 = "CONSTANT", '\000' <repeats 23 times>
(gdb) p m_pSqlda->sqlvar[column-1].aliasname
$35 = "Strasse", '\000' <repeats 24 times>
Comment 9 Julien Nabet 2018-04-09 21:04:24 UTC
Forgot to tell I started from these logs repeated several times:
warn:legacy.osl:14544:14544:dbaccess/source/core/api/column.cxx:204: OColumns::append: Column already exists
warn:legacy.osl:14544:14544:connectivity/source/sdbcx/VCollection.cxx:504: Element already exists
Comment 10 Lionel Elie Mamane 2018-04-10 04:43:58 UTC
(In reply to Julien Nabet from comment #8)

> I also noticed the difference between sqlname and aliasname for CONSTANT,
> eg: 
> (gdb) p m_pSqlda->sqlvar[column-1].sqlname
> $34 = "CONSTANT", '\000' <repeats 23 times>
> (gdb) p m_pSqlda->sqlvar[column-1].aliasname
> $35 = "Strasse", '\000' <repeats 24 times>

Looks like getColumnName should return aliasname and not sqlname.


(In reply to Julien Nabet from comment #9)
> Forgot to tell I started from these logs repeated several times:
> warn:legacy.osl:14544:14544:dbaccess/source/core/api/column.cxx:204:
> OColumns::append: Column already exists
> warn:legacy.osl:14544:14544:connectivity/source/sdbcx/VCollection.cxx:504:
> Element already exists

Looks like different columns are added to the collection with the same name, and thus are not added to the collection (only the first to have a particular name will be added?). I think if you but a breakpoint at the place of these warnings, you will find the problem by going up the stack.
Comment 11 Julien Nabet 2018-04-10 05:23:54 UTC
Created attachment 141243 [details]
bt with debug symbols

I attached bt corresponding to the location where we get column names.

In brief, some column names are ok others have values "CONSTANT". So since there are several "CONSTANT" columns, there are logs of duplicates and at the end there's only 1 column "CONSTANT".
Comment 12 Julien Nabet 2018-04-10 05:38:48 UTC
I submitted this patch on gerrit:
https://gerrit.libreoffice.org/#/c/52662/
Comment 13 Gerhard Schaber 2018-04-10 05:59:17 UTC
The UI also shows the aliases.
Comment 14 Gerhard Schaber 2018-04-10 12:51:33 UTC
According to https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel(int) there is a difference between getColumnLabel and getColumnName. Maybe constant columns are just a special case where always the alias is used. getColumnName returns the original name according to the manual. Is guess this is the case for HSQLDB. Any idea why it nevertheless shows the alias, or is it always using getColumnLabel for LO Basic?
Comment 15 Lionel Elie Mamane 2018-04-10 14:04:44 UTC
(In reply to Gerhard Schaber from comment #14)

> getColumnName returns the original name according to the manual.

It does not make much sense to me, but it seems you are right. A very clear source is https://stackoverflow.com/questions/4271152/getcolumnlabel-vs-getcolumnname

That is how the MySQL C++ connector does it. However, it still keeps unique column names by appending "1", "2", etc when the same column is taken twice. E.g. "colName", then "colName1", "colName2", etc. It uses an empty name for the first computed column, then "1", "2", etc.

The MySQL JDBC also returns the original name. Computed columns have the same string (the alias) in getColumnName() and getColumnLabel(). It lets duplicate names for different result columns, though. However, recordset.columns.ElementNames has "1", "2", etc appended to 

The MySQL ODBC driver always returns the *alias* name in both getColumnName() and getColumnLabel(), with duplicates. recordset.columns.ElementNames has "1", "2", etc appended for unicity. ODBC has the notion of column label (with SQLColAttribute(..., SQL_DESC_LABEL,...)) and column "original" name (with SQLColAttribute(..., SQL_DESC_NAME,...)), the LibreOffice SDBC<->ODBC driver makes the difference, but the MySQL ODBC driver doesn't.

> Is guess this is the case for HSQLDB.

Well, no, it is not. Everything has the alias name. I tried (Tasks table from wizard):

Sub Main
	Dim DBDocUI as Object
	on error resume next
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if

	dim s as Object
	s = DBDocUI.ActiveConnection.createStatement()
	dim r as Object
	rs = s.executeQuery("SELECT ""TaskID"" AS ""TID1"", ""TaskID"" AS ""TID2"" FROM ""Tasks""")
	MsgBox rs.metadata.getColumnName(1)
	MsgBox rs.metadata.getColumnLabel(1)
	XRay rs.columns.ElementNames
End Sub
Comment 16 Gerhard Schaber 2018-04-10 14:11:44 UTC
Yes, I am aware that in the macro always the alias is used fpr HSQLDB. I was just wondering, if LO uses the original column name anywhere and whether the constant columns are replaced with the alias there.

In any case, with Firebird in a macro, when I use the columns from the result set, the constant columns are numbered, but with the preparedstatement they are not. 

And with HSQDL always the aliases are returned when using a macro
.
Comment 17 Lionel Elie Mamane 2018-04-10 14:53:35 UTC
It still seems the problem with Firebird-in-Libreoffice preparedstatement is that the columns collection is constructed with duplicate column names, which means duplicates are dropped.

This could be a Firebird bug. Julien, could you please confirm that by tracing the call to the Firebird API in both cases, and see that it is the Firebird API that returns something different? Then we can file a firebird bug for that.

To be clear, the two cases are (in Gerhard's code):

oResult = oPrepStatement.executeQuery
ccols = oPrepStatement.Columns

and

oResult = oPrepStatement.executeQuery
ccols = oResult.Columns

In all cases, what you want to trace is connectivity::firebird::OResultSetMetaData::getColumnName
It accesses
 m_pSqlda->sqlvar[column-1].sqlname

If in one case, several columns have the same name "CONSTANT" and in the other case all columns have unique names like "CONSTANT", "CONSTANT1", "CONSTANT2", etc, then  we have definitely found the problem, it is in Firebird.

For a simpler test than Gerhard's code, take his database, but execute this basic code:

Sub Main
	Dim DBDocUI as Object
	on error resume next
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if
	on error goto 0

	dim s as Object
	's = DBDocUI.ActiveConnection.createStatement()
	s = DBDocUI.ActiveConnection.prepareStatement("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	dim r as Object
	'rs = s.executeQuery("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	rs = s.executeQuery()
	dim i as integer
	for i=1 to rs.metadata.ColumnCount
		MsgBox i & ": " & rs.metadata.getColumnName(i) & " " &  rs.metadata.getColumnLabel(i)
	next i
	XRay rs.columns.ElementNames
	XRay rs.columns.getByName("ID")
	XRay rs.columns.getByName("ID1")
End Sub


Using alternatively the two definitions of s and rs, which will be your two cases.

Thanks in advance.
Comment 18 Lionel Elie Mamane 2018-04-10 14:59:48 UTC
(In reply to Lionel Elie Mamane from comment #17)
Sorry, wrong test code. One test case is:

Sub Main
	Dim DBDocUI as Object
	on error resume next
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if
	on error goto 0

	dim s as Object
	's = DBDocUI.ActiveConnection.createStatement()
	s = DBDocUI.ActiveConnection.prepareStatement("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	'dim rs as Object
	'rs = s.executeQuery("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	'rs = s.executeQuery()
	dim i as integer
	for i=1 to s.metadata.ColumnCount
		MsgBox i & ": " & s.metadata.getColumnName(i) & " " &  s.metadata.getColumnLabel(i)
	next i
	XRay s.columns.ElementNames
	'XRay s.columns.getByName("ID")
	'XRay s.columns.getByName("ID1")
End Sub

and the other is:

Sub Main
	Dim DBDocUI as Object
	on error resume next
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if
	on error goto 0

	dim s as Object
	's = DBDocUI.ActiveConnection.createStatement()
	s = DBDocUI.ActiveConnection.prepareStatement("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	'dim rs as Object
	'rs = rs.executeQuery("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	rs = s.executeQuery()
	dim i as integer
	for i=1 to s.metadata.ColumnCount
		MsgBox i & ": " & rs.metadata.getColumnName(i) & " " &  rs.metadata.getColumnLabel(i)
	next i
	XRay rs.columns.ElementNames
	'XRay s.columns.getByName("ID")
	'XRay s.columns.getByName("ID1")
End Sub
Comment 19 Lionel Elie Mamane 2018-04-10 15:00:21 UTC
Rah... Second test case, this time correct, is:

Sub Main
	Dim DBDocUI as Object
	on error resume next
	DBDocUI = ThisDatabaseDocument.currentController
	if not DBDocUI.isConnected then
		DBDocUI.connect
	end if
	on error goto 0

	dim s as Object
	's = DBDocUI.ActiveConnection.createStatement()
	s = DBDocUI.ActiveConnection.prepareStatement("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	'dim rs as Object
	'rs = rs.executeQuery("SELECT ""ID"" AS ""TID1"", ""ID"" AS ""TID2"" FROM ""MitgliederVerband""")
	rs = s.executeQuery()
	dim i as integer
	for i=1 to rs.metadata.ColumnCount
		MsgBox i & ": " & rs.metadata.getColumnName(i) & " " &  rs.metadata.getColumnLabel(i)
	next i
	XRay rs.columns.ElementNames
	'XRay s.columns.getByName("ID")
	'XRay s.columns.getByName("ID1")
End Sub
Comment 20 Lionel Elie Mamane 2018-04-10 15:03:42 UTC
(In reply to Gerhard Schaber from comment #16)

> In any case, with Firebird in a macro, when I use the columns from the
> result set, the constant columns are numbered, but with the
> preparedstatement they are not.

Note that if one selects two columns with the same alias, HSQLDB shows a similar bug.

In my "simplified" testcase of selecting twice the same column with different aliases, HSQLDB has the same bug in preparedstatement.columns, but again not in resultset.columns
Comment 21 Julien Nabet 2018-04-10 18:39:48 UTC
(In reply to Lionel Elie Mamane from comment #17)
> ...
> To be clear, the two cases are (in Gerhard's code):
> 
> oResult = oPrepStatement.executeQuery
> ccols = oPrepStatement.Columns
> 
> and
> 
> oResult = oPrepStatement.executeQuery
> ccols = oResult.Columns
> 
> In all cases, what you want to trace is
> connectivity::firebird::OResultSetMetaData::getColumnName
> It accesses
>  m_pSqlda->sqlvar[column-1].sqlname
> 
> If in one case, several columns have the same name "CONSTANT" and in the
> other case all columns have unique names like "CONSTANT", "CONSTANT1",
> "CONSTANT2", etc, then  we have definitely found the problem, it is in
> Firebird.
I got "CONSTANT" several times for both cases.
Comment 22 Lionel Elie Mamane 2018-04-10 19:04:52 UTC
(In reply to Julien Nabet from comment #21)
> (In reply to Lionel Elie Mamane from comment #17)

>> If in one case, several columns have the same name "CONSTANT" and in the
>> other case all columns have unique names like "CONSTANT", "CONSTANT1",
>> "CONSTANT2", etc, then  we have definitely found the problem, it is in
>> Firebird.

> I got "CONSTANT" several times for both cases.

This means the difference is in LibreOffice, and it is LibreOffice that adds the "1", "2", "3", etc to duplicate names. The case "ccols = oPrepStatement.Columns" is done in dbaccess/source/core/api/preparedstatement.cxx function OPreparedStatement::getColumns, there one sees:

for (sal_Int32 i = 0, nCount = xMetaData->getColumnCount(); i < nCount; ++i)
{
    // retrieve the name of the column
    OUString aName = xMetaData->getColumnName(i + 1);
    OResultColumn* pColumn = new OResultColumn(xMetaData, i + 1, xDBMeta);
    m_pColumns->append(aName, pColumn);
}

That's the problematic bit of code. It needs to be changed to add "1", "2", etc to duplicate names.

The "ccols = oResult.Columns" case is probably handled in file resultset.cxx in the same directory, function OResultSet::getColumns(); there we see:


for ( sal_Int32 i = 0; i < nColCount; ++i)
{
    // retrieve the name of the column
    OUString sName = xMetaData->getColumnName(i + 1);
    ODataColumn* pColumn = new ODataColumn(xMetaData, m_xDelegatorRow, m_xDelegatorRowUpdate, i + 1, xDBMetaData);

    // don't silently assume that the name is unique - result set implementations
    // are allowed to return duplicate names, but we are required to have
    // unique column names
    if ( m_pColumns->hasByName( sName ) )
        sName = ::dbtools::createUniqueName( m_pColumns.get(), sName );

    m_pColumns->append( sName, pColumn );
}

The two lines
    if ( m_pColumns->hasByName( sName ) )
        sName = ::dbtools::createUniqueName( m_pColumns.get(), sName );
seem to be exactly what needs to be added to OPreparedStatement::getColumns
Comment 23 Julien Nabet 2018-04-10 19:30:15 UTC
(In reply to Lionel Elie Mamane from comment #22)
> (In reply to Julien Nabet from comment #21)
> > (In reply to Lionel Elie Mamane from comment #17)
> 
...
> This means the difference is in LibreOffice, and it is LibreOffice that adds
> the "1", "2", "3", etc to duplicate names
> ...
> The two lines
>     if ( m_pColumns->hasByName( sName ) )
>         sName = ::dbtools::createUniqueName( m_pColumns.get(), sName );
> seem to be exactly what needs to be added to OPreparedStatement::getColumns
I tested this and it indeed worked!
I updated the patch (code + comment) on gerrit as you may have seen.
Comment 24 Commit Notification 2018-04-10 21:56:40 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dc823f5fa4a5d2eca56297b9045e5962536c00f9

tdf#116890: make unique column names in prepared statement

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 25 Commit Notification 2018-04-11 08:55:04 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d78d17249a58d95b4aa2e8fe09f08e22f20c407&h=libreoffice-6-0

tdf#116890: make unique column names in prepared statement

It will be available in 6.0.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 Julien Nabet 2018-04-11 09:11:39 UTC
Let's put this one to FIXED since it's ok on master and 6.0 branches.

If someone think it'd be useful in 5.4, don't hesitate to tell but we're soon at EOL for 5.4 branch, so...
Comment 27 Gerhard Schaber 2018-04-19 09:53:23 UTC
If there is an alias for the column, can you use that one instead of the unique CONSTANT<n> name?
Comment 28 Gerhard Schaber 2018-04-19 09:54:36 UTC
For both the prepared statement and result set?
Comment 29 Gerhard Schaber 2018-04-19 10:41:01 UTC
So in my opinion, the code fragment in preparedstatement and resultset shoud use xMetaData->getColumnLabel instead of xMetaData->getColumnName. Only, if getColumnLabel is empty, it could use getColumnName. And if that is empty, then use the unique name.
Comment 30 Gerhard Schaber 2018-04-19 11:20:35 UTC
Or do you suggest to generally use rs.metadata.getcolumnlabel directly instead of rs.columns.elementnames in the macro itself?
Comment 31 Julien Nabet 2018-04-19 11:37:58 UTC
(In reply to Gerhard Schaber from comment #27, #28, #29, #30)
>...
> Or do you suggest to generally use rs.metadata.getcolumnlabel directly
> instead of rs.columns.elementnames in the macro itself?

I'm not expert enough to respond so I'll let Lionel speak.
Comment 32 Gerhard Schaber 2018-04-19 12:54:01 UTC
Intuitively I would expect rs.columns.ElementNames to return the aliases, just like the UI shows the aliases. But I don't know whether this would break anything with the other databases.