Hacking MySQL: making TRUNCATE behaviour more intuitive

Posted by Jorge Bernal June 12, 2007

This is my second article about hacking MySQL. If you are interested in this topic, you may want to read my previous Hacking MySQL: SIGNAL support (I)

The problem

If I tell you there is a function called TRUNCATE, what do you think it does? which are its arguments?

For me, the obvious behaviour would be something like:

mysql> SELECT TRUNCATE(123.45);
+--------------------+
| TRUNCATE(123.45)   |
+--------------------+
|                123 |
+--------------------+
1 row in set (0.08 sec)

But the actual behaviour is this:

mysql> SELECT TRUNCATE(123.45); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

This is because TRUNCATE requires a second argument. The syntax, according to the manual, is:

TRUNCATE(X,D)

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.

Wouldn’t be cool if the default value for D were 0 if we don’t specify otherwise?

The solution

This time we only have to touch the parser (sql/sql_yacc.yy). The code for this is quite simple, although you might find some problems on type conversion.

--- mysql-5.1/sql/sql_yacc.yy	2007-05-08 12:25:51.000000000 +0200
+++ mysql-5.1-truncate/sql/sql_yacc.yy	2007-05-08 15:55:42.000000000 +0200
@@ -6741,6 +6741,13 @@
 	  { $$= new (YYTHD->mem_root) Item_func_replace($3,$5,$7); }
 	| TRUNCATE_SYM '(' expr ',' expr ')'
 	  { $$= new (YYTHD->mem_root) Item_func_round($3,$5,1); }
+	| TRUNCATE_SYM '(' expr ')'
+	  {
+            THD *thd= YYTHD;
+            Item *i1= new (thd->mem_root) Item_int((char*) "0",(int32) 0,1);
+
+      	    $$= new (thd->mem_root) Item_func_round($3,i1,1);
+	  }
 	| WEEK_SYM '(' expr ')'
 	  {
             THD *thd= YYTHD;

Get the patch: [28304-truncate_default_value.diff]

Like I said, the hardest part was to go through sql/item*.h trying to guess the expected type for Item_func_round and how to cast that type correctly. I should learn how to better debug the MySQL server.