[GitHub] [shardingsphere] sandynz commented on issue #17194: Preparing failure in scaling because Incorrect statement for create table

[GitBox <gi...@apache.org>]

sandynz commented on issue #17194:
URL: https://github.com/apache/shardingsphere/issues/17194#issuecomment-1113117154

   Hi @RaigorJiang , I tested on local MogDB, it works on `numeric` data type. Could you verify the printed creating table SQL again? (Log keyword: AbstractDataSourcePreparer - execute target table sql)
   
   Test table:
   ```
   CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, order_price NUMERIC(18,2), PRIMARY KEY (order_id));
   ```
   
   Got SQLs by `pg_get_tabledef`:
   ```
   SET search_path = public;
   CREATE TABLE t_order_0 (
   	order_id integer NOT NULL,
   	user_id integer NOT NULL,
   	status character varying(45),
   	order_price numeric(18,2)
   )
   WITH (orientation=row, compression=no);
   ALTER TABLE t_order_0 ADD CONSTRAINT t_order_0_pkey PRIMARY KEY (order_id);
   ```
   Handled SQLs:
   ```
   CREATE TABLE IF NOT EXISTS t_order (
   	order_id integer NOT NULL,
   	user_id integer NOT NULL,
   	status character varying(45),
   	order_price numeric(18,2)
   );
   ALTER TABLE t_order ADD CONSTRAINT t_order_pkey PRIMARY KEY (order_id);
   ```
   And scaling job FINISHED:
   ```
   MogDB=>show scaling status 0130317c30317c3054317c7363616c696e675f6462;
    item | data_source |  status  | active | inventory_finished_percentage | incremental_idle_seconds
   ------+-------------+----------+--------+-------------------------------+--------------------------
    0    | ds_0        | FINISHED | false  | 100                           | 53
    1    | ds_1        | FINISHED | false  | 100                           | 59
   (2 rows)
   
   MogDB=>preview select count(1) from t_order;
    data_source_name |                               actual_sql
   ------------------+-------------------------------------------------------------------------
    ds_2             | select count(1) from t_order_0 UNION ALL select count(1) from t_order_3
    ds_3             | select count(1) from t_order_1 UNION ALL select count(1) from t_order_4
    ds_4             | select count(1) from t_order_2 UNION ALL select count(1) from t_order_5
   (3 rows)
   
   MogDB=>select count(1) from t_order;
    count
   -------
        6
   (1 row)
   ```
   Related sharding db on underlying MogDB:
   ```
   MogDB=#\c scaling_ds_12
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   You are now connected to database "scaling_ds_12" as user "omm".
   MogDB=#\d
                                List of relations
    Schema |      Name      | Type  | Owner |             Storage
   --------+----------------+-------+-------+----------------------------------
    public | t_order_2      | table | mogdb | {orientation=row,compression=no}
    public | t_order_5      | table | mogdb | {orientation=row,compression=no}
    public | t_order_item_2 | table | mogdb | {orientation=row,compression=no}
    public | t_order_item_5 | table | mogdb | {orientation=row,compression=no}
   (4 rows)
   
   MogDB=#\d t_order_5
               Table "public.t_order_5"
      Column    |         Type          | Modifiers
   -------------+-----------------------+-----------
    order_id    | integer               | not null
    user_id     | integer               | not null
    status      | character varying(45) |
    order_price | numeric(18,2)         |
   Indexes:
       "t_order_pkey_t_order_5" PRIMARY KEY, btree (order_id) TABLESPACE pg_default
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org