| Bug #29843 | Problem with averaging ENUM Fields | ||
|---|---|---|---|
| Submitted: | 17 Jul 2007 11:28 | Modified: | 1 Aug 2007 18:02 |
| Reporter: | Danny Keller | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.1 bk, 5.0 bk, 5.1 bk | OS: | Any (Linux, Windows XAMPP Installation with PHP5, Apache 2) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | average, enum | ||
[17 Jul 2007 11:55]
Sveta Smirnova
Thank you for the report. Verified as described. Seems AVG uses index of element in ENUM and not its value: $mysql50 test -T Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3050 Server version: 5.0.48-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT MIN(tablefieldtest) AS minimumtablefieldtest, AVG(tablefieldtest) AS avgtablefieldtest, MAX(tablefieldtest) AS maximumtablefieldtest FROM t33; Field 1: `minimumtablefieldtest` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: latin1_swedish_ci (8) Length: 1 Max_length: 1 Decimals: 0 Flags: ENUM Field 2: `avgtablefieldtest` Catalog: `def` Database: `` Table: `` Org_table: `` Type: DOUBLE Collation: binary (63) Length: 5 Max_length: 3 Decimals: 31 Flags: BINARY NUM Field 3: `maximumtablefieldtest` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: latin1_swedish_ci (8) Length: 1 Max_length: 1 Decimals: 0 Flags: ENUM +-----------------------+-------------------+-----------------------+ | minimumtablefieldtest | avgtablefieldtest | maximumtablefieldtest | +-----------------------+-------------------+-----------------------+ | 0 | 1 | 0 | +-----------------------+-------------------+-----------------------+ 1 row in set (0.00 sec)
[1 Aug 2007 18:02]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Updated the ENUM, SET, and group-by functions sections. Functions that require numeric arguments cast the argument to a number if necessary. For ENUM/SET, this cast operation results in use of the underlying numeric value.
Description: I dont know if it is really a bug, but I just discovered an inconsistency when I tried to average a field with enum clause. Example: Table has an entry like this one: `tablefieldtest` enum('0','1','2','3','4','5') collate latin1_german1_ci NOT NULL default '1' If I have only one entry "0" for this table, in my opinion an average should be "0", too. This SELECT tells me something different: SELECT MIN(tablefieldtest) AS minimumtablefieldtest, AVG(tablefieldtest) AS avgtablefieldtest, MAX(tablefieldtest) AS maximumtablefieldtest FROM tableirgendwas LIMIT 10 The result is like this: minimumtablefieldtest: 0 avgtablefieldtest: 1 maximumtablefieldtest: 0 In tests I discovered that the average value is computet out of the position in the enum field, therefore the "0" has position 1 and so is the average also 1. If you put the "0" at the end like ('1','2','3','4','5','0') the average would be 6. How to repeat: see the details above make a table, set a field to enum with the entrys `tablefieldtest` enum('0','1','2','3','4','5') collate latin1_german1_ci NOT NULL default '1' Then try the SELECT Query Suggested fix: Maybe it is not a real bug, but just intended? Otherwise I would check if it is possible to treat it like an Integer.