Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.6
-
None
-
Ubuntu 16.04
Description
Can not create a table, when adding partition on JSON type table (Connect engine), got en error "Unknown option 'FIELD_FORMAT' ".
--------------
|
create table t1 ( |
`address_street` varchar(150) NOT NULL `FIELD_FORMAT`='address:street', |
`address_zipcode` varchar(150) NOT NULL `FIELD_FORMAT`='address:zipcode', |
`cuisine` varchar(150) NOT NULL, |
`name` varchar(150) NOT NULL, |
`restaurant_id` bigint NOT NULL) |
engine=CONNECT HUGE=1 table_type=JSON file_name='/t/qq.json' |
lrecl=128 option_list='pretty=0' ending=1 |
PARTITION BY RANGE (restaurant_id) ( |
PARTITION p0 VALUES LESS THAN (3), |
PARTITION p1 VALUES LESS THAN MAXVALUE) |
--------------
|
|
|
ERROR 1911 (HY000) at line 4 in file: '/t/a.sql': Unknown option 'FIELD_FORMAT' |
-------------- |
While the same, but with alter table works, but data is presented 2 times (if I add 3 partitions, then 3 times) :
--------------
|
create table t1 ( |
`address_street` varchar(150) NOT NULL `FIELD_FORMAT`='address:street', |
`address_zipcode` varchar(150) NOT NULL `FIELD_FORMAT`='address:zipcode', |
`cuisine` varchar(150) NOT NULL, |
`name` varchar(150) NOT NULL, |
`restaurant_id` bigint NOT NULL) |
engine=CONNECT HUGE=1 table_type=JSON file_name='/t/qq.json' |
lrecl=128 option_list='pretty=0' ending=1 |
--------------
|
Query OK, 0 rows affected (0.06 sec) |
|
|
--------------
|
select * from t1 |
--------------
|
+-----------------+-----------------+---------+-------------+---------------+ |
| address_street | address_zipcode | cuisine | name | restaurant_id | |
+-----------------+-----------------+---------+-------------+---------------+ |
| m | 10462 | | Bake Shop 1 | 1 |
|
| e | 10463 | | Bake Shop 2 | 2 |
|
| t | 10464 | | Bake Shop 3 | 3 |
|
| w | 10465 | | Bake Shop 4 | 4 |
|
| m | 10466 | | Bake Shop 5 | 5 |
|
| t | 10467 | | Bake Shop 6 | 6 |
|
+-----------------+-----------------+---------+-------------+---------------+ |
6 rows in set (0.00 sec) |
--------------
|
ALTER TABLE t1 |
PARTITION BY RANGE (restaurant_id) ( |
PARTITION p0 VALUES LESS THAN (3), |
PARTITION p1 VALUES LESS THAN MAXVALUE) |
--------------
|
|
|
Query OK, 0 rows affected, 2 warnings (0.02 sec) |
Records: 0 Duplicates: 0 Warnings: 2
|
Warning (Code 1105): Data repartition in p0 is unchecked |
Warning (Code 1105): Data repartition in p1 is unchecked |
--------------
|
select * from t1 |
--------------
|
+-----------------+-----------------+---------+-------------+---------------+ |
| address_street | address_zipcode | cuisine | name | restaurant_id | |
+-----------------+-----------------+---------+-------------+---------------+ |
| m | 10462 | | Bake Shop 1 | 1 |
|
| e | 10463 | | Bake Shop 2 | 2 |
|
| t | 10464 | | Bake Shop 3 | 3 |
|
| w | 10465 | | Bake Shop 4 | 4 |
|
| m | 10466 | | Bake Shop 5 | 5 |
|
| t | 10467 | | Bake Shop 6 | 6 |
|
| m | 10462 | | Bake Shop 1 | 1 |
|
| e | 10463 | | Bake Shop 2 | 2 |
|
| t | 10464 | | Bake Shop 3 | 3 |
|
| w | 10465 | | Bake Shop 4 | 4 |
|
| m | 10466 | | Bake Shop 5 | 5 |
|
| t | 10467 | | Bake Shop 6 | 6 |
|
+-----------------+-----------------+---------+-------------+---------------+ |
12 rows in set (0.00 sec) |
json file
{"address": {"street": "m", "zipcode": "10462"}, "name": "Bake Shop 1", "restaurant_id": "1"}
|
{"address": {"street": "e", "zipcode": "10463"}, "name": "Bake Shop 2", "restaurant_id": "2"}
|
{"address": {"street": "t", "zipcode": "10464"}, "name": "Bake Shop 3", "restaurant_id": "3"}
|
{"address": {"street": "w", "zipcode": "10465"}, "name": "Bake Shop 4", "restaurant_id": "4"}
|
{"address": {"street": "m", "zipcode": "10466"}, "name": "Bake Shop 5", "restaurant_id": "5"}
|
{"address": {"street": "t", "zipcode": "10467"}, "name": "Bake Shop 6", "restaurant_id": "6"}
|