MySQL Bugs: #16222: Select on truncated keys

Bug #16222	Select on truncated keys
Submitted:	5 Jan 2006 13:21	Modified:	9 Jan 2006 0:31
Reporter:	Chris Hamono	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.22	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:

When doing a select on a unique key where the supplied key is longer than the field size, no match is found. and no row returned even if the truncated version exists in the table

If you then subsequently try to insert that key, the value inserted is truncated to the size of the field and a duplicate key error is generated.

The workaround is to truncate the value before using it in the select statement. 

but wouldnt it be better if mysql truncated the key to the field size before it attempted the key lookup?

to truncate it prior to the select statement requires prior knowledge of the length of the field. if the length changes then your function no longer works as required

To test the length of the field prior to the select adds uneeded overhead to the function

This has been around a long time, but I cannot find a bug or information relating to it. so I am submitting this. my apologies if I am repeating old stuff

How to repeat:

insert a value into a table that is guaranteed to be truncated
now do a select on that table with the original value in the where clause

eg:
INSERT INTO mytable (mykey) VALUES ('really long value guaranteed to be truncated');

SELECT * FROM mytable WHERE mykey='really long value guaranteed to be truncated';

returns 0 rows

Now ....
INSERT INTO mytable (mykey) VALUES ('really long value guaranteed to be truncated');

returns duplicate key error

Suggested fix:

mysql should truncated the value to the field size before it attempts the lookup in the SELECT statement

Thank you for a problem report. Sorry, but it is not a bug - it is intended behaviour:

mysql> create table t1 (c1 char(3));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values('abcd');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
| abc  |
+------+
1 row in set (0.01 sec)

mysql> select * from t1 where c1 = 'abc';
+------+
| c1   |
+------+
| abc  |
+------+
1 row in set (0.02 sec)

mysql> select * from t1 where c1 = 'abcd';
Empty set (0.00 sec)

mysql> select * from t1 where c1 <> 'abcd';
+------+
| c1   |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

You were warned that the value was truncated, and surely you need to know the column width (N) if you want to compare first N characters only. This is the right behaviour (to pad shorter values with blanks) for string comparison.

if the original value was inserted in a previous unassociated session you have no idea about the warning.

if then the value to be tested is longer than expected for whatever reason it means the script/program must be aware of the length of the field before knowing where to truncate the longer than expected value before testing it.

this becomes additional overhead required in every script, rather than a single extra test performed in the SQL engine.

although I can see conflict in either implementation. mySQL is not fast if the scripts have to make extra calls in to it to test the length of every field

if a value is truncated b4 being tested a warning could be issued then. mission critical applications would then need to heed that warning if it is issued and ignore it if there is none. 

This is much quicker than retrieving the field length before the test

The application that was developed without knowing exact database model (including column types and size) will never be high-performant anyway...

Surely I can mark this bug report as a feature request, but to make it reasonable, can you, please, point out any other popular RDBMS that works as you want?

Well to be honest I don't deal with other databases so I wouldnt know. I take the attitude that anything scripted is slow. so I do as little in the scripts as possible which includes making calls to mysql only when necessary.

Obviously the rest of the mysql community has little problem with this issue. and it only effects me when I am unable to determine in advance what the maximum amount of recieved data is going to be. so I design the table as a best guess.

Still I would have thought it obvious that a test could not exceed the field length due to the fact that a subsequent insert would fail. but thats only one persons perspective.

At the very least this is now in the bug database so that any one else looking like me can find a previous conversation about it.

If you feel it is worth adding as a feature request please do. you are in a far better position to determine this than me.