Bug #53629 Inserting records using NDB API adds empty padding to make value take max space
Submitted: 13 May 2010 20:32 Modified: 16 May 2010 16:51
Reporter: George Tsafas Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Cluster: NDB API Severity:S1 (Critical)
Version:NDB 7.1.3 OS:Any
Assigned to: Andrew Hutchings CPU Architecture:Any
Tags: cluster, ndb, ndbapi, padding, record

[13 May 2010 20:32] George Tsafas
I am inserting records using the NDB API. I have the following table structure.

CREATE TABLE  `TickStore`.`trades` (
  `id` int(10) unsigned NOT NULL,
  `symbol` char(8) NOT NULL,
  `time` char(12) NOT NULL,
  `price` float unsigned NOT NULL,
  `size` mediumint(8) unsigned NOT NULL,
  `exchange` char(1) NOT NULL,
  `code` char(1) NOT NULL,
  `bid` float unsigned NOT NULL,
  `ask` float unsigned NOT NULL,
  `vwap` double unsigned NOT NULL,
  `vwap_volume` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_2` (`symbol`,`time`),
  KEY `Index_3` (`time`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 MAX_ROWS=1000000000;

I am inserting values into symbol that are not always 8 chars long.

It seems the API is padding the values such as if I insert a value of "MSFT" to symbol column it makes MSFT 8 CHARS long. 

This disables the use of,
SELECT * FROM TickStore.trades WHERE symbol = 'MSFT'

It returns 0 results

However with,
SELECT * FROM TickStore.trades WHERE symbol like '%MSFT%'

I am able to see the results. 

How to repeat:
Create a table using char(8) then insert records less than 8 chars long using the NDB API. Try a lookup on the value.
[16 May 2010 16:51] Andrew Hutchings
MySQL uses the space character for padding char characters, so if your application's data needs to be read by MySQL you need to pad using space characters.  Please see the third paragraph of: