MySQL Error On Create Table - Truncated Incorrect DOUBLE VALUE?

Question

I have the following stored procedure that has been giving me problems when calling it. The select statement, when executed alone works just fine, so does creating the stored procedure. But when I "call ();" I get the following error:

Lookup Error - MySQL Database Error: Truncated incorrect DOUBLE value: '0g'

This is the stored procedure. Does anyone see what could potentially be causing this error?

DROP PROCEDURE IF EXISTS test.clients_shipments_with_line_weight_refresh_sp;

CREATE PROCEDURE test.`clients_shipments_with_line_weight_refresh_sp`()
BEGIN
   DROP TABLE IF EXISTS clients_shipments_with_line_weight;
   CREATE TABLE clients_shipments_with_line_weight
   AS
      (SELECT reg_client_shipments.*,
              wow3.meta_value AS cannabis_type,
              (wow2.meta_value * wp_wowcomm_order_itemmeta.meta_value)
                 AS total_line_item_weight,
              pm3.meta_value   AS province
       FROM clients_shipments
            INNER JOIN wp_postmeta pm2
               ON clients_shipments.ID = pm2.post_id
            INNER JOIN wp_postmeta pm3
               ON     clients_shipments.ID = pm3.post_id
                  AND pm3.meta_key = 'statedeliver'
            INNER JOIN wp_wowcomm_order_items
               ON     wp_wowcomm_order_items.order_id =
                         clients_shipments.ID
                  AND wp_wowcomm_order_items.order_item_type =
                         'product'
            INNER JOIN wp_wowcomm_order_itemmeta
               ON     wp_wowcomm_order_items.order_item_id =
                         wp_wowcomm_order_itemmeta.order_item_id
                  AND wp_wowcomm_order_itemmeta.meta_key = 'weight'
            INNER JOIN wp_wowcomm_order_itemmeta wow2
               ON     wp_wowcomm_order_items.order_item_id =
                         wow2.order_item_id
                  AND wow2.meta_key = 'amount'
            INNER JOIN wp_wowcomm_order_itemmeta wow3
               ON     wp_wowcomm_order_items.order_item_id =
                         wow3.order_item_id
                  AND wow3.meta_key = 'producttype'
       GROUP BY wp_wowcomm_order_itemmeta.meta_id);
END;

Any chance to see definition of the tables you're using? – Alfabravo Feb 01 '17 at 15:38 — Alfabravo, Feb 01 '17 at 15:38

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

This answer suggests that you could be comparing a number or a string on a where or on clause.

https://stackoverflow.com/a/16069446/409556

You select Statment looks pretty much like it may be doing just that. The OP suggests removing strict and so it turns into a warning.

edited May 23 '17 at 10:29

Community

1
1

answered Feb 01 '17 at 15:44

hayres

120
1
11

Would that makes sense as to why the select works but not calling the create table? creating a temp table works too – choloboy Feb 01 '17 at 15:52
It might well do. Your best bet is to look at what each column type is set for each of the columns you are doing the And and ON on. Maybe try creating the table and then populating it with data using Select Into. It may be that the act of creating a table with a select is just two much if data transformation has to happen. – hayres Feb 01 '17 at 15:58

MySQL Error On Create Table - Truncated Incorrect DOUBLE VALUE?

1 Answers1