In BIN table date_format now imply by default field_format='C'.

  modified:   storage/connect/tabfix.cpp
  modified:   storage/connect/reldef.cpp

Json array index (position) is now 0 based by default. This corresponds
to what all json applications and functions do. Also fix ROWNUM calculation.
  modified:   storage/connect/jsonudf.cpp
  modified:   storage/connect/mysql-test/connect/r/json.result
  modified:   storage/connect/mysql-test/connect/t/json.test
  modified:   storage/connect/tabjson.cpp
  modified:   storage/connect/tabjson.h

2 files changed, 274 insertions, 274 deletions

diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result
index 80b0a2cbc18..acb74c38e26 100644
--- a/storage/connect/mysql-test/connect/r/json.result
+++ b/storage/connect/mysql-test/connect/r/json.result
@@ -97,7 +97,7 @@ ISBN	Language	Subject	AuthorFN	AuthorLN	Title	Translation	Translator	Publisher	L
 CREATE TABLE t2 (
 FIRSTNAME CHAR(32),
 LASTNAME CHAR(32))
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]:AUTHOR';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[1]:AUTHOR';
 SELECT * FROM t2;
 FIRSTNAME	LASTNAME
 William J.	Pardi
@@ -252,9 +252,9 @@ DROP TABLE t1;
 #
 CREATE TABLE t2 (
 WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
+WEEK INT(2) FIELD_FORMAT='WEEK:[0]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:AMOUNT')
 ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
 SELECT * FROM t2;
 WHO	WEEK	WHAT	AMOUNT
@@ -268,9 +268,9 @@ Janet	3	Food	18.00
 Janet	3	Beer	18.00
 CREATE TABLE t3 (
 WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
+WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
 ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
 SELECT * FROM t3;
 WHO	WEEK	WHAT	AMOUNT
@@ -284,9 +284,9 @@ Beth	4	Beer	15.00
 Janet	4	Car	17.00
 CREATE TABLE t4 (
 WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT')
+WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
 ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
 SELECT * FROM t4;
 WHO	WEEK	WHAT	AMOUNT
diff --git a/storage/connect/mysql-test/connect/t/json.test b/storage/connect/mysql-test/connect/t/json.test
index 79588e9fe5b..1cc2c054db4 100644
--- a/storage/connect/mysql-test/connect/t/json.test
+++ b/storage/connect/mysql-test/connect/t/json.test
@@ -1,264 +1,264 @@
---source include/not_embedded.inc

---source include/have_partition.inc

-

-let $MYSQLD_DATADIR= `select @@datadir`;

-

---copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json

---copy_file $MTR_SUITE_DIR/std_data/expense.json $MYSQLD_DATADIR/test/expense.json

---copy_file $MTR_SUITE_DIR/std_data/mulexp3.json $MYSQLD_DATADIR/test/mulexp3.json

---copy_file $MTR_SUITE_DIR/std_data/mulexp4.json $MYSQLD_DATADIR/test/mulexp4.json

---copy_file $MTR_SUITE_DIR/std_data/mulexp5.json $MYSQLD_DATADIR/test/mulexp5.json

-

---echo #

---echo # Testing doc samples

---echo #

-CREATE TABLE t1

-(

-  ISBN CHAR(15),

-  LANG CHAR(2),

-  SUBJECT CHAR(32),

-  AUTHOR CHAR(64),

-  TITLE CHAR(32),

-  TRANSLATION CHAR(32),

-  TRANSLATOR CHAR(80),

-  PUBLISHER CHAR(32),

-  DATEPUB int(4)

-) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

-

---echo #

---echo # Testing Jpath. Get the number of authors

---echo #

-CREATE TABLE t1

-(

-  ISBN CHAR(15),

-  Language CHAR(2) FIELD_FORMAT='LANG',

-  Subject CHAR(32) FIELD_FORMAT='SUBJECT',

-  Authors INT(2) FIELD_FORMAT='AUTHOR:[#]',

-  Title CHAR(32) FIELD_FORMAT='TITLE',

-  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',

-  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',

-  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',

-  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',

-  Year int(4) FIELD_FORMAT='DATEPUB'

-)

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # Concatenates the authors

---echo #

-CREATE TABLE t1

-(

-  ISBN CHAR(15),

-  Language CHAR(2) FIELD_FORMAT='LANG',

-  Subject CHAR(32) FIELD_FORMAT='SUBJECT',

-  AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:FIRSTNAME',

-  AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:LASTNAME',

-  Title CHAR(32) FIELD_FORMAT='TITLE',

-  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',

-  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',

-  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',

-  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',

-  Year int(4) FIELD_FORMAT='DATEPUB'

-)

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # Testing expanding authors

---echo #

-CREATE TABLE t1

-(

-  ISBN CHAR(15),

-  Language CHAR(2) FIELD_FORMAT='LANG',

-  Subject CHAR(32) FIELD_FORMAT='SUBJECT',

-  AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:FIRSTNAME',

-  AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:LASTNAME',

-  Title CHAR(32) FIELD_FORMAT='TITLE',

-  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',

-  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',

-  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',

-  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',

-  Year int(4) FIELD_FORMAT='DATEPUB'

-)

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';

-SELECT * FROM t1;

-UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab';

-SELECT * FROM t1 WHERE ISBN = '9782212090819';

-

---echo #

---echo # To add an author a new table must be created

---echo #

-CREATE TABLE t2 (

-FIRSTNAME CHAR(32),

-LASTNAME CHAR(32))

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]:AUTHOR';

-SELECT * FROM t2;

-INSERT INTO t2 VALUES('Charles','Dickens');

-SELECT * FROM t1;

-DROP TABLE t1;

-DROP TABLE t2;

-

---echo #

---echo # Check the biblio file has the good format

---echo #

-CREATE TABLE t1

-(

-  line char(255)

-)

-ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # A file with 2 arrays

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12),

-WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',

-WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # Now it can be fully expanded

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12),

-WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',

-WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-#--error ER_GET_ERRMSG

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # A table showing many calculated results

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12) NOT NULL,

-WEEKS CHAR(12) NOT NULL FIELD_FORMAT='WEEK:[", "]:NUMBER',

-SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT',

-SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT',

-AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT',

-SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT',

-AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT',

-AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT',

-AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-SELECT * FROM t1;

-DROP TABLE t1;

-

---echo #

---echo # Expand expense in 3 one week tables

---echo #

-CREATE TABLE t2 (

-WHO CHAR(12),

-WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',

-WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-SELECT * FROM t2;

-

-CREATE TABLE t3 (

-WHO CHAR(12),

-WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',

-WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-SELECT * FROM t3;

-

-CREATE TABLE t4 (

-WHO CHAR(12),

-WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER',

-WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';

-SELECT * FROM t4;

-

---echo #

---echo # The expanded table is made as a TBL table

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32),

-AMOUNT DOUBLE(8,2))

-ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t2,t3,t4';

-SELECT * FROM t1;

-DROP TABLE t1, t2, t3, t4;

-

---echo #

---echo # Three partial JSON tables

---echo #

-CREATE TABLE t2 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json';

-SELECT * FROM t2;

-

-CREATE TABLE t3 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json';

-SELECT * FROM t3;

-

-CREATE TABLE t4 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json';

-SELECT * FROM t4;

-

---echo #

---echo # The complete table can be a multiple JSON table

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1;

-SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT;

-DROP TABLE t1;

-

---echo #

---echo # Or also a partition JSON table

---echo #

-CREATE TABLE t1 (

-WHO CHAR(12),

-WEEK INT(2),

-WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',

-AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')

-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json';

-ALTER TABLE t1

-PARTITION BY LIST COLUMNS(WEEK) (

-PARTITION `3` VALUES IN(3),

-PARTITION `4` VALUES IN(4),

-PARTITION `5` VALUES IN(5));

-SHOW WARNINGS;

-SELECT * FROM t1;

-SELECT * FROM t1 WHERE WEEK = 4;

-DROP TABLE t1, t2, t3, t4;

-

-#

-# Clean up

-#

---remove_file $MYSQLD_DATADIR/test/biblio.json

---remove_file $MYSQLD_DATADIR/test/expense.json

---remove_file $MYSQLD_DATADIR/test/mulexp3.json

---remove_file $MYSQLD_DATADIR/test/mulexp4.json

---remove_file $MYSQLD_DATADIR/test/mulexp5.json

+--source include/not_embedded.inc
+--source include/have_partition.inc
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json
+--copy_file $MTR_SUITE_DIR/std_data/expense.json $MYSQLD_DATADIR/test/expense.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp3.json $MYSQLD_DATADIR/test/mulexp3.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp4.json $MYSQLD_DATADIR/test/mulexp4.json
+--copy_file $MTR_SUITE_DIR/std_data/mulexp5.json $MYSQLD_DATADIR/test/mulexp5.json
+
+--echo #
+--echo # Testing doc samples
+--echo #
+CREATE TABLE t1
+(
+  ISBN CHAR(15),
+  LANG CHAR(2),
+  SUBJECT CHAR(32),
+  AUTHOR CHAR(64),
+  TITLE CHAR(32),
+  TRANSLATION CHAR(32),
+  TRANSLATOR CHAR(80),
+  PUBLISHER CHAR(32),
+  DATEPUB int(4)
+) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Testing Jpath. Get the number of authors
+--echo #
+CREATE TABLE t1
+(
+  ISBN CHAR(15),
+  Language CHAR(2) FIELD_FORMAT='LANG',
+  Subject CHAR(32) FIELD_FORMAT='SUBJECT',
+  Authors INT(2) FIELD_FORMAT='AUTHOR:[#]',
+  Title CHAR(32) FIELD_FORMAT='TITLE',
+  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
+  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
+  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
+  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
+  Year int(4) FIELD_FORMAT='DATEPUB'
+)
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Concatenates the authors
+--echo #
+CREATE TABLE t1
+(
+  ISBN CHAR(15),
+  Language CHAR(2) FIELD_FORMAT='LANG',
+  Subject CHAR(32) FIELD_FORMAT='SUBJECT',
+  AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:FIRSTNAME',
+  AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[" and "]:LASTNAME',
+  Title CHAR(32) FIELD_FORMAT='TITLE',
+  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
+  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
+  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
+  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
+  Year int(4) FIELD_FORMAT='DATEPUB'
+)
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Testing expanding authors
+--echo #
+CREATE TABLE t1
+(
+  ISBN CHAR(15),
+  Language CHAR(2) FIELD_FORMAT='LANG',
+  Subject CHAR(32) FIELD_FORMAT='SUBJECT',
+  AuthorFN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:FIRSTNAME',
+  AuthorLN CHAR(128) FIELD_FORMAT='AUTHOR:[X]:LASTNAME',
+  Title CHAR(32) FIELD_FORMAT='TITLE',
+  Translation CHAR(32) FIELD_FORMAT='TRANSLATION',
+  Translator CHAR(80) FIELD_FORMAT='TRANSLATOR',
+  Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
+  Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
+  Year int(4) FIELD_FORMAT='DATEPUB'
+)
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
+SELECT * FROM t1;
+UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab';
+SELECT * FROM t1 WHERE ISBN = '9782212090819';
+
+--echo #
+--echo # To add an author a new table must be created
+--echo #
+CREATE TABLE t2 (
+FIRSTNAME CHAR(32),
+LASTNAME CHAR(32))
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[1]:AUTHOR';
+SELECT * FROM t2;
+INSERT INTO t2 VALUES('Charles','Dickens');
+SELECT * FROM t1;
+DROP TABLE t1;
+DROP TABLE t2;
+
+--echo #
+--echo # Check the biblio file has the good format
+--echo #
+CREATE TABLE t1
+(
+  line char(255)
+)
+ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # A file with 2 arrays
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12),
+WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Now it can be fully expanded
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12),
+WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+#--error ER_GET_ERRMSG
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # A table showing many calculated results
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12) NOT NULL,
+WEEKS CHAR(12) NOT NULL FIELD_FORMAT='WEEK:[", "]:NUMBER',
+SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT',
+SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT',
+AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT',
+SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT',
+AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT',
+AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT',
+AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Expand expense in 3 one week tables
+--echo #
+CREATE TABLE t2 (
+WHO CHAR(12),
+WEEK INT(2) FIELD_FORMAT='WEEK:[0]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t2;
+
+CREATE TABLE t3 (
+WHO CHAR(12),
+WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t3;
+
+CREATE TABLE t4 (
+WHO CHAR(12),
+WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t4;
+
+--echo #
+--echo # The expanded table is made as a TBL table
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32),
+AMOUNT DOUBLE(8,2))
+ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t2,t3,t4';
+SELECT * FROM t1;
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # Three partial JSON tables
+--echo #
+CREATE TABLE t2 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json';
+SELECT * FROM t2;
+
+CREATE TABLE t3 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json';
+SELECT * FROM t3;
+
+CREATE TABLE t4 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json';
+SELECT * FROM t4;
+
+--echo #
+--echo # The complete table can be a multiple JSON table
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1;
+SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT;
+DROP TABLE t1;
+
+--echo #
+--echo # Or also a partition JSON table
+--echo #
+CREATE TABLE t1 (
+WHO CHAR(12),
+WEEK INT(2),
+WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json';
+ALTER TABLE t1
+PARTITION BY LIST COLUMNS(WEEK) (
+PARTITION `3` VALUES IN(3),
+PARTITION `4` VALUES IN(4),
+PARTITION `5` VALUES IN(5));
+SHOW WARNINGS;
+SELECT * FROM t1;
+SELECT * FROM t1 WHERE WEEK = 4;
+DROP TABLE t1, t2, t3, t4;
+
+#
+# Clean up
+#
+--remove_file $MYSQLD_DATADIR/test/biblio.json
+--remove_file $MYSQLD_DATADIR/test/expense.json
+--remove_file $MYSQLD_DATADIR/test/mulexp3.json
+--remove_file $MYSQLD_DATADIR/test/mulexp4.json
+--remove_file $MYSQLD_DATADIR/test/mulexp5.json