Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13045

Strange behavior when adding partitions to Json type table (Connect engine)

    Details

      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"}
      

        Attachments

          Activity

            People

            • Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              alice Alice Sherepa
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: