Float Or Double Unsigned Can't Be Done

[slimso]

When creating or altering tables I can't tick the “Unsigned” check for fields with datatypes float or double.

Is this a MySQL limitation? I've never encountered it before…

I have Community Edition version 5.22a and I first noticed the problem in 5.20.

Thank you, all you guys who shared your work and gave us, the small web developers, the best MySQL GUI I've seen! 😉

[peterlaursen]

no .. this is a SQLyog bug.

this SQL is valid:

Code:

alter table `test`.`a` add column `newcolumn` double UNSIGNED NULL after `lastcolumn`;

MySQL docs:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

.. does not mention anything particular about UNSIGNED and FLOATs and DOUBLEs

Also you can alter a negative number from SQLyog, svae, press “refresh” and it shows '0 (zero) – so the UNSIGNBED clause works with MySQL 5.0!

there could be some issue with old MySQL versions however … we need a littletime to research on this!

[slimso]

I've tested it and the feature works in MySQL 5.0.27-community-nt via phpMyAdmin on the same table (MyISAM).

Also SQLyog recognizes it as “float unsigned” but can't reassign the same datatype again.

So i guess it's just a tiny bug…

[peterlaursen]

fixed in the development tree.  Beta 2 with the fix will be released tonight or tomorrow.

[slimso]

peterlaursen wrote on Jan 29 2007, 02:03 PM:

fixed in the development tree.  Beta 2 with the fix will be released tonight or tomorrow.

Thanks for the quick action guys, i've checked beta 2 and the new stable release 5.23 and it does work, except …

Don't mean to nag you, but it still doesn't recognize the “unsigned” flag.

So when you open the “Alter table” dialog, the “Unsigned” checkbox is unchecked.

This causes a problem if you indeed alter the field, because it is modified back to “signed” without notice.

Again, the problem only appears for FLOAT or DOUBLE fields.

Thank you.

[peterlaursen]

It works fine here.

the SQL sent is

Code:

alter table `test`.`a1` add column `xx` float UNSIGNED NULL after `suman`, add column `yy` double UNSIGNED NULL after `xx`

or

Code:

alter table `test`.`a1` change `xx` `xx` float UNSIGNED NULL , change `yy` `yy` double UNSIGNED NULL

 

and the create statement is after that:

Code:

CREATE TABLE `a1` (
`bikas` int(11) default NULL,
`suman` varchar(25) default NULL,
`xx` float unsigned default NULL,
`yy` double unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and they are checked next I open the dialogue.

Are you sure you tested the correct version (5.23) this time?  what does Program menu .. help .. about … display?

Please copy the ALTER TABLE statement from the HSITORY tab and the 'CREATE STATEMENT' from the OBJECTS tab.

[slimso]

Well, i've done better than that. I've installed version 5.24 and made some screenshots too.

Here's the ALTER TABLE statement after opening the “Alter table” dialog and changing the field name from “priceOld” to “priceOlds”:

Code:

alter table `deliverit`.`prods` change `priceOld` `priceOlds` float (10,2) DEFAULT '0.00' NOT NULL

Here's the original CREATE TABLE statement (as shown in screen1.gif and screen2.gif):

Table Create Table

---

---

prods CREATE TABLE `prods` (

`ID` smallint(5) unsigned NOT NULL auto_increment,

`catID` smallint(5) unsigned NOT NULL default '0',

`brandID` smallint(5) unsigned NOT NULL default '0',

`typeID` smallint(5) unsigned NOT NULL default '0',

`photoID` smallint(5) unsigned NOT NULL default '0',

`name` varchar(500) default NULL,

`price` float(10,2) unsigned NOT NULL default '0.00',

`priceOld` float(10,2) unsigned NOT NULL default '0.00',

`stock` tinyint(3) unsigned NOT NULL default '0',

`link` varchar(200) default NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=1369 DEFAULT CHARSET=latin1

This is not a SQL related problem, it's just and UI bug: the “Alter table” dialog does not read the “float(10,2) unsigned” corectly.

When opening the “Alter table” dialog, the “Unsigned” flag is not checked even though the data type of the field is FLOAT UNSIGNED.

If I choose to “Alter” the table, the program behaves properly and alters the datatype to float signed because the “Unsigned” flag was indeed not checked (not by me though).

[peterlaursen]

Reproduced with your example!  

There seems to be a problem when both the length fields and the 'UNSIGNED' descriptor are used at the same time.

We will wok on this tomorrow.

[slimso]

OK, it works!

Didn't fully tested it, but i'm confident the problem is solved.

Topic closed i guess.

Thank you.

[Author]

Posts