From 5c8862ee19eed2b6ddb9116c40c9ebbd67ee71ea Mon Sep 17 00:00:00 2001 From: Olivier Bertrand Date: Sun, 1 Mar 2015 19:20:40 +0100 Subject: - Fix crash when Json_Value was called without arguments. Correct memory calculation in Serialize. Correct some UDF's messages. Add and modify the json tests removed: storage/connect/mysql-test/connect/std_data/biblio.jsn storage/connect/mysql-test/connect/std_data/expense.jsn storage/connect/mysql-test/connect/std_data/mulexp3.jsn storage/connect/mysql-test/connect/std_data/mulexp4.jsn storage/connect/mysql-test/connect/std_data/mulexp5.jsn added: storage/connect/mysql-test/connect/r/json_udf.result storage/connect/mysql-test/connect/std_data/biblio.json storage/connect/mysql-test/connect/std_data/expense.json storage/connect/mysql-test/connect/std_data/mulexp3.json storage/connect/mysql-test/connect/std_data/mulexp4.json storage/connect/mysql-test/connect/std_data/mulexp5.json storage/connect/mysql-test/connect/t/json_udf.test modified: storage/connect/json.cpp storage/connect/jsonudf.cpp storage/connect/mysql-test/connect/r/json.result storage/connect/mysql-test/connect/t/json.test --- storage/connect/json.cpp | 2 +- storage/connect/jsonudf.cpp | 6 +- storage/connect/mysql-test/connect/r/json.result | 52 ++++-- .../connect/mysql-test/connect/r/json_udf.result | 174 +++++++++++++++++++++ .../connect/mysql-test/connect/std_data/biblio.jsn | 45 ------ .../mysql-test/connect/std_data/biblio.json | 45 ++++++ .../mysql-test/connect/std_data/expense.jsn | 158 ------------------- .../mysql-test/connect/std_data/expense.json | 158 +++++++++++++++++++ .../mysql-test/connect/std_data/mulexp3.jsn | 52 ------ .../mysql-test/connect/std_data/mulexp3.json | 52 ++++++ .../mysql-test/connect/std_data/mulexp4.jsn | 52 ------ .../mysql-test/connect/std_data/mulexp4.json | 52 ++++++ .../mysql-test/connect/std_data/mulexp5.jsn | 52 ------ .../mysql-test/connect/std_data/mulexp5.json | 52 ++++++ storage/connect/mysql-test/connect/t/json.test | 69 +++++--- storage/connect/mysql-test/connect/t/json_udf.test | 99 ++++++++++++ 16 files changed, 715 insertions(+), 405 deletions(-) create mode 100644 storage/connect/mysql-test/connect/r/json_udf.result delete mode 100644 storage/connect/mysql-test/connect/std_data/biblio.jsn create mode 100644 storage/connect/mysql-test/connect/std_data/biblio.json delete mode 100644 storage/connect/mysql-test/connect/std_data/expense.jsn create mode 100644 storage/connect/mysql-test/connect/std_data/expense.json delete mode 100644 storage/connect/mysql-test/connect/std_data/mulexp3.jsn create mode 100644 storage/connect/mysql-test/connect/std_data/mulexp3.json delete mode 100644 storage/connect/mysql-test/connect/std_data/mulexp4.jsn create mode 100644 storage/connect/mysql-test/connect/std_data/mulexp4.json delete mode 100644 storage/connect/mysql-test/connect/std_data/mulexp5.jsn create mode 100644 storage/connect/mysql-test/connect/std_data/mulexp5.json create mode 100644 storage/connect/mysql-test/connect/t/json_udf.test diff --git a/storage/connect/json.cpp b/storage/connect/json.cpp index 7356a86d53c..6f4bd18457b 100644 --- a/storage/connect/json.cpp +++ b/storage/connect/json.cpp @@ -662,7 +662,7 @@ JOUTSTR::JOUTSTR(PGLOBAL g) : JOUT(g) N = 0; Max = pph->FreeBlk; - Max = (Max > 512) ? Max - 512 : Max; + Max = (Max > 32) ? Max - 32 : Max; Strp = (char*)PlugSubAlloc(g, NULL, 0); // Size not know yet } // end of JOUTSTR constructor diff --git a/storage/connect/jsonudf.cpp b/storage/connect/jsonudf.cpp index 36ea87630cd..0879210c23e 100644 --- a/storage/connect/jsonudf.cpp +++ b/storage/connect/jsonudf.cpp @@ -232,7 +232,7 @@ static PSZ MakeKey(PGLOBAL g, UDF_ARGS *args, int i) /***********************************************************************/ static PJVAL MakeValue(PGLOBAL g, UDF_ARGS *args, int i) { - char *sap = args->args[i]; + char *sap = (args->arg_count > i) ? args->args[i] : NULL; PJSON jsp; PJVAL jvp = new(g) JVALUE; @@ -362,7 +362,7 @@ my_bool Json_Array_Add_init(UDF_INIT *initid, UDF_ARGS *args, char *message) strcpy(message, "Json_Value_Add must have at least 2 arguments"); return true; } else if (!IsJson(args, 0)) { - strcpy(message, "Json_Value_Add first argument must be a json array"); + strcpy(message, "Json_Value_Add first argument must be a json item"); return true; } else CalcLen(args, false, reslen, memlen); @@ -561,7 +561,7 @@ my_bool Json_Object_Grp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) unsigned long reslen, memlen, n = GetJsonGrpSize(); if (args->arg_count != 2) { - strcpy(message, "Json_Array_Grp can only accept 2 argument"); + strcpy(message, "Json_Array_Grp can only accept 2 arguments"); return true; } else CalcLen(args, true, reslen, memlen); diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result index e832685c855..ebcad699759 100644 --- a/storage/connect/mysql-test/connect/r/json.result +++ b/storage/connect/mysql-test/connect/r/json.result @@ -12,7 +12,7 @@ TRANSLATION CHAR(32), TRANSLATOR CHAR(80), PUBLISHER CHAR(32), DATEPUB int(4) -) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB 9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999 @@ -34,7 +34,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject Authors Title Translation Translator Publisher Location Year 9782212090819 fr applications 2 Construire une application XML Eyrolles Paris 1999 @@ -57,7 +57,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year 9782212090819 fr applications Jean-Christophe and François Bernadac and Knab Construire une application XML Eyrolles Paris 1999 @@ -80,7 +80,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year 9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999 @@ -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.jsn' OPTION_LIST='Object=[2]:AUTHOR'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[2]:AUTHOR'; SELECT * FROM t2; FIRSTNAME LASTNAME William J. Pardi @@ -117,7 +117,7 @@ CREATE TABLE t1 ( line char(255) ) -ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; SELECT * FROM t1; line [ @@ -178,7 +178,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT Joe 3 Beer+Food+Food+Car 69.00 @@ -199,7 +199,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -228,6 +228,26 @@ Janet 5 Beer 19.00 Janet 5 Food 12.00 DROP TABLE t1; # +# A table showing many calculated results +# +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; +WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVGAVG AVERAGE +Joe 3, 4, 5 69.00+83.00+26.00 178.00 17.25+16.60+13.00 46.85 59.33 15.62 16.18 +Beth 3, 4, 5 16.00+32.00+32.00 80.00 16.00+16.00+16.00 48.00 26.67 16.00 16.00 +Janet 3, 4, 5 55.00+17.00+57.00 129.00 18.33+17.00+14.25 49.58 43.00 16.53 16.12 +DROP TABLE t1; +# # Expand expense in 3 one week tables # CREATE TABLE t2 ( @@ -235,7 +255,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -251,7 +271,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -267,7 +287,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -322,7 +342,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT Joe 3 Beer 18.00 @@ -338,7 +358,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT Joe 4 Beer 19.00 @@ -354,7 +374,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT Joe 5 Beer 14.00 @@ -373,7 +393,7 @@ 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*.jsn' MULTIPLE=1; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1; SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; WHO WEEK WHAT AMOUNT Beth 3 Beer 16.00 @@ -409,7 +429,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json'; ALTER TABLE t1 PARTITION BY LIST COLUMNS(WEEK) ( PARTITION `3` VALUES IN(3), diff --git a/storage/connect/mysql-test/connect/r/json_udf.result b/storage/connect/mysql-test/connect/r/json_udf.result new file mode 100644 index 00000000000..4a672bf0d44 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/json_udf.result @@ -0,0 +1,174 @@ +CREATE FUNCTION Json_Array RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Array_Add RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Object RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Object_Nonull RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Value returns STRING SONAME 'ha_connect'; +CREATE AGGREGATE FUNCTION Json_Array_Grp RETURNS STRING SONAME 'ha_connect'; +CREATE AGGREGATE FUNCTION Json_Object_Grp RETURNS STRING SONAME 'ha_connect'; +# +# Test UDF's with constant arguments +# +SELECT Json_Array(); +Json_Array() +[] +SELECT Json_Object(56,3.1416,'foo',NULL); +Json_Object(56,3.1416,'foo',NULL) +{"56":56,"3.1416":3.141600,"foo":"foo","NULL":null} +SELECT Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty); +Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty) +{"qty":56,"price":3.141600,"truc":"foo","garanty":null} +SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL); +Json_Array(56,3.1416,'My name is "Foo"',NULL) +[56,3.141600,"My name is \"Foo\"",null] +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array; +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add must have at least 2 arguments +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array; +Array +[56,3.141600,"foo",null,"One more"] +SELECT Json_Array_Add(Json_Value('one value'),'One more'); +Json_Array_Add(Json_Value('one value'),'One more') +["one value","One more"] +SELECT Json_Array_Add('one value','One more'); +ERROR HY000: Can't initialize function 'Json_Array_Add'; Json_Value_Add first argument must be a json item +SELECT Json_Array_Add('one value' json_,'One more'); +Json_Array_Add('one value' json_,'One more') +[null,"One more"] +Warnings: +Warning 1105 Bad 'o' character near one value +SELECT Json_Value(56,3.1416,'foo',NULL); +ERROR HY000: Can't initialize function 'Json_Value'; Json_Value cannot accept more than 1 argument +SELECT Json_Value(3.1416); +Json_Value(3.1416) +3.141600 +SELECT Json_Value('foo'); +Json_Value('foo') +"foo" +SELECT Json_Value(NULL); +Json_Value(NULL) +null +SELECT Json_Value(); +Json_Value() +null +SELECT Json_Object(); +Json_Object() +{} +SELECT Json_Object(Json_Array(56,3.1416,'foo'),NULL); +Json_Object(Json_Array(56,3.1416,'foo'),NULL) +{"Array(56,3.1416,'foo')":[56,3.141600,"foo"],"NULL":null} +SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL); +Json_Array(Json_Array(56,3.1416,'foo'),NULL) +[[56,3.141600,"foo"],null] +SELECT Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL); +Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL) +[{"qty":56,"price":3.141600,"foo":"foo"},null] +# +# Test UDF's with column arguments +# +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 Json_Array(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Array(AUTHOR, TITLE, DATEPUB) +["Jean-Christophe Bernadac","Construire une application XML",1999] +["William J. Pardi","XML en Action",1999] +SELECT Json_Object(AUTHOR, TITLE, DATEPUB) FROM t1; +Json_Object(AUTHOR, TITLE, DATEPUB) +{"AUTHOR":"Jean-Christophe Bernadac","TITLE":"Construire une application XML","DATEPUB":1999} +{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} +SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t1; +ERROR HY000: Can't initialize function 'Json_Array_Grp'; Json_Array_Grp can only accept 1 argument +SELECT Json_Array_Grp(TITLE) FROM t1; +Json_Array_Grp(TITLE) +["Construire une application XML","XML en Action"] +DROP TABLE t1; +CREATE TABLE t1 ( +SERIALNO CHAR(5) NOT NULL, +NAME VARCHAR(12) NOT NULL FLAG=6, +SEX SMALLINT(1) NOT NULL, +TITLE VARCHAR(15) NOT NULL FLAG=20, +MANAGER CHAR(5) DEFAULT NULL, +DEPARTMENT CHAr(4) NOT NULL FLAG=41, +SECRETARY CHAR(5) DEFAULT NULL FLAG=46, +SALARY DOUBLE(8,2) NOT NULL FLAG=52 +) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1; +SELECT Json_Object(SERIALNO, NAME, TITLE, SALARY) FROM t1 WHERE NAME = 'MERCHANT'; +Json_Object(SERIALNO, NAME, TITLE, SALARY) +{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000} +SELECT DEPARTMENT, Json_Array_Grp(NAME) FROM t1 GROUP BY DEPARTMENT; +DEPARTMENT Json_Array_Grp(NAME) +0021 ["STRONG","SHORTSIGHT"] +0318 ["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"] +0319 ["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL"] +2452 ["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"] +Warnings: +Warning 1105 Result truncated to json_grp_size values +set connect_json_grp_size=30; +SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t1 GROUP BY DEPARTMENT; +Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) +["0021",["STRONG","SHORTSIGHT"]] +["0318",["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]] +["0319",["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]] +["2452",["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]] +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) +{"DEPARTMENT":"0021","NAMES":["STRONG","SHORTSIGHT"]} +{"DEPARTMENT":"0318","NAMES":["BANCROFT","PLUMHEAD","HONEY","TONGHO","WALTER","SHRINKY","WERTHER","MERCHANT","WHEELFOR"]} +{"DEPARTMENT":"0319","NAMES":["BULLOZER","QUINN","BROWNY","KITTY","MONAPENNY","MARTIN","FUNNIGUY","BUGHAPPY","FODDERMAN","MESSIFUL","GOOSEPEN"]} +{"DEPARTMENT":"2452","NAMES":["BIGHEAD","ORELLY","BIGHORN","SMITH","CHERRY"]} +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.000000},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","TITLE":"SALESMAN","SALARY":9600.000000},{"SERIALNO":"24888","NAME":"PLUMHEAD","TITLE":"TYPIST","SALARY":2800.000000},{"SERIALNO":"27845","NAME":"HONEY","TITLE":"SECRETARY","SALARY":4900.000000},{"SERIALNO":"73452","NAME":"TONGHO","TITLE":"ENGINEER","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","TITLE":"ENGINEER","SALARY":7400.000000},{"SERIALNO":"77777","NAME":"SHRINKY","TITLE":"ADMINISTRATOR","SALARY":7500.000000},{"SERIALNO":"70012","NAME":"WERTHER","TITLE":"DIRECTOR","SALARY":14500.000000},{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","TITLE":"SALESMAN","SALARY":10030.000000}]} +{"DEPARTMENT":"0319","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","TITLE":"SALESMAN","SALARY":14800.000000},{"SERIALNO":"40567","NAME":"QUINN","TITLE":"DIRECTOR","SALARY":14000.000000},{"SERIALNO":"00137","NAME":"BROWNY","TITLE":"ENGINEER","SALARY":10500.000000},{"SERIALNO":"12345","NAME":"KITTY","TITLE":"TYPIST","SALARY":3000.450000},{"SERIALNO":"33333","NAME":"MONAPENNY","TITLE":"SECRETARY","SALARY":3800.000000},{"SERIALNO":"00023","NAME":"MARTIN","TITLE":"ENGINEER","SALARY":10000.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","TITLE":"ADMINISTRATOR","SALARY":8500.000000},{"SERIALNO":"45678","NAME":"BUGHAPPY","TITLE":"PROGRAMMER","SALARY":8500.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","TITLE":"SALESMAN","SALARY":7000.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","TITLE":"SECRETARY","SALARY":5000.500000},{"SERIALNO":"98765","NAME":"GOOSEPEN","TITLE":"ADMINISTRATOR","SALARY":4700.000000}]} +{"DEPARTMENT":"2452","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","TITLE":"SCIENTIST","SALARY":8000.000000},{"SERIALNO":"31416","NAME":"ORELLY","TITLE":"ENGINEER","SALARY":13400.000000},{"SERIALNO":"36666","NAME":"BIGHORN","TITLE":"SCIENTIST","SALARY":11000.000000},{"SERIALNO":"02345","NAME":"SMITH","TITLE":"ENGINEER","SALARY":9000.000000},{"SERIALNO":"11111","NAME":"CHERRY","TITLE":"SECRETARY","SALARY":4500.000000}]} +SELECT Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT, TITLE; +Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.000000}]} +{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.000000}]} +{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.000000}]} +{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.000000},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.000000}]} +{"DEPARTMENT":"0318","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","SALARY":9600.000000},{"SERIALNO":"78943","NAME":"MERCHANT","SALARY":8700.000000},{"SERIALNO":"73111","NAME":"WHEELFOR","SALARY":10030.000000}]} +{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.000000}]} +{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.000000}]} +{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.000000},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.000000}]} +{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.000000},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.000000}]} +{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.000000}]} +{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.000000},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.000000}]} +{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.000000},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.500000}]} +{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.450000}]} +{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.000000},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.000000},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.000000}]} +{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.000000}]} +SELECT Json_Object_Grp(SALARY) FROM t1; +ERROR HY000: Can't initialize function 'Json_Object_Grp'; Json_Array_Grp can only accept 2 arguments +SELECT Json_Object_Grp(SALARY, NAME) FROM t1; +Json_Object_Grp(SALARY, NAME) +{"BANCROFT":9600.000000,"SMITH":9000.000000,"MERCHANT":8700.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"BIGHEAD":8000.000000,"SHRINKY":7500.000000,"WALTER":7400.000000,"FODDERMAN":7000.000000,"TONGHO":6800.000000,"SHORTSIGHT":5500.000000,"MESSIFUL":5000.500000,"HONEY":4900.000000,"GOOSEPEN":4700.000000,"CHERRY":4500.000000,"MONAPENNY":3800.000000,"KITTY":3000.450000,"PLUMHEAD":2800.000000,"STRONG":23000.000000,"BULLOZER":14800.000000,"WERTHER":14500.000000,"QUINN":14000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"BROWNY":10500.000000,"WHEELFOR":10030.000000,"MARTIN":10000.000000} +SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t1 GROUP BY DEPARTMENT; +Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") +{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.000000,"SHORTSIGHT":5500.000000}} +{"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.000000,"PLUMHEAD":2800.000000,"HONEY":4900.000000,"TONGHO":6800.000000,"WALTER":7400.000000,"SHRINKY":7500.000000,"WERTHER":14500.000000,"MERCHANT":8700.000000,"WHEELFOR":10030.000000}} +{"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.000000,"QUINN":14000.000000,"BROWNY":10500.000000,"KITTY":3000.450000,"MONAPENNY":3800.000000,"MARTIN":10000.000000,"FUNNIGUY":8500.000000,"BUGHAPPY":8500.000000,"FODDERMAN":7000.000000,"MESSIFUL":5000.500000,"GOOSEPEN":4700.000000}} +{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.000000,"ORELLY":13400.000000,"BIGHORN":11000.000000,"SMITH":9000.000000,"CHERRY":4500.000000}} +SELECT Json_Array_Grp(NAME) from t1; +Json_Array_Grp(NAME) +["BANCROFT","SMITH","MERCHANT","FUNNIGUY","BUGHAPPY","BIGHEAD","SHRINKY","WALTER","FODDERMAN","TONGHO","SHORTSIGHT","MESSIFUL","HONEY","GOOSEPEN","CHERRY","MONAPENNY","KITTY","PLUMHEAD","STRONG","BULLOZER","WERTHER","QUINN","ORELLY","BIGHORN","BROWNY","WHEELFOR","MARTIN"] +DROP TABLE t1; +DROP FUNCTION Json_Array; +DROP FUNCTION Json_Array_Add; +DROP FUNCTION Json_Object; +DROP FUNCTION Json_Object_Nonull; +DROP FUNCTION Json_Value; +DROP FUNCTION Json_Array_Grp; +DROP FUNCTION Json_Object_Grp; diff --git a/storage/connect/mysql-test/connect/std_data/biblio.jsn b/storage/connect/mysql-test/connect/std_data/biblio.jsn deleted file mode 100644 index bab8fd24305..00000000000 --- a/storage/connect/mysql-test/connect/std_data/biblio.jsn +++ /dev/null @@ -1,45 +0,0 @@ -[ - { - "ISBN": "9782212090819", - "LANG": "fr", - "SUBJECT": "applications", - "AUTHOR": [ - { - "FIRSTNAME": "Jean-Christophe", - "LASTNAME": "Bernadac" - }, - { - "FIRSTNAME": "François", - "LASTNAME": "Knab" - } - ], - "TITLE": "Construire une application XML", - "PUBLISHER": { - "NAME": "Eyrolles", - "PLACE": "Paris" - }, - "DATEPUB": 1999 - }, - { - "ISBN": "9782840825685", - "LANG": "fr", - "SUBJECT": "applications", - "AUTHOR": [ - { - "FIRSTNAME": "William J.", - "LASTNAME": "Pardi" - } - ], - "TITLE": "XML en Action", - "TRANSLATION": "adapté de l'anglais par", - "TRANSLATOR": { - "FIRSTNAME": "James", - "LASTNAME": "Guerin" - }, - "PUBLISHER": { - "NAME": "Microsoft Press", - "PLACE": "Paris" - }, - "DATEPUB": 1999 - } -] diff --git a/storage/connect/mysql-test/connect/std_data/biblio.json b/storage/connect/mysql-test/connect/std_data/biblio.json new file mode 100644 index 00000000000..bab8fd24305 --- /dev/null +++ b/storage/connect/mysql-test/connect/std_data/biblio.json @@ -0,0 +1,45 @@ +[ + { + "ISBN": "9782212090819", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "Jean-Christophe", + "LASTNAME": "Bernadac" + }, + { + "FIRSTNAME": "François", + "LASTNAME": "Knab" + } + ], + "TITLE": "Construire une application XML", + "PUBLISHER": { + "NAME": "Eyrolles", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + }, + { + "ISBN": "9782840825685", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "William J.", + "LASTNAME": "Pardi" + } + ], + "TITLE": "XML en Action", + "TRANSLATION": "adapté de l'anglais par", + "TRANSLATOR": { + "FIRSTNAME": "James", + "LASTNAME": "Guerin" + }, + "PUBLISHER": { + "NAME": "Microsoft Press", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + } +] diff --git a/storage/connect/mysql-test/connect/std_data/expense.jsn b/storage/connect/mysql-test/connect/std_data/expense.jsn deleted file mode 100644 index e65ad5261f1..00000000000 --- a/storage/connect/mysql-test/connect/std_data/expense.jsn +++ /dev/null @@ -1,158 +0,0 @@ -[ - { - "WHO": "Joe", - "WEEK": [ - { - "NUMBER": 3, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 18.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - }, - { - "WHAT": "Food", - "AMOUNT": 19.00 - }, - { - "WHAT": "Car", - "AMOUNT": 20.00 - } - ] - }, - { - "NUMBER": 4, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 19.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 16.00 - }, - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 14.00 - } - ] - }, - { - "NUMBER": 5, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 14.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - } - ] - } - ] - }, - { - "WHO": "Beth", - "WEEK": [ - { - "NUMBER": 3, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 16.00 - } - ] - }, - { - "NUMBER": 4, - "EXPENSE": [ - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 15.00 - } - ] - }, - { - "NUMBER": 5, - "EXPENSE": [ - { - "WHAT": "Food", - "AMOUNT": 12.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 20.00 - } - ] - } - ] - }, - { - "WHO": "Janet", - "WEEK": [ - { - "NUMBER": 3, - "EXPENSE": [ - { - "WHAT": "Car", - "AMOUNT": 19.00 - }, - { - "WHAT": "Food", - "AMOUNT": 18.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 18.00 - } - ] - }, - { - "NUMBER": 4, - "EXPENSE": [ - { - "WHAT": "Car", - "AMOUNT": 17.00 - } - ] - }, - { - "NUMBER": 5, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 14.00 - }, - { - "WHAT": "Car", - "AMOUNT": 12.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 19.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - } - ] - } - ] - } -] diff --git a/storage/connect/mysql-test/connect/std_data/expense.json b/storage/connect/mysql-test/connect/std_data/expense.json new file mode 100644 index 00000000000..e65ad5261f1 --- /dev/null +++ b/storage/connect/mysql-test/connect/std_data/expense.json @@ -0,0 +1,158 @@ +[ + { + "WHO": "Joe", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 18.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Food", + "AMOUNT": 19.00 + }, + { + "WHAT": "Car", + "AMOUNT": 20.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 16.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 14.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } + ] + }, + { + "WHO": "Beth", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 16.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 15.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 20.00 + } + ] + } + ] + }, + { + "WHO": "Janet", + "WEEK": [ + { + "NUMBER": 3, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 18.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 18.00 + } + ] + }, + { + "NUMBER": 4, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 17.00 + } + ] + }, + { + "NUMBER": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Car", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn b/storage/connect/mysql-test/connect/std_data/mulexp3.jsn deleted file mode 100644 index c228448b073..00000000000 --- a/storage/connect/mysql-test/connect/std_data/mulexp3.jsn +++ /dev/null @@ -1,52 +0,0 @@ -[ - { - "WHO": "Joe", - "WEEK": 3, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 18.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - }, - { - "WHAT": "Food", - "AMOUNT": 19.00 - }, - { - "WHAT": "Car", - "AMOUNT": 20.00 - } - ] - }, - { - "WHO": "Beth", - "WEEK": 3, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 16.00 - } - ] - }, - { - "WHO": "Janet", - "WEEK": 3, - "EXPENSE": [ - { - "WHAT": "Car", - "AMOUNT": 19.00 - }, - { - "WHAT": "Food", - "AMOUNT": 18.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 18.00 - } - ] - } -] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp3.json b/storage/connect/mysql-test/connect/std_data/mulexp3.json new file mode 100644 index 00000000000..c228448b073 --- /dev/null +++ b/storage/connect/mysql-test/connect/std_data/mulexp3.json @@ -0,0 +1,52 @@ +[ + { + "WHO": "Joe", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 18.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Food", + "AMOUNT": 19.00 + }, + { + "WHAT": "Car", + "AMOUNT": 20.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 16.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 3, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 18.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 18.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn b/storage/connect/mysql-test/connect/std_data/mulexp4.jsn deleted file mode 100644 index 0e43ffec07b..00000000000 --- a/storage/connect/mysql-test/connect/std_data/mulexp4.jsn +++ /dev/null @@ -1,52 +0,0 @@ -[ - { - "WHO": "Joe", - "WEEK": 4, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 19.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 16.00 - }, - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 14.00 - } - ] - }, - { - "WHO": "Beth", - "WEEK": 4, - "EXPENSE": [ - { - "WHAT": "Food", - "AMOUNT": 17.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 15.00 - } - ] - }, - { - "WHO": "Janet", - "WEEK": 4, - "EXPENSE": [ - { - "WHAT": "Car", - "AMOUNT": 17.00 - } - ] - } -] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp4.json b/storage/connect/mysql-test/connect/std_data/mulexp4.json new file mode 100644 index 00000000000..0e43ffec07b --- /dev/null +++ b/storage/connect/mysql-test/connect/std_data/mulexp4.json @@ -0,0 +1,52 @@ +[ + { + "WHO": "Joe", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 16.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 14.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 17.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 15.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 4, + "EXPENSE": [ + { + "WHAT": "Car", + "AMOUNT": 17.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn b/storage/connect/mysql-test/connect/std_data/mulexp5.jsn deleted file mode 100644 index 7a707506c2f..00000000000 --- a/storage/connect/mysql-test/connect/std_data/mulexp5.jsn +++ /dev/null @@ -1,52 +0,0 @@ -[ - { - "WHO": "Joe", - "WEEK": 5, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 14.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - } - ] - }, - { - "WHO": "Beth", - "WEEK": 5, - "EXPENSE": [ - { - "WHAT": "Food", - "AMOUNT": 12.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 20.00 - } - ] - }, - { - "WHO": "Janet", - "WEEK": 5, - "EXPENSE": [ - { - "WHAT": "Beer", - "AMOUNT": 14.00 - }, - { - "WHAT": "Car", - "AMOUNT": 12.00 - }, - { - "WHAT": "Beer", - "AMOUNT": 19.00 - }, - { - "WHAT": "Food", - "AMOUNT": 12.00 - } - ] - } -] diff --git a/storage/connect/mysql-test/connect/std_data/mulexp5.json b/storage/connect/mysql-test/connect/std_data/mulexp5.json new file mode 100644 index 00000000000..7a707506c2f --- /dev/null +++ b/storage/connect/mysql-test/connect/std_data/mulexp5.json @@ -0,0 +1,52 @@ +[ + { + "WHO": "Joe", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + }, + { + "WHO": "Beth", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Food", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 20.00 + } + ] + }, + { + "WHO": "Janet", + "WEEK": 5, + "EXPENSE": [ + { + "WHAT": "Beer", + "AMOUNT": 14.00 + }, + { + "WHAT": "Car", + "AMOUNT": 12.00 + }, + { + "WHAT": "Beer", + "AMOUNT": 19.00 + }, + { + "WHAT": "Food", + "AMOUNT": 12.00 + } + ] + } +] diff --git a/storage/connect/mysql-test/connect/t/json.test b/storage/connect/mysql-test/connect/t/json.test index 91cb6308557..79588e9fe5b 100644 --- a/storage/connect/mysql-test/connect/t/json.test +++ b/storage/connect/mysql-test/connect/t/json.test @@ -3,11 +3,11 @@ let $MYSQLD_DATADIR= `select @@datadir`; ---copy_file $MTR_SUITE_DIR/std_data/biblio.jsn $MYSQLD_DATADIR/test/biblio.jsn ---copy_file $MTR_SUITE_DIR/std_data/expense.jsn $MYSQLD_DATADIR/test/expense.jsn ---copy_file $MTR_SUITE_DIR/std_data/mulexp3.jsn $MYSQLD_DATADIR/test/mulexp3.jsn ---copy_file $MTR_SUITE_DIR/std_data/mulexp4.jsn $MYSQLD_DATADIR/test/mulexp4.jsn ---copy_file $MTR_SUITE_DIR/std_data/mulexp5.jsn $MYSQLD_DATADIR/test/mulexp5.jsn +--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 @@ -23,7 +23,7 @@ CREATE TABLE t1 TRANSLATOR CHAR(80), PUBLISHER CHAR(32), DATEPUB int(4) -) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; DROP TABLE t1; @@ -44,7 +44,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; DROP TABLE t1; @@ -65,7 +65,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; DROP TABLE t1; @@ -86,7 +86,7 @@ CREATE TABLE t1 Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE', Year int(4) FIELD_FORMAT='DATEPUB' ) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn'; +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'; @@ -97,7 +97,7 @@ SELECT * FROM t1 WHERE ISBN = '9782212090819'; CREATE TABLE t2 ( FIRSTNAME CHAR(32), LASTNAME CHAR(32)) -ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]:AUTHOR'; +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; @@ -111,7 +111,7 @@ CREATE TABLE t1 ( line char(255) ) -ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn'; +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; SELECT * FROM t1; DROP TABLE t1; @@ -123,7 +123,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; DROP TABLE t1; @@ -135,11 +135,28 @@ 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.jsn'; +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 # @@ -148,7 +165,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t2; CREATE TABLE t3 ( @@ -156,7 +173,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t3; CREATE TABLE t4 ( @@ -164,7 +181,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t4; --echo # @@ -187,7 +204,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; SELECT * FROM t2; CREATE TABLE t3 ( @@ -195,7 +212,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; SELECT * FROM t3; CREATE TABLE t4 ( @@ -203,7 +220,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json'; SELECT * FROM t4; --echo # @@ -214,7 +231,7 @@ 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*.jsn' MULTIPLE=1; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1; SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; DROP TABLE t1; @@ -226,7 +243,7 @@ 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.jsn'; +ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json'; ALTER TABLE t1 PARTITION BY LIST COLUMNS(WEEK) ( PARTITION `3` VALUES IN(3), @@ -240,8 +257,8 @@ DROP TABLE t1, t2, t3, t4; # # Clean up # ---remove_file $MYSQLD_DATADIR/test/biblio.jsn ---remove_file $MYSQLD_DATADIR/test/expense.jsn ---remove_file $MYSQLD_DATADIR/test/mulexp3.jsn ---remove_file $MYSQLD_DATADIR/test/mulexp4.jsn ---remove_file $MYSQLD_DATADIR/test/mulexp5.jsn +--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 diff --git a/storage/connect/mysql-test/connect/t/json_udf.test b/storage/connect/mysql-test/connect/t/json_udf.test new file mode 100644 index 00000000000..744e1764e3b --- /dev/null +++ b/storage/connect/mysql-test/connect/t/json_udf.test @@ -0,0 +1,99 @@ +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/employee.dat $MYSQLD_DATADIR/test/employee.dat + +CREATE FUNCTION Json_Array RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Array_Add RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Object RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Object_Nonull RETURNS STRING SONAME 'ha_connect'; +CREATE FUNCTION Json_Value returns STRING SONAME 'ha_connect'; +CREATE AGGREGATE FUNCTION Json_Array_Grp RETURNS STRING SONAME 'ha_connect'; +CREATE AGGREGATE FUNCTION Json_Object_Grp RETURNS STRING SONAME 'ha_connect'; + +--echo # +--echo # Test UDF's with constant arguments +--echo # +SELECT Json_Array(); +SELECT Json_Object(56,3.1416,'foo',NULL); +SELECT Json_Object(56 qty,3.1416 price,'foo' truc, NULL garanty); +SELECT Json_Array(56,3.1416,'My name is "Foo"',NULL); +--error ER_CANT_INITIALIZE_UDF +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL)) Array; +SELECT Json_Array_Add(Json_Array(56,3.1416,'foo',NULL),'One more') Array; +SELECT Json_Array_Add(Json_Value('one value'),'One more'); +--error ER_CANT_INITIALIZE_UDF +SELECT Json_Array_Add('one value','One more'); +SELECT Json_Array_Add('one value' json_,'One more'); +--error ER_CANT_INITIALIZE_UDF +SELECT Json_Value(56,3.1416,'foo',NULL); +SELECT Json_Value(3.1416); +SELECT Json_Value('foo'); +SELECT Json_Value(NULL); +SELECT Json_Value(); +SELECT Json_Object(); +SELECT Json_Object(Json_Array(56,3.1416,'foo'),NULL); +SELECT Json_Array(Json_Array(56,3.1416,'foo'),NULL); +SELECT Json_Array(Json_Object(56 "qty",3.1416 "price",'foo'),NULL); + +--echo # +--echo # Test UDF's with column arguments +--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 Json_Array(AUTHOR, TITLE, DATEPUB) FROM t1; +SELECT Json_Object(AUTHOR, TITLE, DATEPUB) FROM t1; +--error ER_CANT_INITIALIZE_UDF +SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t1; +SELECT Json_Array_Grp(TITLE) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + SERIALNO CHAR(5) NOT NULL, + NAME VARCHAR(12) NOT NULL FLAG=6, + SEX SMALLINT(1) NOT NULL, + TITLE VARCHAR(15) NOT NULL FLAG=20, + MANAGER CHAR(5) DEFAULT NULL, + DEPARTMENT CHAr(4) NOT NULL FLAG=41, + SECRETARY CHAR(5) DEFAULT NULL FLAG=46, + SALARY DOUBLE(8,2) NOT NULL FLAG=52 +) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=8 FILE_NAME='employee.dat' ENDING=1; + +SELECT Json_Object(SERIALNO, NAME, TITLE, SALARY) FROM t1 WHERE NAME = 'MERCHANT'; +SELECT DEPARTMENT, Json_Array_Grp(NAME) FROM t1 GROUP BY DEPARTMENT; +set connect_json_grp_size=30; +SELECT Json_Array(DEPARTMENT, Json_Array_Grp(NAME)) FROM t1 GROUP BY DEPARTMENT; +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(NAME) json_NAMES) FROM t1 GROUP BY DEPARTMENT; +SELECT Json_Object(DEPARTMENT, Json_Array_Grp(Json_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT; +SELECT Json_Object(DEPARTMENT, TITLE, Json_Array_Grp(Json_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t1 GROUP BY DEPARTMENT, TITLE; +--error ER_CANT_INITIALIZE_UDF +SELECT Json_Object_Grp(SALARY) FROM t1; +SELECT Json_Object_Grp(SALARY, NAME) FROM t1; +SELECT Json_Object(DEPARTMENT, Json_Object_Grp(SALARY, NAME) "Json_SALARIES") FROM t1 GROUP BY DEPARTMENT; +SELECT Json_Array_Grp(NAME) from t1; +DROP TABLE t1; + +DROP FUNCTION Json_Array; +DROP FUNCTION Json_Array_Add; +DROP FUNCTION Json_Object; +DROP FUNCTION Json_Object_Nonull; +DROP FUNCTION Json_Value; +DROP FUNCTION Json_Array_Grp; +DROP FUNCTION Json_Object_Grp; + +# +# Clean up +# +--remove_file $MYSQLD_DATADIR/test/biblio.json +--remove_file $MYSQLD_DATADIR/test/employee.dat -- cgit v1.2.1