Download it now!
Bug 66171 - forms: ListBox BoundColumn==0 resets to 1 after save/load
Summary: forms: ListBox BoundColumn==0 resets to 1 after save/load
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.1 rc
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:4.2.0 target:4.1.0.2 target:4.0.5
Keywords:
Depends on:
Blocks:
 
Reported: 2013-06-25 21:07 UTC by tim
Modified: 2013-07-10 14:56 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
An HSQL version (11.42 KB, application/vnd.oasis.opendocument.database)
2013-06-27 07:49 UTC, tim
Details
Currency (1.98 KB, text/x-sql)
2013-06-27 10:44 UTC, tim
Details
AC table (2.07 KB, text/x-sql)
2013-06-27 10:44 UTC, tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2013-06-25 21:07:01 UTC
I have a MySql database with a Base front end.  Some of my listboxes use a lookup on a single column in a table, such as:

Bound field: 1
Data Field: CurrencyCode
Input required: Yes
List content: SELECT "CurrencyCode" FROM "MyAccounts"."Currency" AS "Currency" ORDER BY "CurrencyCode" ASC
Type of list content: sql

In 3.5.7 (ubuntu 12.04) this works fine.  I previously tried to set the bound column to 0 (ie the 1st field) but that always gets reset to 1 by Base.

The 'CurrencyCode' column in table 'Currency' table is a 3 character code, and is the primary key.

With a MySql database, using MySql native connector, on existing valid data, I get the following error:

   "The Contents of a combo box or list field could not be determined"

    "index out of range"

Trying to emulate this with an HSQL engine failed, it works as expected, so I am unable to post a demonstration odb.


I tried changing the list box to use 2 fields in the SELECT statement, as follows:

SELECT "CurrencyCode", "CurrencyCode" FROM "MyAccounts"."Currency" AS "Currency" ORDER BY "CurrencyCode" ASC

This works with MySql as well as HSQL, so I have a work-around, but I have to edit many list boxes to enable me to continue.

I tried placing the single-field sql statement in a Base Query rather than using sql in the control definition.  The query run on its own works.  When used as the source of the list box it gives the same error as above.

To summarise, it appears that there is a problem using a single (text) bound field in an sql or query lookup from a Mysql database to populate a list box. 

Given that I cannot supply a demo, I'm willing to try other experiments if it would be useful.
Comment 1 tim 2013-06-27 07:49:36 UTC
Created attachment 81527 [details]
An HSQL version

The attached ODB contains an HSQL database and a form, AC, that when opened shows items in Table AC with a list box containing Currency codes from Currency.  This works.

If you create the same two tables (AC and Currency) in MySQL, and modify the ODB and AC form to use them, it fails as described in my report.

I also tried using JDBC instead of MySQL Native Connector.  This also fails, albeit with a slightly different error message:

"Column index out of range, 2>1."
Comment 2 Alex Thurgood 2013-06-27 09:31:39 UTC
Hi Tim,

In your hsqldb file, the link between the two tables shows up as a 1-n join in the Relations dialog.

How have you got this set up in your mysql db ? Mysql doesn't enforce referential integrity if your are using 

In order to give us a better idea, could you post the output of :

SHOW CREATE TABLE tablename

for each table from your mysql console ?

Or better still, dump the database as sql output using mysqldump (which would give us the data too). My concern is that if I just try to convert your hsqldb table definitions from within LO, I might not get exactly what you have in your own db setup (field definitions, foreign keys, etc). 


Alex
Comment 3 Alex Thurgood 2013-06-27 09:34:15 UTC
(In reply to comment #2)
> Hi Tim,
> 
> In your hsqldb file, the link between the two tables shows up as a 1-n join
> in the Relations dialog.
> 
> How have you got this set up in your mysql db ? Mysql doesn't enforce
> referential integrity if your are using 


Ooops, pressed send too soon ;-) 

Mysql doesn't enforce referential integrity if you are not using the InnoDB storage engine.


Alex
Comment 4 Alex Thurgood 2013-06-27 10:35:26 UTC
So I set up my own test db using the table definitions provided in your hsqldb file and created a corresponding form manually (because the wizard is broken at the moment).

I can not reproduce your problem on my master build on OSX :

Version: 4.2.0.0.alpha0+
Build ID: 51c79f97a90eaf469d1d2531f3f0eccfafe0f94c

FWIW, here are my mysql table definitions :

