Some of the columns in the query have been set to NULL intentionally.

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.

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

By the way, in the UI all columns are displayed correctly.

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.

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.

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.

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>

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

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

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

I submitted this patch on gerrit:
https://gerrit.libreoffice.org/#/c/52662/

The UI also shows the aliases.

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?

(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

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
.

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.

(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

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

(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

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

(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

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

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.

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.

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

If there is an alias for the column, can you use that one instead of the unique CONSTANT<n> name?

For both the prepared statement and result set?

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.

Or do you suggest to generally use rs.metadata.getcolumnlabel directly instead of rs.columns.elementnames in the macro itself?

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

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.