The split query_script statement will not allow the use of PARTITION selection syntax feature of MySQL 5.6

[GoogleCodeExporter]

## Test Case:

### Create and popluate test table

mysql> USE dbatest;
mysql> CREATE TABLE account_payments (
         id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
         account_id        INT UNSIGNED NOT NULL DEFAULT 0,
         payment_date      DATE         NOT NULL DEFAULT '0000-00-00',
         payment_amt_cents INT SIGNED   NOT NULL DEFAULT 0,
         PRIMARY KEY ( id, account_id )
         )
         PARTITION BY RANGE ( account_id )
         ( PARTITION p0 VALUES LESS THAN ( 0 ),
           PARTITION p1 VALUES LESS THAN ( 100 ),
           PARTITION p2 VALUES LESS THAN ( 500 ),
           PARTITION p3 VALUES LESS THAN MAXVALUE
         )
         ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO account_payments ( account_id, payment_date, 
payment_amt_cents ) VALUES
         ( 10,  '2000-01-01', 12300 ), ( 10,  '2000-02-01', 12500 ), ( 10, '2000-03-01', 8700 ),
         ( 105, '2000-01-01', 54321 ), ( 205, '2002-04-01', 8572 ),
         ( 700, '2005-12-31', 150 )
         ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

### Now try to use the split statement is a few different ways:

#### Straight SQL:
mysql> SELECT * FROM dbatest.account_payments PARTITION ( p2 );
+----+------------+--------------+-------------------+
| id | account_id | payment_date | payment_amt_cents |
+----+------------+--------------+-------------------+
|  4 |        105 | 2000-01-01   |             54321 |
|  5 |        205 | 2002-04-01   |              8572 |
+----+------------+--------------+-------------------+
2 rows in set (0.00 sec)

#### Split Fails:
mysql> CALL common_schema.run( "split ( SELECT * FROM dbatest.account_payments 
PARTITION ( p2 )) pass;" );
ERROR 1644 (91100): QueryScript error: [split() cannot deduce split table name. 
Please specify explicitly] at 15: "SELECT * FROM dbatest.account_pay"

#### One workaround:
mysql> CALL common_schema.run( "split ( SELECT * FROM dbatest.account_payments 
WHERE account_id < 500 AND account_id >= 100) pass;" );
+----+------------+--------------+-------------------+
| id | account_id | payment_date | payment_amt_cents |
+----+------------+--------------+-------------------+
|  4 |        105 | 2000-01-01   |             54321 |
|  5 |        205 | 2002-04-01   |              8572 |
+----+------------+--------------+-------------------+
2 rows in set (0.08 sec)

Query OK, 2 rows affected (0.10 sec)

#### Split on whole table:
mysql> CALL common_schema.run("
       split (dbatest.account_payments) {
         SELECT $split_step as step, $split_columns as columns, $split_min as min_value,
                $split_max as max_value, $split_range_start as range_start, $split_range_end as range_end
         }");
+------+-------------------+-----------+-----------+-------------+-----------+
| step | columns           | min_value | max_value | range_start | range_end |
+------+-------------------+-----------+-----------+-------------+-----------+
|    1 | `id`,`account_id` | '1','10'  | '6','700' | '1','10'    | '6','700' |
+------+-------------------+-----------+-----------+-------------+-----------+
1 row in set (0.07 sec)

Query OK, 2 rows affected (0.09 sec)

#### Split on specific partition Fails:
mysql> CALL common_schema.run("
       split (dbatest.account_payments PARTITION ( p2 )) {
         SELECT $split_step as step, $split_columns as columns, $split_min as min_value,
                $split_max as max_value, $split_range_start as range_start, $split_range_end as range_end
         }");
ERROR 1644 (91100): QueryScript error: [split(): unsupported query type] at 16: 
"dbatest.account_payments PARTITION ( p2 )) {
  SELECT $split_step"

#### One workaround:
mysql> CALL common_schema.run("
       split ({start:'0,100', stop:'1000000000,500', table:dbatest.account_payments}) {
         SELECT $split_step as step, $split_columns as columns, $split_min as min_value,
                $split_max as max_value, $split_range_start as range_start, $split_range_end as range_end
         }");
+------+-------------------+-----------+--------------------+-------------+-----
------+
| step | columns           | min_value | max_value          | range_start | 
range_end |
+------+-------------------+-----------+--------------------+-------------+-----
------+
|    1 | `id`,`account_id` | '0','100' | '1000000000','500' | '0','100'   | 
'6','700' |
+------+-------------------+-----------+--------------------+-------------+-----
------+
1 row in set (0.07 sec)

Query OK, 2 rows affected (0.09 sec)

#########

These workarounds frustrate the use of the split statement for partitioned 
tables.  These are ironically just the kind of tables, those large enough to 
need partitioning (billions of rows), that the split statement should greatly 
help manage.
From looking at the code in the _split_* sprocs, it seems that the inclusion of 
elements from the information_schema.PARTITIONS table may be needed.  That or 
perhaps just adjustment of the SQL parsing in key areas could circumvent 
broader changes.
I believe this would be a broadly useful enhancement.  Further, may I suggest 
that the documentation for the split command call out it’s lack of support 
for the PARTITION selection syntax now available in MySQL 5.6.

mysql> SELECT * FROM common_schema.status\G
*************************** 1. row ***************************
                       project_name: common_schema
                            version: 2.2
                           revision: 548
                       install_time: 2013-11-25 14:47:14
                    install_success: 1
          base_components_installed: 1
 innodb_plugin_components_installed: 1
percona_server_components_installed: 0
        tokudb_components_installed: 0
              install_mysql_version: 5.6.14-log
                   install_sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

Original issue reported on code.google.com by s...@davidson-pappas.com on 28 Mar 2014 at 6:46

[GoogleCodeExporter]

Thank you. This is, I believe, and before testing, merely a parsing issue, and 
will not require use of INFORMATION_SCHEMA.PARTITIONS.
Will fix this shortly.

Original comment by shlomi.n...@gmail.com on 31 Mar 2014 at 3:13

• Changed state: Accepted

[GoogleCodeExporter]

Original comment by shlomi.n...@gmail.com on 31 Mar 2014 at 3:35

• Changed state: Started

[GoogleCodeExporter]

split() now support PARTITION keyword. Current limitation is that there can be 
only be one table in the query when PARTITION is used.
Not yet released.

Original comment by shlomi.n...@gmail.com on 27 May 2014 at 12:14

• Changed state: Fixed