Bug 88631 - EDITING - mysql(jdbc) tinyint(1) fields display as Boolean tickboxes in table view mode
Summary: EDITING - mysql(jdbc) tinyint(1) fields display as Boolean tickboxes in table...
Status: CLOSED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: x86-64 (AMD64) macOS (All)
: medium critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-20 16:23 UTC by Alex Thurgood
Modified: 2015-01-20 17:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
test dump file (1.92 KB, text/plain)
2015-01-20 16:23 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2015-01-20 16:23:01 UTC
Created attachment 112557 [details]
test dump file

See bug 88601, comment 2, and comment 3

1) Using mysql-connector-java-5.1.21 JDBC connector
2) Create a test table from mysql cli :

 CREATE TABLE `Table1` (
  `Field1` tinyint(1) unsigned NOT NULL,
  `Field2` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Field2`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1


3) Add the following data :

+--------+--------+
| Field1 | Field2 |
+--------+--------+
|    244 |      1 |
|    255 |      2 |
|    213 |      3 |
|    128 |      4 |
|      1 |      5 |
|      2 |      6 |
|      4 |      7 |
|      5 |      8 |
|    255 |      9 |


Have enclosed dump file
Comment 1 Alex Thurgood 2015-01-20 16:25:46 UTC
Loos of data occurs when trying to manipulate the data in the tinyint fields from LO as the user can only tick or untick the boxes.

If the box is unticked, the value of tinyint(1) gets set to zero >>> dataloss.
Comment 2 Alex Thurgood 2015-01-20 16:27:37 UTC
This bug also prevents data entry.
Comment 3 Alex Thurgood 2015-01-20 16:30:26 UTC
Also present in 


Version: 4.5.0.0.alpha0+
Build ID: 15db0307fe6429238be72612cccd4a1df5e1e97c
Locale: fr_
Comment 4 Lionel Elie Mamane 2015-01-20 16:47:25 UTC
https://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html#idm140272205367040

MySQL Type Name     Returned as Java Class
TINYINT	            java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.
Comment 5 Alex Thurgood 2015-01-20 16:56:07 UTC
Hmm, ok.

If I set the connection string with that parameter set to false, reload the tables, I get the following error when trying to read a table with the value of 244 :

SQL Status: 22003

'244' in column '1' is outside valid range for the datatype TINYINT.
Comment 6 Alex Thurgood 2015-01-20 17:00:04 UTC
(In reply to Alex Thurgood from comment #5)
> Hmm, ok.
> 
> If I set the connection string with that parameter set to false, reload the
> tables, I get the following error when trying to read a table with the value
> of 244 :
> 
> SQL Status: 22003
> 
> '244' in column '1' is outside valid range for the datatype TINYINT.

Forget that last comment, I was testing on LO 3.6.x. Testing on master 4500 with that switch displays the correct values.
Comment 7 Lionel Elie Mamane 2015-01-20 17:11:25 UTC
(In reply to Alex Thurgood from comment #5)

> If I set the connection string with that parameter set to false, reload the
> tables, I get the following error when trying to read a table with the value
> of 244 :
> 
> SQL Status: 22003
> 
> '244' in column '1' is outside valid range for the datatype TINYINT.

JDBC has no way to express an unsigned column (that concept does not exist in Java, and does not exist in the SQL standard), so it is presented by the JDBC driver as a *signed* tinyint column, whose valid values are -128 to +127.

FYI, you can use TINYINT columns as integers without unsetting tinyInt1isBit by using TINYINT(4) instead of TINYINT(1).