Woocommerce MySQL Views General notes

Precision of $ values stored as LONGTEXT

Since most $$ are stored as meta_keys in:

• ..post_meta
• ..order_itemmeta

and because meta_keys is of data type LONGTEXT

When using the values as numbers there is an implicit numeric conversion to floating point, we get values like this from say, SUM(x):

• 7564.5999999999985

Where we expect this:

• 7564.60

Updated 20180808: Totals calculated directly from product prices (assuming product prices are always no more than 2dp) are always 2dp, it is tax cacualtions that are 4dp

Solution:

1. In the lowest level views, where we typically flatten meta_key/Meta_values per a parent record, we will cast them as:
   1. DECIMAL(12,4) for TAX
   2. DECIMAL (10,2) for product price derived values (e.g. order_line_item->line_subtotal)
2. In any other query that accesses what is expected to be a $ value, we will do the same cast every time
3. Note we are casting as 4dp as this seems to be the accuracy used to populate these values.  As we have had rounding discrepancies with with tax, i.e. calced & stored at order line (item) level vs calculated across the order, we are leaving these at 4dp until we make progress on that issue

When doing data clenaup in mySQL, Safe updtae is turned on by default, solution example:

SET SQL_SAFE_UPDATES=0;
 update wp1_woocommerce_order_itemmeta
 set meta_key = 'length'
 where meta_key like '%required%';
SET SQL_SAFE_UPDATES=1;
 

xx