|
From: Gerd W. <ger...@t-...> - 2025-09-14 19:23:33
|
On 9/14/25 18:15, John Hardin wrote: > On Sat, 13 Sep 2025, Gerd Wagner wrote: >> On 9/11/25 19:51, info wrote: >>> SquirrelSQL 4.8.0 displays bit(n) fields where n>1 as true/false, >>> whereas >>> they should be displayed as integers of the corresponding width as for >>> example MySQL Workbench does. >>> >> >> Sorry, I can't reproduce you problem. >> My MYSQL DB does not allow values other than 0, 1, true, false in >> columns of type BIT. I'm using MySQL version 8.0.17 and JDBC-Driver >> "MySQL Connector/J" version mysql-connector-java-8.0.15 >> >> Gerd > > That sounds incorrect (or at least _incomplete_) per the documentation: > > https://docs.oracle.com/cd/E17952_01/mysql-8.4-en/bit-type.html > > "bit(n)" is not a single bit, it's an array of n bits. > > Squirrel should be aware of "bit" vs. "bit(n)" and should not display > data in a column of type "bit(n>1)" as just true/false. That is (badly) > mininterpreting the data. > > I'd suggest the best default is the native "b'value'" notation from the > documentation, but that might not be easily platform-portable. Does > Squirrel support other platform-specific display formatting for > platform-specific data types? > > https://docs.oracle.com/cd/E17952_01/mysql-8.4-en/bit-value- > literals.html > > The alternative would be to display them as integers as the OP says > MySQL Workbench does. > > "In numeric contexts, MySQL treats a bit literal like an integer. To > ensure numeric treatment of a bit literal, use it in numeric context. > Ways to do this include adding 0 or using CAST(... AS UNSIGNED)." > > OP: For the moment you can probably work around this by querying > "CAST(bitn_col AS UNSIGNED)", "(bitn_col + 0)" or "BIN(bitn_col)" as > noted in the MySQL bit-value-literals documentation, rather than > querying just raw "bitn_col". > > OP: I suggest opening a bug for this in the bug tracker referenced from > the Squirrel home page: > > https://github.com/squirrel-sql-client/squirrel-sql-code/issues > Ups, sorry, I didn't pay intention to the (n) of bit(n). I checked it out now. The problem is that bit(n) is not a common SQL data type, at least there seems no appropriate representation in the JDK class java.sql.Types. This might be the reason why the implementers of the MySQL JDBC driver decided to deliver the type BIT(=-7) for columns of type bit(n). A probably better choice would have been INTEGER(=4). I attach a short Java program that reproduces the problem. You may want to contact the JDCB driver implementers about this issue. Gerd P.S.: Your workaround CAST(bitArrayTestValue AS UNSIGNED) works for me, too. |