CREATE TABLE `currency` (
  `CurrencyCode` varchar(3) NOT NULL,
  `CurrencyName` varchar(40) NOT NULL,
  `Default` tinyint(3) NOT NULL,
  `Base` tinyint(3) NOT NULL,
  `DefaultRatetoBase` decimal(10,0) NOT NULL,
  PRIMARY KEY (`CurrencyCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `Ac` (
  `bid` int(10) NOT NULL AUTO_INCREMENT,
  `Ac` varchar(100) DEFAULT NULL,
  `cur` varchar(3) NOT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


Then I copied over the data from your hsqldb tables to my mysql tables using the Append data function from the Copy Table Wizard that starts when you drag and drop one table to another db. This fails BTW if you don't append the db alias of the new table before the table name in the wizard dialog, I will see if there is already a bug report for that.

Next I set up my main form to be based on table Ac, and then substituted cur for a combobox using the following SQL query :

SELECT CurrencyCode from test.Currency

with SQL as the "Type of List Contents"

and set the properties of the combobox to be dropdown.

On testing, this works for me.


Alex
Comment 5 Alex Thurgood 2013-06-27 10:37:35 UTC
Adding Lionel, Julien, Robert to CC to see whether they can reproduce.
Comment 6 tim 2013-06-27 10:44:33 UTC
Created attachment 81542 [details]
Currency

This is the Currency table, AC to follow.
Comment 7 tim 2013-06-27 10:44:56 UTC
Created attachment 81543 [details]
AC table
Comment 8 tim 2013-06-27 10:46:18 UTC
It fails for me every time with Mysql with these two tables.
Comment 9 Alex Thurgood 2013-06-27 10:54:58 UTC
Tim,

It could be the constraint. What happens if you remove that constraint (my sample tables do not have it, as they are ISAM based) ?


Alex
Comment 10 Alex Thurgood 2013-06-27 11:03:23 UTC
Replaced my tables with yours. Still can not reproduce on OSX 10.8.4 with 4.2 master build.

The form works just fine for me. Will have to see about Linux later when I'm in front of the machine again.


Alex
Comment 11 tim 2013-06-27 11:35:51 UTC
The constraint makes no difference.  Neither does the data content.

I'm on Ubuntu 12.04 64 bit.  I started with a downloaded RC1 build from the website, then yesterday I added the ppa for pre-releases (http://ppa.launchpad.net/libreoffice/libreoffice-prereleases/ubuntu precise main) and installed:

Version: 4.1.0.1  Build ID: 410m0(Build:1)

No change in behaviour from the 4.1.0.1 RC1 version I first tried.

The Mysql version is 5.5.31.  I am using InnoDB.

When the problem first arose I only tried HSQL in order to prepare a demo to attach to this report, expecting it to fail.  I was very surprised when it worked.  I then  back-created the sample mysql database tables I sent you from scratch, in a brand new database, to make sure it wasn't something odd in my main system database.

I am using MySql Connector 1.0.3 (as well as having tried JDBC as reported previously).
Comment 12 Julien Nabet 2013-06-27 12:05:24 UTC
Alex: I'll try to find some time after my day time job. For your information Lionel pushed some fixes about ListBox 3 days ago. Perhaps one of them may have helped but surely Lionel will bring more accurate information:-)

Tim: what's your odbc version? Would it be possible you test with a recent daily build from master (future 4.2)? (see http://dev-builds.libreoffice.org/daily/master/)
Comment 13 tim 2013-06-27 12:25:28 UTC
I haven't tried odbc, just native mysql and jdbc.  I haven't got all the libraries loaded for odbc access.  If I get time I'll try that (but I'm currently hunting down another problem, this time with dates,which also used to work fine).
Comment 14 Alex Thurgood 2013-06-27 12:47:06 UTC
Also tested on Linux 32bit (Ubuntu 13.04 Raring Ringtail) with LO master build :

Version: 4.2.0.0.alpha0+
Build ID: ee311c55c999a68aa677faac69b3462975ad05d4


Still can't reproduce the problem.


Alex
Comment 15 Alex Thurgood 2013-06-27 12:51:47 UTC
Ah Tim, my apologies, I have been testing with Comboboxes... and not Listboxes :-/


When I swap my Combobox for a Listbox, save the form, then try and use the form, I see that the list is not displayed. If I try to save my data entry session with the form that doesn't display the list of elements, I then get :

SQL Status: 23000
Error code: 1452

Cannot add or update a child row: a foreign key constraint fails ("test"."AC", CONSTRAINT "AC_ibfk_1" FOREIGN KEY ("Cur") REFERENCES "Currency" ("CurrencyCode"))

So confirming that Listboxes based on SQL queries do not appear to work.


Alex
Comment 16 Alex Thurgood 2013-06-27 12:53:30 UTC
When I reopen the form, I see the same error message as you with regard to the out of range index.
Comment 17 Alex Thurgood 2013-06-27 12:54:39 UTC
Error message :
The contents of a combo box or list field could not be determined.

Error code: 1

index out of range



Alex
Comment 18 tim 2013-06-27 12:54:57 UTC
Not being a linux expert I can't test 4.2, since there's no deb version I can find.

Being too curious for my own good I did a very quick test using ODBC to mysql.  I got a similar error for the listbox, but this time the second line said:

[MYSQL][ODBC 5.1 Driver][mysqld-5.5.31-0ubuntu0.12.04.2]Invalid descriptor index
Comment 19 tim 2013-06-27 12:56:01 UTC
Alex,

Thank you for persisting.  I was beginning to think I was going slightly crackers.
Comment 20 Lionel Elie Mamane 2013-06-27 13:23:08 UTC
(In reply to comment #0)

> Bound field: 1
> Data Field: CurrencyCode
> Input required: Yes
> List content: SELECT "CurrencyCode" FROM "MyAccounts"."Currency" AS
> "Currency" ORDER BY "CurrencyCode" ASC
> Type of list content: sql
 
> In 3.5.7 (ubuntu 12.04) this works fine.

Hmmm... I don't immediately understand why it used to work. Maybe it would automatically use the first column if "Bound Field" was out of range.

> I previously tried to set the
> bound column to 0 (ie the 1st field) but that always gets reset to 1 by Base.

Does 4.1 still reset to 1? It does not for me. Set it to 0, IMHO it will work. If not, reopen.

In 3.5/3.6, set it to empty instead of zero. This should work (in all versions, also in 4.1).

> Trying to emulate this with an HSQL engine failed, it works as expected, so
> I am unable to post a demonstration odb.

For problems that don't happen with embedded HSQLDB, you can attach the .odb taht connects to MySQL *and* a dump of the MySQL database.

So, I'm closing the bugs under the assumption that my explanations above help. If they don't feel free to reopen.
Comment 21 tim 2013-06-27 13:49:28 UTC
I set the bound field to blank and saved the form.  I then opened the form, and got the same error.  

I edited the form again and looked at the field, the bound field was 1 again.

I have tried several times.  Nothing except 1 (or more) in the bound field seems to be acceptable.
Comment 22 Lionel Elie Mamane 2013-06-27 13:51:29 UTC
(In reply to comment #21)
> I set the bound field to blank and saved the form.  I then opened the form,
> and got the same error.  

> I edited the form again and looked at the field, the bound field was 1 again.

In 4.1.0.1rc1?
Comment 23 tim 2013-06-27 13:59:26 UTC
Since last night when I changed my set-up:

Version  4.1.0.1  Build ID: 410m0(Build:1) from the pre-release ubuntu ppa (as reported above).
Comment 24 Alex Thurgood 2013-06-27 14:54:51 UTC
Confirming Tim's findings also on OSX.

If you set the bound field to 0 as Lionel suggests, save the form, then switch to data entry mode, you can enter new data and the listbox will display its values correctly.

However, if you then close the form and re-open it, you get the error message about being beyond the index range. If you then switch to Form design mode, you can see that the listbox control has had its bound field reset to 1.


Alex
Comment 25 Alex Thurgood 2013-06-27 14:55:30 UTC
I am testing with master 

Version: 4.2.0.0.alpha0+
Build ID: 51c79f97a90eaf469d1d2531f3f0eccfafe0f94c


Alex
Comment 26 Lionel Elie Mamane 2013-06-27 15:27:30 UTC
(In reply to comment #24)

> If you set the bound field to 0 as Lionel suggests, save the form, then
> switch to data entry mode, you can enter new data and the listbox will
> display its values correctly.

> However, if you then close the form and re-open it, (...)
> the listbox control has had its bound field reset to 1.

Ah, it reset after a save/load cycle. I see. Working on it.
Comment 27 Lionel Elie Mamane 2013-06-27 16:32:13 UTC
Fixed in my dev tree. Push to official git soon.

Here's a better / more robust work-around: use a combobox instead of a listbox.
Comment 28 Commit Notification 2013-06-27 17:00:35 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

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

fdo#66171 ListBox correctly save empty or zero BoundColumn



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 29 Commit Notification 2013-06-28 12:55:25 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=19d08b0c655ff66c2948d6390e32e0ad0a1a67a3&h=libreoffice-4-1

fdo#66171 ListBox correctly save empty or zero BoundColumn


It will be available in LibreOffice 4.1.

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 30 Commit Notification 2013-06-28 13:04:00 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=60d4d5a4ec70186f64b12ebb485428e905283785&h=libreoffice-4-0

fdo#66171 ListBox correctly save empty or zero BoundColumn


It will be available in LibreOffice 4.0.5.

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 31 tim 2013-07-10 10:16:18 UTC
Confirm fixed in 4.1.0.2 (RC2) in 32 bit Linux Mint Debian.

I note that setting boundfield to 0 or 1 makes no difference when there is only 1 field, which is fine.  The 0 value does now get saved as stated.

Thanks.
Comment 32 Lionel Elie Mamane 2013-07-10 14:56:46 UTC
Setting to VERIFIED (FIXED), as per Tim's comment.