| Bug #15280 | String function 'LENGTH' not working correctly | ||
|---|---|---|---|
| Submitted: | 28 Nov 2005 2:22 | Modified: | 30 Nov 2005 17:43 |
| Reporter: | Richard de Courtney | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.16 | OS: | Windows (Windows 2003) |
| Assigned to: | CPU Architecture: | Any | |
[28 Nov 2005 5:11]
Jorge del Conde
Thanks for your bug report. I was able to reproduce this under both, WinXP and FC4
[29 Nov 2005 11:34]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: This is not a bug. 'xxx' is treated as a string literal in MySQL unless your current sql_mode includes ANSI_QUOTES.
[29 Nov 2005 17:18]
Sergei Golubchik
Oops, sorry. " (double quote) changes its meaning depending on ANSI_QUOTES, not ' (single quote). The latter always denote string literal. http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html http://dev.mysql.com/doc/refman/4.1/en/string-syntax.html
[29 Nov 2005 17:31]
Richard de Courtney
Sorry Sergei, I don't understand, are you saying the final SELECT statement is incorrect, if so, why does it return various numbers as the length of the column? Thanks!
[30 Nov 2005 12:32]
Sergei Golubchik
length('colA')
returns the length of the string 'colA', naturally it's 4.
[30 Nov 2005 17:43]
Richard de Courtney
Ok, now I'm embarrassed ;-), and it caught Jorge del Conde out as well. My apologies, is mis-read the documentation! Have a great day.
Description: When conducting an operation with the LENGTH string function, the function is returning incorrect results. How to repeat: 1. CREATE TABLE lengthtest (colA varchar(255)) 2. INSERT INTO lengthtest (colA) VALUES ('This is a test') 3. INSERT INTO lengthtest (colA) VALUES ('test') 4. SELECT length('colA'),colA FROM lengthtest WHERE length('colA') < 5 The SELECT statement then returns both rows, and both lengths of the string is returned as 4. This works with whatever columntype the column is set as. Suggested fix: Return the correct length! ;-)