Download it now!
Bug 83685 - Unaliased columns bound in table grid control using SQL functions do not display data
Summary: Unaliased columns bound in table grid control using SQL functions do not disp...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-09 19:59 UTC by senya
Modified: 2019-03-29 13:38 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
File with issue (40.72 KB, application/vnd.oasis.opendocument.database)
2014-09-09 19:59 UTC, senya
Details
File created by reproducible steps (11.67 KB, application/vnd.oasis.opendocument.database)
2015-03-26 18:32 UTC, senya
Details

Note You need to log in before you can comment on or make changes to this bug.
Description senya 2014-09-09 19:59:47 UTC
Created attachment 106005 [details]
File with issue

I've got a LO: Base file, containing forms. One of the forms has table, and this table shows results of query.

On Ubuntu 14.04 LO version (from repositories) it works fine, while in Windows versions it shows empty columns, though it have to show data there.

To reproduce:
1. Open database attached to this post
2. Open forms tab
3. Open form called "A_Form"
4. Look at columns 3,4,5.
5. Should have data in there.

Tested on Windows XP and 8.1.
JRE tested 1.7u65 and 1.7u67.
Comment 1 senya 2014-09-09 20:53:10 UTC
Giving alias to column headers of the query in query editor (and pointing them in the form editor) overrides the problem.
Comment 2 Alex Thurgood 2014-10-21 09:23:56 UTC
@Senya: the file contains macros, are these necessary for your form to work ?

If I disable macro execution, I confirm that data not displayed in form grid for three columns
Comment 3 Alex Thurgood 2014-10-21 09:34:50 UTC
Reworded title to reflect findings
Comment 4 Alex Thurgood 2014-10-21 09:35:33 UTC
Confirmed
Comment 5 senya 2014-10-21 09:37:20 UTC
Well, I had cleared all macros from the original document, before I posted it there. I checked it now - there is no macros in the attachment (you can check it in macros managment dialog). But it still asks this question on start. Isn't it an other bug?
Comment 6 Alex Thurgood 2014-10-21 09:40:58 UTC
(In reply to senya from comment #5)



> start. Isn't it an other bug?


Yes, possibly already known, seems to ring a bell with me.

Anyway, I tested on 


Version: 4.4.0.0.alpha0+
Build ID: d807cba9ee60cb1404b54addf9cd3e54de89f331

my OSX master build and I can still reproduce the problem, thanks for your input.
Comment 7 Robert Großkopf 2014-10-21 13:58:47 UTC
The data of the columns 3,4,5 in the form aren't linked well to the fields of the query. When see the properties of the column, for example, it shows 'SUM( "Отдано" )' as datafield. But the GUI needs the datafield, which could be chosen:  'SUM(SYSTEM_SUBQUERY."Отдано")'. 
Start the query, where the form based on, in the GUI. It also shows  'SUM(SYSTEM_SUBQUERY."Отдано")' as the alias for the field, not 'SUM( "Отдано" )'.
If you create the query with the wizard all will work. With an alias it will work also.

I don't know if we could call this a bug. Gives the same result here with all Versions since LO 3.4 - OpenSUSE 12.3, 64bit rpm Linux.
How did you create the form?

--- Macrowarning: Appears, because the macro-folder couldn't be deleted. You have to open the *.odb-file and delete the internal folder "Basic". Then the warning disappears. Don't find the report for this ...
Comment 8 senya 2014-10-21 14:07:41 UTC
(In reply to robert from comment #7)
> I don't know if we could call this a bug. Gives the same result here with
> all Versions since LO 3.4 - OpenSUSE 12.3, 64bit rpm Linux.

But it gives different (correct) result on Ubuntu 14.04 LO 4.2.6.3. The difference in behavior is certainly a bug.

> How did you create the form?

I created the form using the form wizard, but then I made lots of manual changes.
Comment 9 Robert Großkopf 2014-10-21 15:58:49 UTC
(In reply to senya from comment #8)
> (In reply to robert from comment #7)
> > I don't know if we could call this a bug. Gives the same result here with
> > all Versions since LO 3.4 - OpenSUSE 12.3, 64bit rpm Linux.
> 
> But it gives different (correct) result on Ubuntu 14.04 LO 4.2.6.3. The
> difference in behavior is certainly a bug.

You use Ubuntu. Which packages do you use? The packages of your distribution or the packages from LO directly? All packages from LO (beginning with 3.4, also tested with 4.1, 4.2 and 4.3) give the same result on my system: Works, if I create the form with the wizard on my system. Shows other names of a column of the query like it is created by the version of LO you use.
Comment 10 senya 2014-10-21 16:15:48 UTC
> You use Ubuntu. Which packages do you use? The packages of your distribution
> or the packages from LO directly? 

I am using packages from the repositories of Ubuntu. Build ID: 420m0(Build:3).

> All packages from LO (beginning with 3.4,
> also tested with 4.1, 4.2 and 4.3) give the same result on my system: Works,
> if I create the form with the wizard on my system. Shows other names of a
> column of the query like it is created by the version of LO you use.

I can't reproduce steps, that led the form to such state. If I just create new form using the wizard it works ok on every system. 

But it doesn't matter in the case. It has nothing to do with form creation. Here is input data - existing form, that makes LO behave differently depending on the type of platform. This is a bug, no matter if the data itself right or wrong. If the data is wrong it should fail everywhere. If the data is ok, it should work everywhere. But not fifty-fifty.
Comment 11 Julien Nabet 2014-10-21 17:34:05 UTC
senya: what's your LO version on Windows? What's the LO version of Ubuntu 14.04?

For your information, last stable LO version is 4.3.2 that you can retrieve on Windows via official website (see http://www.libreoffice.org/download/libreoffice-fresh/) and for Ubuntu from LO ppa (see https://launchpad.net/~libreoffice/+archive/ubuntu/ppa)
Could you do the update and compare between platforms if you have the same thing or not?
(please don't forget to rename your LO directory profile so we're sure we compare with a clean LO version, see https://wiki.documentfoundation.org/UserProfile)
Comment 12 senya 2014-10-22 11:52:33 UTC
(In reply to Julien Nabet from comment #11)

I tested version 4.3.2 provided by your links. Both tests was on fresh installs on test VMs with WinXP and Ubuntu 14.04. It shows exactly the same result I have reported before. Columns 3-5  are empty on Windows and they contain data on Ubuntu.
Comment 13 Robert Großkopf 2014-10-22 14:13:56 UTC
So please run the query in Ubuntu. If you see fields like 'SUM( "Отдано" )', this behavior is a special behavior of the Ubuntu-repository. It has never been a behavior of the original LO-repositories I could test here for *.rpm-64bit. 

LO-versions in Windows and also AOO have the same behavior as the original LO-rpm-packages. All this versions will give the columns an alias, if it isn't defined by you: 'SUM(SYSTEM_SUBQUERY."Отдано")'. Could be this is set, because it is the only way to set a query as a subquery for another query.

So I would report this bug to Ubuntu, not to LO.
Comment 14 Alex Thurgood 2015-01-03 17:38:33 UTC Comment hidden (no-value)
Comment 15 senya 2015-03-22 21:47:49 UTC
Robert,
so you mean, that Ubuntu LO version generates wrong .odb file that shouldn't really work anywhere? And if I had created the form the same way in any other (non-Ubuntu) version of LO, then I would have got a different output file?

Then I need to present the steps of the form creation to show that difference in output.
Comment 16 Robert Großkopf 2015-03-23 16:11:50 UTC
(In reply to senya from comment #15)
> Robert,
> so you mean, that Ubuntu LO version generates wrong .odb file that shouldn't
> really work anywhere? 

Not a wrong *.odb-file, but a wrong connection from fields of the query to the form.
> 
> Then I need to present the steps of the form creation to show that
> difference in output.

We would need the steps which would show the way to such a form. So we could see if any other system would give the same result witrh the same not working connection.
Comment 17 senya 2015-03-26 18:32:45 UTC
Created attachment 114387 [details]
File created by reproducible steps

Here is the steps to create failing document on Ubuntu 14.04 LO 4.2.7.2.
1) Create new Base document with HSQLDB.
2) Create a table with primary key and integer parameter (lets call it VALUE).
3) Create a query. In designer add the table to the query, add VALUE and select Summ in the Function section below the field name in the designer. Don't make any aliases for the field of query.
4) Create a form. Link the form to the query. Add a grid to the form and reflect the only query field in the grid's column.
5) profit

File attached.
Difference in behaviour noticed between Ubuntu and Windows versions of LO with that file.
Comment 18 senya 2015-03-26 18:36:41 UTC
Forgot to mention, if it is not obvious, that to see the issue you have to fill the table with some data.
Comment 19 Robert Großkopf 2015-04-03 15:39:28 UTC
Have tested with all the steps of comment17. Test-version here LO 4.3.6.2. The tablecontrols will show the content of the field where I had chosen SUM. Had tested to open the file in LO 4.2.3.1 (installed many versions here for testing). Gives the same result.
This is the created query:
SELECT SUM( "Zahl" ) FROM "Table"
No alias inside.
Data were shown in the form as
SUM("Table"."Zahl")

It is the same behavior with your database. I create a form and there is shown
SUM("table"."VALUE")
but in your form is only shown
SUM("Value")
If I try to edit your form it shows here no field SUM("VALUE"), which I could chose in the properties. Only SUM("table"."VALUE").

I am not able to reproduce the buggy behavior here on my system (OpenSUSE 13.2 64bit rpm Linux). Seems the packages of UBUNTU don't work the way the original packages of LO and AOO (tested here with AOO 4.0.1) would work.

LO 4.2.7.2 isn't supported any more. No fix. So why don't you try with packages for *.deb from LO-Website?
Comment 20 tommy27 2016-04-16 07:23:30 UTC Comment hidden (obsolete)
Comment 21 QA Administrators 2017-05-22 13:23:35 UTC Comment hidden (obsolete)
Comment 22 Drew Jensen 2019-03-29 13:38:18 UTC
Tested with Ubuntu 18.04.1 and LO 6.2.2

First - the column in the example database does not have the full alias name for the column in the grid control definition, which is why the data is not retrieved. I believe this was a bug in the form wizard back with the older version.

Using 6.2.2 I first checked that fixing the column name in the existing form did indeed fix the data problem. It did.

Next I used 6.2.2 to create a new form, using the form wizard, based on the query and the resulting form worked as expected.

Changing this to status 'Works For Me'.