diff options
Diffstat (limited to 'storage/connect/mysql-test/connect/r')
33 files changed, 2936 insertions, 483 deletions
diff --git a/storage/connect/mysql-test/connect/r/alter_xml.result b/storage/connect/mysql-test/connect/r/alter_xml.result index 7cdb1e5d21c..d2f882f1287 100644 --- a/storage/connect/mysql-test/connect/r/alter_xml.result +++ b/storage/connect/mysql-test/connect/r/alter_xml.result @@ -54,7 +54,7 @@ line </t1> # NOTE: The first (ignored) row is due to the remaining HEADER=1 option. # Testing field option modification -ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL FIELD_FORMAT='@', HEADER=0; +ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL XPATH='@', HEADER=0; SELECT * FROM t1; c d 1 One @@ -64,7 +64,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c` int(11) NOT NULL, - `d` char(10) NOT NULL `FIELD_FORMAT`='@' + `d` char(10) NOT NULL `XPATH`='@' ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=domdoc,rownode=row' `HEADER`=0 SELECT * FROM t2; line diff --git a/storage/connect/mysql-test/connect/r/alter_xml2.result b/storage/connect/mysql-test/connect/r/alter_xml2.result index 8eb56e3dcc3..a15be966aa8 100644 --- a/storage/connect/mysql-test/connect/r/alter_xml2.result +++ b/storage/connect/mysql-test/connect/r/alter_xml2.result @@ -56,7 +56,7 @@ line </t1> # NOTE: The first (ignored) row is due to the remaining HEADER=1 option. # Testing field option modification -ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL FIELD_FORMAT='@', HEADER=0; +ALTER TABLE t1 MODIFY d CHAR(10) NOT NULL XPATH='@', HEADER=0; SELECT * FROM t1; c d 1 One @@ -66,7 +66,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c` int(11) NOT NULL, - `d` char(10) NOT NULL `FIELD_FORMAT`='@' + `d` char(10) NOT NULL `XPATH`='@' ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `QUOTED`=1 `TABLE_TYPE`=XML `TABNAME`=t1 `OPTION_LIST`='xmlsup=libxml2,rownode=row' `HEADER`=0 SELECT * FROM t2; line diff --git a/storage/connect/mysql-test/connect/r/bson.result b/storage/connect/mysql-test/connect/r/bson.result new file mode 100644 index 00000000000..fd15e020aac --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson.result @@ -0,0 +1,517 @@ +# +# Testing doc samples +# +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=BSON FILE_NAME='biblio.json'; +SELECT * FROM t1; +ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB +9782212090819 fr applications Jean-Christophe Bernadac, François Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Testing Jpath. Get the number of authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +Authors INT(2) JPATH='$.AUTHOR[#]', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON 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 NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications 1 XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Concatenates the authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[" and "].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[" and "].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON 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 NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +# +# Testing expanding authors +# +CREATE TABLE t1 +( +ISBN CHAR(15), +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' +) +ENGINE=CONNECT TABLE_TYPE=BSON 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 NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab'; +SELECT * FROM t1 WHERE ISBN = '9782212090819'; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications Philippe Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +# +# To add an author a new table must be created +# +CREATE TABLE t2 ( +FIRSTNAME CHAR(32), +LASTNAME CHAR(32)) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='biblio.json' OPTION_LIST='Object=$[1].AUTHOR'; +SELECT * FROM t2; +FIRSTNAME LASTNAME +William J. Pardi +INSERT INTO t2 VALUES('Charles','Dickens'); +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year +9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications Philippe Knab Construire une application XML NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +9782840825685 fr applications Charles Dickens XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 +DROP TABLE t1; +DROP TABLE t2; +# +# Check the biblio file has the good format +# +CREATE TABLE t1 +( +line char(255) +) +ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json'; +SELECT * FROM t1; +line +[ + { + "ISBN": "9782212090819", + "LANG": "fr", + "SUBJECT": "applications", + "AUTHOR": [ + { + "FIRSTNAME": "Jean-Christophe", + "LASTNAME": "Bernadac" + }, + { + "FIRSTNAME": "Philippe", + "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" + }, + { + "FIRSTNAME": "Charles", + "LASTNAME": "Dickens" + } + ], + "TITLE": "XML en Action", + "TRANSLATION": "adapté de l'anglais par", + "TRANSLATOR": { + "FIRSTNAME": "James", + "LASTNAME": "Guerin" + }, + "PUBLISHER": { + "NAME": "Microsoft Press", + "PLACE": "Paris" + }, + "DATEPUB": 1999 + } +] +DROP TABLE t1; +# +# Testing a pretty=0 file +# +CREATE TABLE t1 +( +ISBN CHAR(15) NOT NULL, +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATED.PREFIX', +TranslatorFN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.FIRSTNAME', +TranslatorLN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.LASTNAME', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB', +INDEX IX(ISBN) +) +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='bib0.json' LRECL=320 OPTION_LIST='Pretty=0'; +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 IX 1 ISBN A NULL NULL NULL XINDEX +SELECT * FROM t1; +ISBN Language Subject AuthorFN AuthorLN Title Translation TranslatorFN TranslatorLN Publisher Location Year +9782212090819 fr applications Jean-Michel Bernadac Construire une application XML NULL NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications François Knab Construire une application XML NULL NULL NULL Eyrolles Paris 1999 +9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 2001 +DESCRIBE SELECT * FROM t1 WHERE ISBN = '9782212090819'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref IX IX 15 const 1 Using where +UPDATE t1 SET AuthorFN = 'Philippe' WHERE ISBN = '9782212090819'; +ERROR HY000: Got error 122 'Cannot write expanded column when Pretty is not 2' from CONNECT +DROP TABLE t1; +# +# A file with 2 arrays +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[].EXPENSE["+"].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[].EXPENSE[+].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer+Food+Food+Car 69.00 +Joe 4 Beer+Beer+Food+Food+Beer 83.00 +Joe 5 Beer+Food 26.00 +Beth 3 Beer 16.00 +Beth 4 Food+Beer 32.00 +Beth 5 Food+Beer 32.00 +Janet 3 Car+Food+Beer 55.00 +Janet 4 Car 17.00 +Janet 5 Beer+Car+Beer+Food 57.00 +DROP TABLE t1; +# +# Now it can be fully expanded +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[*].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[*].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 3 Beer 16.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Janet 4 Car 17.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +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 JPATH='$.WEEK[", "].NUMBER', +SUMS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[+].AMOUNT', +SUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[+].AMOUNT', +AVGS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[!].AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[!].AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[+].AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[!].AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON 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 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[0].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[0].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[0].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t2; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[1].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[1].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[1].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t3; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2) JPATH='$.WEEK[2].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[2].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[2].EXPENSE[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='expense.json'; +SELECT * FROM t4; +WHO WEEK WHAT AMOUNT +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +# +# The expanded table is made as a TBL table +# +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; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +DROP TABLE t1, t2, t3, t4; +# +# Three partial JSON tables +# +CREATE TABLE t2 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp3.json'; +SELECT * FROM t2; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +CREATE TABLE t3 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp4.json'; +SELECT * FROM t3; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +CREATE TABLE t4 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp5.json'; +SELECT * FROM t4; +WHO WEEK WHAT AMOUNT +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +# +# The complete table can be a multiple JSON table +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON FILE_NAME='mulexp*.json' MULTIPLE=1; +SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; +WHO WEEK WHAT AMOUNT +Beth 3 Beer 16.00 +Beth 4 Beer 15.00 +Beth 4 Food 17.00 +Beth 5 Beer 20.00 +Beth 5 Food 12.00 +Janet 3 Beer 18.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 4 Car 17.00 +Janet 5 Beer 14.00 +Janet 5 Beer 19.00 +Janet 5 Car 12.00 +Janet 5 Food 12.00 +Joe 3 Beer 18.00 +Joe 3 Car 20.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 4 Beer 14.00 +Joe 4 Beer 16.00 +Joe 4 Beer 19.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +DROP TABLE t1; +# +# Or also a partition JSON table +# +CREATE TABLE t1 ( +WHO CHAR(12), +WEEK INT(2), +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') +ENGINE=CONNECT TABLE_TYPE=BSON 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)); +Warnings: +Warning 1105 Data repartition in 3 is unchecked +Warning 1105 Data repartition in 4 is unchecked +Warning 1105 Data repartition in 5 is unchecked +SHOW WARNINGS; +Level Code Message +Warning 1105 Data repartition in 3 is unchecked +Warning 1105 Data repartition in 4 is unchecked +Warning 1105 Data repartition in 5 is unchecked +SELECT * FROM t1; +WHO WEEK WHAT AMOUNT +Joe 3 Beer 18.00 +Joe 3 Food 12.00 +Joe 3 Food 19.00 +Joe 3 Car 20.00 +Beth 3 Beer 16.00 +Janet 3 Car 19.00 +Janet 3 Food 18.00 +Janet 3 Beer 18.00 +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +Joe 5 Beer 14.00 +Joe 5 Food 12.00 +Beth 5 Food 12.00 +Beth 5 Beer 20.00 +Janet 5 Beer 14.00 +Janet 5 Car 12.00 +Janet 5 Beer 19.00 +Janet 5 Food 12.00 +SELECT * FROM t1 WHERE WEEK = 4; +WHO WEEK WHAT AMOUNT +Joe 4 Beer 19.00 +Joe 4 Beer 16.00 +Joe 4 Food 17.00 +Joe 4 Food 17.00 +Joe 4 Beer 14.00 +Beth 4 Food 17.00 +Beth 4 Beer 15.00 +Janet 4 Car 17.00 +DROP TABLE t1, t2, t3, t4; diff --git a/storage/connect/mysql-test/connect/r/bson_java_2.result b/storage/connect/mysql-test/connect/r/bson_java_2.result new file mode 100644 index 00000000000..1c21fc7c54f --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_java_2.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,Version=2' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":"2014-03-03T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-09-11T00:00:00.000Z"},"grade":"A","score":6},{"date":{"$date":"2013-01-24T00:00:00.000Z"},"grade":"A","score":10},{"date":{"$date":"2011-11-23T00:00:00.000Z"},"grade":"A","score":9},{"date":{"$date":"2011-03-10T00:00:00.000Z"},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":"2014-12-30T00:00:00.000Z"},"grade":"A","score":8},{"date":{"$date":"2014-07-01T00:00:00.000Z"},"grade":"B","score":23},{"date":{"$date":"2013-04-30T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2012-05-08T00:00:00.000Z"},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":"2014-09-06T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-07-22T00:00:00.000Z"},"grade":"A","score":11},{"date":{"$date":"2012-07-31T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2011-12-29T00:00:00.000Z"},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=Java,Version=2' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' `LRECL`=4096 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 2014-03-03T00:00:00.000Z A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 2014-12-30T00:00:00.000Z A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 2014-09-06T00:00:00.000Z A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 2014-06-10T00:00:00.000Z A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 2014-11-24T00:00:00.000Z Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=2,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 1970-01-01 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 1970-01-01 +Wendy'S Flatbush Avenue 8 1970-01-01 +Dj Reynolds Pub And Restaurant West 57 Street 2 1970-01-01 +Riviera Caterer Stillwell Avenue 5 1970-01-01 +Tov Kosher Kitchen 63 Road 20 1970-01-01 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 1970-01-01 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 1970-01-01 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 1970-01-01 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 1970-01-01 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 1970-01-01 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 1970-01-01 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 1970-01-01 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 1970-01-01 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 1970-01-01 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 1970-01-01 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 1970-01-01 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 1970-01-01 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 1970-01-01 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 1970-01-01 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 1970-01-01 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 1970-01-01 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 1970-01-01 01:33:34 B 15 +Tout Va Bien Manhattan 1970-01-01 01:33:34 A 13 +Tout Va Bien Manhattan 1970-01-01 01:33:33 C 36 +Tout Va Bien Manhattan 1970-01-01 01:33:33 B 22 +Tout Va Bien Manhattan 1970-01-01 01:33:32 C 36 +Tout Va Bien Manhattan 1970-01-01 01:33:32 C 7 +La Grenouille Manhattan 1970-01-01 01:33:34 A 10 +La Grenouille Manhattan 1970-01-01 01:33:33 A 9 +La Grenouille Manhattan 1970-01-01 01:33:32 A 13 +Le Perigord Manhattan 1970-01-01 01:33:34 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 1970-01-01 01:33:34 +Bistro Sk A 12 1970-01-01 01:33:34 +Bistro Sk B 18 1970-01-01 01:33:33 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"cuisine":0}' CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,level=2,version=2'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` char(24) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=2' `LRECL`=4096 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=Java,Version=2,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/r/bson_java_3.result b/storage/connect/mysql-test/connect/r/bson_java_3.result new file mode 100644 index 00000000000..d198ee3faa4 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_java_3.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,Version=3' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=Java,Version=3' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' `LRECL`=4096 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=3,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 2014-03-03 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 2014-03-03 +Wendy'S Flatbush Avenue 8 2014-12-30 +Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 +Riviera Caterer Stillwell Avenue 5 2014-06-10 +Tov Kosher Kitchen 63 Road 20 2014-11-24 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 +Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 +Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 +Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 +Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 +Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 +La Grenouille Manhattan 2014-04-09 02:00:00 A 10 +La Grenouille Manhattan 2013-03-05 01:00:00 A 9 +La Grenouille Manhattan 2012-02-02 01:00:00 A 13 +Le Perigord Manhattan 2014-07-14 02:00:00 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 2014-11-21 01:00:00 +Bistro Sk A 12 2014-02-19 01:00:00 +Bistro Sk B 18 2013-06-12 02:00:00 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"cuisine":0}' CONNECTION='mongodb://localhost:27017' LRECL=4096 +OPTION_LIST='Driver=Java,level=2,version=3'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=3' `LRECL`=4096 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=Java,Version=3,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=4096; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/r/bson_mongo_c.result b/storage/connect/mysql-test/connect/r/bson_mongo_c.result new file mode 100644 index 00000000000..e2273be4bec --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_mongo_c.result @@ -0,0 +1,385 @@ +set connect_enable_mongo=1; +set connect_json_all_path=0; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) JPATH='*') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=1024 +OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.8560769999999991,40.8484470000000002],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.9617039999999974,40.6629420000000010],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.9851355999999925,40.7676919000000026],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Depth=1,Driver=C,Version=0' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath +_id 1 CHAR 24 24 0 0 _id +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 1024 1024 0 1 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_date 1 CHAR 1024 1024 0 1 grades.0.date +grades_grade 1 CHAR 14 14 0 1 grades.0.grade +grades_score 7 INTEGER 2 2 0 1 grades.0.score +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +OPTION_LIST='Depth=1,Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' `LRECL`=1024 +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 -73.8560769999999991, 40.8484470000000002 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.9617039999999974, 40.6629420000000010 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.9851355999999925, 40.7676919000000026 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.9824199999999905, 40.5795049999999975 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601151999999956, 40.7311739000000017 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 (-73.8560769999999991, 40.8484470000000002) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.9617039999999974, 40.6629420000000010) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.9851355999999925, 40.7676919000000026) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.9824199999999905, 40.5795049999999975) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601151999999956, 40.7311739000000017) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803826999999984, 40.7643124000000014) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286000000026, 40.6119571999999991) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068505999999985, 40.6199033999999983) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.0052889999999906, 40.6288860000000014) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482608999999940, 40.6408271000000028) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 2014-03-03 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 2014-03-03 +Wendy'S Flatbush Avenue 8 2014-12-30 +Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 +Riviera Caterer Stillwell Avenue 5 2014-06-10 +Tov Kosher Kitchen 63 Road 20 2014-11-24 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 +Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 +Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 +Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 +Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 +Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 +La Grenouille Manhattan 2014-04-09 02:00:00 A 10 +La Grenouille Manhattan 2013-03-05 01:00:00 A 9 +La Grenouille Manhattan 2012-02-02 01:00:00 A 13 +Le Perigord Manhattan 2014-07-14 02:00:00 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 2014-11-21 01:00:00 +Bistro Sk A 12 2014-02-19 01:00:00 +Bistro Sk B 18 2013-06-12 02:00:00 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"projection":{"cuisine":0}}' CONNECTION='mongodb://localhost:27017' LRECL=1024 +OPTION_LIST='Driver=C,level=2,version=0'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(21,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='BSON' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' `LRECL`=1024 +SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=BSON TABNAME='cities' +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=BSON TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=YES' CONNECTION='mongodb://localhost:27017' LRECL=1024; +SELECT * FROM t1; +item total average +journal 285 57.00 +notebook 1368 456.00 +paper 23 5.75 +planner 167 41.75 +postcard 23 5.75 +DROP TABLE t1; +true +set connect_enable_mongo=0; diff --git a/storage/connect/mysql-test/connect/r/bson_udf.result b/storage/connect/mysql-test/connect/r/bson_udf.result new file mode 100644 index 00000000000..fef55f7d3d9 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/bson_udf.result @@ -0,0 +1,685 @@ +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=VIR BLOCK_SIZE=5; +# +# Test UDF's with constant arguments +# +SELECT BsonValue(56, 3.1416, 'foo', NULL); +ERROR HY000: Can't initialize function 'bsonvalue'; Cannot accept more than 1 argument +SELECT BsonValue(3.1416); +BsonValue(3.1416) +3.1416 +SELECT BsonValue(-80); +BsonValue(-80) +-80 +SELECT BsonValue('foo'); +BsonValue('foo') +foo +SELECT BsonValue(9223372036854775807); +BsonValue(9223372036854775807) +9223372036854775807 +SELECT BsonValue(NULL); +BsonValue(NULL) +null +SELECT BsonValue(TRUE); +BsonValue(TRUE) +true +SELECT BsonValue(FALSE); +BsonValue(FALSE) +false +SELECT BsonValue(); +BsonValue() +null +SELECT BsonValue('[11, 22, 33]' json_) FROM t1; +BsonValue('[11, 22, 33]' json_) +[11,22,33] +[11,22,33] +[11,22,33] +[11,22,33] +[11,22,33] +SELECT Bson_Make_Array(); +Bson_Make_Array() +[] +SELECT Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL); +Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL) +[56,3.1416,"My name is \"Foo\"",null] +SELECT Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE); +Bson_Make_Array(Bson_Make_Array(56, 3.1416, 'foo'), TRUE) +[[56,3.1416,"foo"],true] +SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL)) Array; +ERROR HY000: Can't initialize function 'bson_array_add'; This function must have at least 2 arguments +SELECT Bson_Array_Add(Bson_Make_Array(56, 3.1416, 'foo', NULL), 'One more') Array; +Array +[56,3.1416,"foo",null,"One more"] +SELECT Bson_Array_Add(BsonValue('one value'), 'One more'); +Bson_Array_Add(BsonValue('one value'), 'One more') +["one value","One more"] +SELECT Bson_Array_Add('one value', 'One more'); +Bson_Array_Add('one value', 'One more') +["one value","One more"] +SELECT Bson_Array_Add('one value' json_, 'One more'); +Bson_Array_Add('one value' json_, 'One more') +["one value","One more"] +SELECT Bson_Array_Add(5 json_, 'One more'); +Bson_Array_Add(5 json_, 'One more') +[5,"One more"] +SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0); +Bson_Array_Add('[5,3,8,7,9]' json_, 4, 0) +[4,5,3,8,7,9] +SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 2) Array; +Array +[5,3,4,8,7,9] +SELECT Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9); +Bson_Array_Add('[5,3,8,7,9]' json_, 4, 9) +[5,3,8,7,9,4] +SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1); +Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), '[2]', 33, 1) +[1,2,[11,22],"[2]"] +SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1); +Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, '[2]', 1) +[1,2,[11,33,22]] +SELECT Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]'); +Bson_Array_Add(Bson_Make_Array(1, 2, Bson_Make_Array(11, 22)), 33, 1, '[2]') +[1,2,[11,33,22]] +SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin', NULL), 'One more', 'Two more') Array; +Array +[56,3.1416,"machin",null,"One more","Two more"] +SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), 'One more', 'Two more') Array FROM t1; +Array +[56,3.1416,"machin","One more","Two more"] +[56,3.1416,"machin","One more","Two more"] +[56,3.1416,"machin","One more","Two more"] +[56,3.1416,"machin","One more","Two more"] +[56,3.1416,"machin","One more","Two more"] +SELECT Bson_Array_Add_Values(Bson_Make_Array(56, 3.1416, 'machin'), n) Array FROM t1; +Array +[56,3.1416,"machin",1] +[56,3.1416,"machin",2] +[56,3.1416,"machin",3] +[56,3.1416,"machin",4] +[56,3.1416,"machin",5] +SELECT Bson_Array_Add_Values(Bson_Make_Array(n, 3.1416, 'machin'), n) Array FROM t1; +Array +[1,3.1416,"machin",1] +[2,3.1416,"machin",2] +[3,3.1416,"machin",3] +[4,3.1416,"machin",4] +[5,3.1416,"machin",5] +SELECT Bson_Array_Add_Values('[56]', 3.1416, 'machin') Array; +Array +[56,3.1416,"machin"] +SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0); +Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), 0) +[3.1416,"My name is \"Foo\"",null] +SELECT Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2); +Bson_Array_Delete(Bson_Make_Object(56, 3.1416, 'My name is Foo', NULL), 2) +{"56":56,"3.1416":3.1416,"My name is Foo":"My name is Foo","NULL":null} +Warnings: +Warning 1105 First argument target is not an array +SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2'); +Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2') +[56,3.1416,"My name is \"Foo\"",null] +Warnings: +Warning 1105 Missing or null array index +SELECT Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2); +Bson_Array_Delete(Bson_Make_Array(56, 3.1416, 'My name is "Foo"', NULL), '2', 2) +[56,3.1416,"My name is \"Foo\"",null] +Warnings: +Warning 1105 First argument target is not an array +/* WARNING VOID */ +# +SELECT Bson_Make_Object(56, 3.1416, 'foo', NULL); +Bson_Make_Object(56, 3.1416, 'foo', NULL) +{"56":56,"3.1416":3.1416,"foo":"foo","NULL":null} +SELECT Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty); +Bson_Make_Object(56 qty, 3.1416 price, 'foo' truc, NULL garanty) +{"qty":56,"price":3.1416,"truc":"foo","garanty":null} +SELECT Bson_Make_Object(); +Bson_Make_Object() +{} +SELECT Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL); +Bson_Make_Object(Bson_Make_Array(56, 3.1416, 'foo'), NULL) +{"Make_Array(56, 3.1416, 'foo')":[56,3.1416,"foo"],"NULL":null} +SELECT Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL); +Bson_Make_Array(Bson_Make_Object(56 "qty", 3.1416 "price", 'foo') ,NULL) +[{"qty":56,"price":3.1416,"foo":"foo"},null] +SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL); +Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL) +{"qty":56,"price":3.1416,"truc":"machin","garanty":null} +SELECT Bson_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty'); +ERROR HY000: Can't initialize function 'bson_object_key'; This function must have an even number of arguments +SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color); +Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'blue' color) +{"qty":56,"price":3.1416,"truc":"machin","garanty":null,"color":"blue"} +SELECT Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price); +Bson_Object_Add(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 45.99 price) +{"qty":56,"price":45.99,"truc":"machin","garanty":null} +SELECT Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1); +Bson_Object_Add(Bson_File('notexist.json'), 'cheese' item, '[1]', 1) +NULL +Warnings: +Warning 1105 Error 2 opening notexist.json +Warning 1105 No sub-item at '[1]' +SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc'); +Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'truc') +{"qty":56,"price":3.1416,"garanty":null} +SELECT Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose'); +Bson_Object_Delete(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'chose') +{"qty":56,"price":3.1416,"truc":"machin","garanty":null} +SELECT Bson_Object_List(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty)) "Key List"; +Key List +["qty","price","truc","garanty"] +SELECT Bson_Object_List('{"qty":56, "price":3.1416, "truc":"machin", "garanty":null}') "Key List"; +Key List +["qty","price","truc","garanty"] +SELECT Bson_Object_Values('{"One":1,"Two":2,"Three":3}') "Value List"; +Value List +[1,2,3] +# +# Test UDF's with column arguments +# +SELECT Bsonset_Def_Prec(2); +Bsonset_Def_Prec(2) +2 +CREATE TABLE t2 +( +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=BSON FILE_NAME='biblio.json'; +SELECT Bson_Make_Array(AUTHOR, TITLE, DATEPUB) FROM t2; +Bson_Make_Array(AUTHOR, TITLE, DATEPUB) +[" Jean-Christophe Bernadac, François Knab","Construire une application XML",1999] +["William J. Pardi","XML en Action",1999] +SELECT Bson_Make_Object(AUTHOR, TITLE, DATEPUB) FROM t2; +Bson_Make_Object(AUTHOR, TITLE, DATEPUB) +{"AUTHOR":" Jean-Christophe Bernadac, François Knab","TITLE":"Construire une application XML","DATEPUB":1999} +{"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} +SELECT Bson_Array_Grp(TITLE, DATEPUB) FROM t2; +ERROR HY000: Can't initialize function 'bson_array_grp'; This function can only accept 1 argument +SELECT Bson_Array_Grp(TITLE) FROM t2; +Bson_Array_Grp(TITLE) +["Construire une application XML","XML en Action"] +CREATE TABLE t3 ( +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 Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY) FROM t3 WHERE NAME = 'MERCHANT'; +Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY) +{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.00} +SELECT DEPARTMENT, Bson_Array_Grp(NAME) FROM t3 GROUP BY DEPARTMENT; +DEPARTMENT Bson_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 +SELECT BsonSet_Grp_Size(30); +BsonSet_Grp_Size(30) +30 +SELECT Bson_Make_Object(title, Bson_Array_Grp(name) `json_names`) from t3 GROUP BY title; +Bson_Make_Object(title, Bson_Array_Grp(name) `json_names`) +{"title":"ADMINISTRATOR","names":["GOOSEPEN","FUNNIGUY","SHRINKY"]} +{"title":"DIRECTOR","names":["QUINN","WERTHER","STRONG"]} +{"title":"ENGINEER","names":["BROWNY","ORELLY","MARTIN","TONGHO","WALTER","SMITH"]} +{"title":"PROGRAMMER","names":["BUGHAPPY"]} +{"title":"SALESMAN","names":["WHEELFOR","MERCHANT","BULLOZER","BANCROFT","FODDERMAN"]} +{"title":"SCIENTIST","names":["BIGHEAD","BIGHORN"]} +{"title":"SECRETARY","names":["MESSIFUL","HONEY","SHORTSIGHT","CHERRY","MONAPENNY"]} +{"title":"TYPIST","names":["KITTY","PLUMHEAD"]} +SELECT Bson_Make_Array(DEPARTMENT, Bson_Array_Grp(NAME)) FROM t3 GROUP BY DEPARTMENT; +Bson_Make_Array(DEPARTMENT, Bson_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 Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(NAME) json_NAMES) FROM t3 GROUP BY DEPARTMENT; +Bson_Make_Object(DEPARTMENT, Bson_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 Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT; +Bson_Make_Object(DEPARTMENT, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, TITLE, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","TITLE":"DIRECTOR","SALARY":23000.00},{"SERIALNO":"22222","NAME":"SHORTSIGHT","TITLE":"SECRETARY","SALARY":5500.00}]} +{"DEPARTMENT":"0318","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","TITLE":"SALESMAN","SALARY":9600.00},{"SERIALNO":"24888","NAME":"PLUMHEAD","TITLE":"TYPIST","SALARY":2800.00},{"SERIALNO":"27845","NAME":"HONEY","TITLE":"SECRETARY","SALARY":4900.00},{"SERIALNO":"73452","NAME":"TONGHO","TITLE":"ENGINEER","SALARY":6800.00},{"SERIALNO":"74234","NAME":"WALTER","TITLE":"ENGINEER","SALARY":7400.00},{"SERIALNO":"77777","NAME":"SHRINKY","TITLE":"ADMINISTRATOR","SALARY":7500.00},{"SERIALNO":"70012","NAME":"WERTHER","TITLE":"DIRECTOR","SALARY":14500.00},{"SERIALNO":"78943","NAME":"MERCHANT","TITLE":"SALESMAN","SALARY":8700.00},{"SERIALNO":"73111","NAME":"WHEELFOR","TITLE":"SALESMAN","SALARY":10030.00}]} +{"DEPARTMENT":"0319","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","TITLE":"SALESMAN","SALARY":14800.00},{"SERIALNO":"40567","NAME":"QUINN","TITLE":"DIRECTOR","SALARY":14000.00},{"SERIALNO":"00137","NAME":"BROWNY","TITLE":"ENGINEER","SALARY":10500.00},{"SERIALNO":"12345","NAME":"KITTY","TITLE":"TYPIST","SALARY":3000.45},{"SERIALNO":"33333","NAME":"MONAPENNY","TITLE":"SECRETARY","SALARY":3800.00},{"SERIALNO":"00023","NAME":"MARTIN","TITLE":"ENGINEER","SALARY":10000.00},{"SERIALNO":"07654","NAME":"FUNNIGUY","TITLE":"ADMINISTRATOR","SALARY":8500.00},{"SERIALNO":"45678","NAME":"BUGHAPPY","TITLE":"PROGRAMMER","SALARY":8500.00},{"SERIALNO":"56789","NAME":"FODDERMAN","TITLE":"SALESMAN","SALARY":7000.00},{"SERIALNO":"55555","NAME":"MESSIFUL","TITLE":"SECRETARY","SALARY":5000.50},{"SERIALNO":"98765","NAME":"GOOSEPEN","TITLE":"ADMINISTRATOR","SALARY":4700.00}]} +{"DEPARTMENT":"2452","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","TITLE":"SCIENTIST","SALARY":8000.00},{"SERIALNO":"31416","NAME":"ORELLY","TITLE":"ENGINEER","SALARY":13400.00},{"SERIALNO":"36666","NAME":"BIGHORN","TITLE":"SCIENTIST","SALARY":11000.00},{"SERIALNO":"02345","NAME":"SMITH","TITLE":"ENGINEER","SALARY":9000.00},{"SERIALNO":"11111","NAME":"CHERRY","TITLE":"SECRETARY","SALARY":4500.00}]} +SELECT Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) FROM t3 GROUP BY DEPARTMENT, TITLE; +Bson_Make_Object(DEPARTMENT, TITLE, Bson_Array_Grp(Bson_Make_Object(SERIALNO, NAME, SALARY)) json_EMPLOYES) +{"DEPARTMENT":"0021","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"87777","NAME":"STRONG","SALARY":23000.00}]} +{"DEPARTMENT":"0021","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"22222","NAME":"SHORTSIGHT","SALARY":5500.00}]} +{"DEPARTMENT":"0318","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"77777","NAME":"SHRINKY","SALARY":7500.00}]} +{"DEPARTMENT":"0318","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"70012","NAME":"WERTHER","SALARY":14500.00}]} +{"DEPARTMENT":"0318","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"73452","NAME":"TONGHO","SALARY":6800.00},{"SERIALNO":"74234","NAME":"WALTER","SALARY":7400.00}]} +{"DEPARTMENT":"0318","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"74200","NAME":"BANCROFT","SALARY":9600.00},{"SERIALNO":"78943","NAME":"MERCHANT","SALARY":8700.00},{"SERIALNO":"73111","NAME":"WHEELFOR","SALARY":10030.00}]} +{"DEPARTMENT":"0318","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"27845","NAME":"HONEY","SALARY":4900.00}]} +{"DEPARTMENT":"0318","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"24888","NAME":"PLUMHEAD","SALARY":2800.00}]} +{"DEPARTMENT":"0319","TITLE":"ADMINISTRATOR","EMPLOYES":[{"SERIALNO":"98765","NAME":"GOOSEPEN","SALARY":4700.00},{"SERIALNO":"07654","NAME":"FUNNIGUY","SALARY":8500.00}]} +{"DEPARTMENT":"0319","TITLE":"DIRECTOR","EMPLOYES":[{"SERIALNO":"40567","NAME":"QUINN","SALARY":14000.00}]} +{"DEPARTMENT":"0319","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"00023","NAME":"MARTIN","SALARY":10000.00},{"SERIALNO":"00137","NAME":"BROWNY","SALARY":10500.00}]} +{"DEPARTMENT":"0319","TITLE":"PROGRAMMER","EMPLOYES":[{"SERIALNO":"45678","NAME":"BUGHAPPY","SALARY":8500.00}]} +{"DEPARTMENT":"0319","TITLE":"SALESMAN","EMPLOYES":[{"SERIALNO":"76543","NAME":"BULLOZER","SALARY":14800.00},{"SERIALNO":"56789","NAME":"FODDERMAN","SALARY":7000.00}]} +{"DEPARTMENT":"0319","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"33333","NAME":"MONAPENNY","SALARY":3800.00},{"SERIALNO":"55555","NAME":"MESSIFUL","SALARY":5000.50}]} +{"DEPARTMENT":"0319","TITLE":"TYPIST","EMPLOYES":[{"SERIALNO":"12345","NAME":"KITTY","SALARY":3000.45}]} +{"DEPARTMENT":"2452","TITLE":"ENGINEER","EMPLOYES":[{"SERIALNO":"31416","NAME":"ORELLY","SALARY":13400.00},{"SERIALNO":"02345","NAME":"SMITH","SALARY":9000.00}]} +{"DEPARTMENT":"2452","TITLE":"SCIENTIST","EMPLOYES":[{"SERIALNO":"34567","NAME":"BIGHEAD","SALARY":8000.00},{"SERIALNO":"36666","NAME":"BIGHORN","SALARY":11000.00}]} +{"DEPARTMENT":"2452","TITLE":"SECRETARY","EMPLOYES":[{"SERIALNO":"11111","NAME":"CHERRY","SALARY":4500.00}]} +SELECT Bson_Object_Grp(SALARY) FROM t3; +ERROR HY000: Can't initialize function 'bson_object_grp'; This function requires 2 arguments (key, value) +SELECT Bson_Object_Grp(NAME, SALARY) FROM t3; +Bson_Object_Grp(NAME, SALARY) +{"BANCROFT":9600.00,"SMITH":9000.00,"MERCHANT":8700.00,"FUNNIGUY":8500.00,"BUGHAPPY":8500.00,"BIGHEAD":8000.00,"SHRINKY":7500.00,"WALTER":7400.00,"FODDERMAN":7000.00,"TONGHO":6800.00,"SHORTSIGHT":5500.00,"MESSIFUL":5000.50,"HONEY":4900.00,"GOOSEPEN":4700.00,"CHERRY":4500.00,"MONAPENNY":3800.00,"KITTY":3000.45,"PLUMHEAD":2800.00,"STRONG":23000.00,"BULLOZER":14800.00,"WERTHER":14500.00,"QUINN":14000.00,"ORELLY":13400.00,"BIGHORN":11000.00,"BROWNY":10500.00,"WHEELFOR":10030.00,"MARTIN":10000.00} +SELECT Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES") FROM t3 GROUP BY DEPARTMENT; +Bson_Make_Object(DEPARTMENT, Bson_Object_Grp(NAME, SALARY) "Json_SALARIES") +{"DEPARTMENT":"0021","SALARIES":{"STRONG":23000.00,"SHORTSIGHT":5500.00}} +{"DEPARTMENT":"0318","SALARIES":{"BANCROFT":9600.00,"PLUMHEAD":2800.00,"HONEY":4900.00,"TONGHO":6800.00,"WALTER":7400.00,"SHRINKY":7500.00,"WERTHER":14500.00,"MERCHANT":8700.00,"WHEELFOR":10030.00}} +{"DEPARTMENT":"0319","SALARIES":{"BULLOZER":14800.00,"QUINN":14000.00,"BROWNY":10500.00,"KITTY":3000.45,"MONAPENNY":3800.00,"MARTIN":10000.00,"FUNNIGUY":8500.00,"BUGHAPPY":8500.00,"FODDERMAN":7000.00,"MESSIFUL":5000.50,"GOOSEPEN":4700.00}} +{"DEPARTMENT":"2452","SALARIES":{"BIGHEAD":8000.00,"ORELLY":13400.00,"BIGHORN":11000.00,"SMITH":9000.00,"CHERRY":4500.00}} +SELECT Bson_Array_Grp(NAME) FROM t3; +Bson_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"] +SELECT Bson_Object_Key(name, title) FROM t3 WHERE DEPARTMENT = 318; +Bson_Object_Key(name, title) +{"BANCROFT":"SALESMAN"} +{"MERCHANT":"SALESMAN"} +{"SHRINKY":"ADMINISTRATOR"} +{"WALTER":"ENGINEER"} +{"TONGHO":"ENGINEER"} +{"HONEY":"SECRETARY"} +{"PLUMHEAD":"TYPIST"} +{"WERTHER":"DIRECTOR"} +{"WHEELFOR":"SALESMAN"} +SELECT Bson_Object_Grp(name, title) FROM t3 WHERE DEPARTMENT = 318; +Bson_Object_Grp(name, title) +{"BANCROFT":"SALESMAN","MERCHANT":"SALESMAN","SHRINKY":"ADMINISTRATOR","WALTER":"ENGINEER","TONGHO":"ENGINEER","HONEY":"SECRETARY","PLUMHEAD":"TYPIST","WERTHER":"DIRECTOR","WHEELFOR":"SALESMAN"} +# +# Test value getting UDF's +# +SELECT BsonGet_String(Bson_Array_Grp(name),'[#]') FROM t3; +BsonGet_String(Bson_Array_Grp(name),'[#]') +27 +SELECT BsonGet_String(Bson_Array_Grp(name),'[","]') FROM t3; +BsonGet_String(Bson_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 +SELECT BsonGet_String(Bson_Array_Grp(name),'[>]') FROM t3; +BsonGet_String(Bson_Array_Grp(name),'[>]') +WHEELFOR +SET @j1 = '[45,28,36,45,89]'; +SELECT BsonGet_String(@j1,'1'); +BsonGet_String(@j1,'1') +28 +SELECT BsonGet_String(@j1 json_,'3'); +BsonGet_String(@j1 json_,'3') +45 +SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3'); +BsonGet_String(Bson_Make_Array(45,28,36,45,89),'3') +45 +SELECT BsonGet_String(Bson_Make_Array(45,28,36,45,89),'["+"]') "list",'=' as "egal",BsonGet_String(Bson_Make_Array(45,28,36,45,89),'[+]') "sum"; +list egal sum +45+28+36+45+89 = 243 +SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0'); +BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.0') +36 +SELECT BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*'); +BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)),'1.*') +[36,45,89] +SELECT BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc'); +BsonGet_String(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'truc') +machin +SET @j2 = '{"qty":56,"price":3.141600,"truc":"machin","garanty":null}'; +SELECT BsonGet_String(@j2 json_,'truc'); +BsonGet_String(@j2 json_,'truc') +machin +SELECT BsonGet_String(@j2,'truc'); +BsonGet_String(@j2,'truc') +machin +SELECT BsonGet_String(@j2,'chose'); +BsonGet_String(@j2,'chose') +NULL +SELECT BsonGet_String(NULL json_, NULL); +BsonGet_String(NULL json_, NULL) +NULL +Warnings: +Warning 1105 +/* NULL WARNING */ +SELECT department, BsonGet_String(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department; +department Sumsal +0021 28500.00 +0318 72230.00 +0319 89800.95 +2452 45900.00 +SELECT BsonGet_Int(@j1, '4'); +BsonGet_Int(@j1, '4') +89 +SELECT BsonGet_Int(@j1, '[#]'); +BsonGet_Int(@j1, '[#]') +5 +SELECT BsonGet_Int(@j1, '[+]'); +BsonGet_Int(@j1, '[+]') +243 +SELECT BsonGet_Int(@j1 json_, '3'); +BsonGet_Int(@j1 json_, '3') +45 +SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3'); +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '3') +45 +SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]'); +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '["+"]') +45 +SELECT BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]'); +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]') +243 +SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0'); +BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0') +36 +SELECT BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1'); +BsonGet_Int(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '0.1') +28 +SELECT BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty'); +BsonGet_Int(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'qty') +56 +SELECT BsonGet_Int(@j2 json_, 'price'); +BsonGet_Int(@j2 json_, 'price') +3 +SELECT BsonGet_Int(@j2, 'qty'); +BsonGet_Int(@j2, 'qty') +56 +SELECT BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); +BsonGet_Int('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') +NULL +SELECT BsonGet_Int(BsonGet_String(Bson_Make_Array(Bson_Make_Array(45,28),Bson_Make_Array(36,45,89)), '1.*'), '[+]') sum; +sum +170 +SELECT department, BsonGet_Int(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"), 'salaries.[+]') Sumsal FROM t3 GROUP BY department; +department Sumsal +0021 28500 +0318 72230 +0319 89800 +2452 45900 +SELECT BsonGet_Real(@j1, '2'); +BsonGet_Real(@j1, '2') +36.000000000000000 +SELECT BsonGet_Real(@j1 json_, '3', 2); +BsonGet_Real(@j1 json_, '3', 2) +45.00 +SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3'); +BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '3') +45.000000000000000 +SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]'); +BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '["+"]') +45.000000000000000 +SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]'); +BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[+]') +243.000000000000000 +SELECT BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]'); +BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]') +48.600000000000000 +SELECT BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0'); +BsonGet_Real(Bson_Make_Array(Bson_Make_Array(45,28), Bson_Make_Array(36,45,89)), '1.0') +36.000000000000000 +SELECT BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price'); +BsonGet_Real(Bson_Make_Object(56 qty, 3.1416 price, 'machin' truc, NULL garanty), 'price') +3.141600000000000 +SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty'); +BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}' json_, 'qty') +56.000000000000000 +SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price'); +BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price') +3.141600000000000 +SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4); +BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'price', 4) +3.1416 +SELECT BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose'); +BsonGet_Real('{"qty":56,"price":3.141600,"truc":"machin","garanty":null}', 'chose') +NULL +SELECT department, BsonGet_Real(Bson_Make_Object(department, Bson_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department; +department Sumsal +0021 28500.000000000000000 +0318 72230.000000000000000 +0319 89800.950000000000000 +2452 45900.000000000000000 +# +# Documentation examples +# +SELECT +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '4') "Rank", +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[#]') "Number", +BsonGet_String(Bson_Make_Array(45,28,36,45,89), '[","]') "Concat", +BsonGet_Int(Bson_Make_Array(45,28,36,45,89), '[+]') "Sum", +BsonGet_Real(Bson_Make_Array(45,28,36,45,89), '[!]', 2) "Avg"; +Rank Number Concat Sum Avg +89 5 45,28,36,45,89 243 48.60 +SELECT +BsonGet_String('{"qty":7,"price":29.50,"garanty":null}', 'price') "String", +BsonGet_Int('{"qty":7,"price":29.50,"garanty":null}', 'price') "Int", +BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price') "Real"; +String Int Real +29.50 29 29.500000000000000 +SELECT BsonGet_Real('{"qty":7,"price":29.50,"garanty":null}', 'price', 3) "Real"; +Real +29.500 +# +# Testing Locate +# +SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin'); +BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'machin') +$.truc +SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56); +BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),56) +$.qty +SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416); +BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),3.1416) +$.price +SELECT BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose'); +BsonLocate(Bson_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty),'chose') +NULL +SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'Jack') Path; +Path +$.AUTHORS[1].FN +SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, 'jack' ci) Path; +Path +$.AUTHORS[1].FN +SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"Jack", "LN":"London"}' json_) Path; +Path +$.AUTHORS[1] +SELECT BsonLocate('{"AUTHORS":[{"FN":"Jules", "LN":"Verne"}, {"FN":"Jack", "LN":"London"}]}' json_, '{"FN":"jack", "LN":"London"}' json_) Path; +Path +NULL +SELECT BsonLocate('[45,28,36,45,89]',36); +BsonLocate('[45,28,36,45,89]',36) +$[2] +SELECT BsonLocate('[45,28,36,45,89]' json_,28.0); +BsonLocate('[45,28,36,45,89]' json_,28.0) +NULL +SELECT Bson_Locate_All('[45,28,36,45,89]',10); +Bson_Locate_All('[45,28,36,45,89]',10) +[] +SELECT Bson_Locate_All('[45,28,36,45,89]',45); +Bson_Locate_All('[45,28,36,45,89]',45) +["$[0]","$[3]"] +SELECT Bson_Locate_All('[[45,28],36,45,89]',45); +Bson_Locate_All('[[45,28],36,45,89]',45) +["$[0][0]","$[2]"] +SELECT Bson_Locate_All('[[45,28,45],36,45,89]',45); +Bson_Locate_All('[[45,28,45],36,45,89]',45) +["$[0][0]","$[0][2]","$[2]"] +SELECT Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]')); +Bson_Locate_All('[[45,28,45],36,45,89]',BsonGet_Int('[3,45]','[1]')) +["$[0][0]","$[0][2]","$[2]"] +SELECT BsonLocate('[[45,28,45],36,45,89]',45,n) from t1; +BsonLocate('[[45,28,45],36,45,89]',45,n) +$[0][0] +$[0][2] +$[2] +NULL +NULL +SELECT BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) FROM t1; +BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) +$[0][0] +$[0][2] +$[2] +NULL +NULL +SELECT BsonGet_String(Bson_Locate_All('[[45,28,45],36,45,89]',45),concat('[',n-1,']')) AS `Path` FROM t1 GROUP BY n HAVING `Path` IS NOT NULL; +Path +$[0][0] +$[0][2] +$[2] +SELECT Bson_Locate_All('[45,28,[36,45,89]]',45); +Bson_Locate_All('[45,28,[36,45,89]]',45) +["$[0]","$[2][1]"] +SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0)); +Bson_Locate_All('[[45,28],[36,45.0,89]]',BsonValue(45.0)) +[] +SELECT Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0); +Bson_Locate_All('[[45,28],[36,45.0,89]]',45.0) +["$[1][1]"] +SELECT BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_); +BsonLocate('[[45,28],[36,45,89]]','[36,45,89]' json_) +$[1] +SELECT BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_); +BsonLocate('[[45,28],[36,45,89]]','[45,28]' json_) +$[0] +SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','45') "All paths"; +All paths +[] +SELECT Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_); +Bson_Locate_All('[[45,28],[[36,45],89]]','[36,45]' json_) +["$[1][0]"] +SELECT BsonGet_Int(Bson_Locate_All('[[45,28],[[36,45],89]]',45), '[#]') "Nb of occurs"; +Nb of occurs +2 +SELECT Bson_Locate_All('[[45,28],[[36,45],89]]',45,2); +Bson_Locate_All('[[45,28],[[36,45],89]]',45,2) +["$[0][0]"] +SELECT BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0'); +BsonGet_String(Bson_Locate_All('[45,28,36,45,89]',45),'0') +$[0] +SELECT BsonLocate(Bson_File('test/biblio.json'), 'Knab'); +BsonLocate(Bson_File('test/biblio.json'), 'Knab') +$[0].AUTHOR[1].LASTNAME +SELECT Bson_Locate_All('test/biblio.json' jfile_, 'Knab'); +Bson_Locate_All('test/biblio.json' jfile_, 'Knab') +["$[0].AUTHOR[1].LASTNAME"] +# +# Testing json files +# +SELECT Bfile_Make('[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]}, +{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]}, +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]}, +{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}]', 'test/fx.json', 0) AS NewFile; +NewFile +test/fx.json +SELECT Bfile_Make('test/fx.json', 1); +Bfile_Make('test/fx.json', 1) +test/fx.json +SELECT Bfile_Make('test/fx.json' jfile_); +Bfile_Make('test/fx.json' jfile_) +test/fx.json +SELECT Bfile_Make(Bbin_File('test/fx.json'), 0); +Bfile_Make(Bbin_File('test/fx.json'), 0) +test/fx.json +SELECT Bson_File('test/fx.json', 1); +Bson_File('test/fx.json', 1) +[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]},{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]},{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]},{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}] +Warnings: +Warning 1105 File pretty format doesn't match the specified pretty value +SELECT Bson_File('test/fx.json', 2); +Bson_File('test/fx.json', 2) +[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]},{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]},{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]},{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}] +Warnings: +Warning 1105 File pretty format doesn't match the specified pretty value +SELECT Bson_File('test/fx.json', 0); +Bson_File('test/fx.json', 0) +[{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]},{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]},{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]},{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":40},"ratings":[5,8,7]}] +SELECT Bson_File('test/fx.json', '0'); +Bson_File('test/fx.json', '0') +{"_id":5,"type":"food","item":"beer","taste":"light","price":5.65,"ratings":[5,8,9]} +SELECT Bson_File('test/fx.json', '[?]'); +Bson_File('test/fx.json', '[?]') +NULL +Warnings: +Warning 1105 Invalid function specification ? +SELECT BsonGet_String(Bson_File('test/fx.json'), '1.*'); +BsonGet_String(Bson_File('test/fx.json'), '1.*') +{"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]} +SELECT BsonGet_String(Bson_File('test/fx.json'), '1'); +BsonGet_String(Bson_File('test/fx.json'), '1') +6 car roadster 56000 (6, 9) +SELECT BsonGet_Int(Bson_File('test/fx.json'), '1.mileage') AS Mileage; +Mileage +56000 +SELECT BsonGet_Real(Bson_File('test/fx.json'), '0.price', 2) AS Price; +Price +5.65 +SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings'); +Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings') +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4,6]} +SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings'); +Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 1, 'ratings') +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]} +SELECT Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1); +Bson_Array_Add(Bson_File('test/fx.json', '2'), 6, 'ratings', 1) +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,6,4]} +SELECT Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0); +Bson_Array_Add(Bson_File('test/fx.json', '2.ratings'), 6, 0) +[6,2,4] +SELECT Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1); +Bson_Array_Delete(Bson_File('test/fx.json', '2'), 'ratings', 1) +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2]} +SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin); +Bson_Object_Add(Bson_File('test/fx.json', '2'), 'france' origin) +{"_id":7,"type":"food","item":"meat","origin":"france","ratings":[2,4]} +SELECT Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size'); +Bson_Object_Add(Bson_File('test/fx.json', '2'), 70 H, 'size') +{"_id":7,"type":"food","item":"meat","origin":"argentina","ratings":[2,4]} +Warnings: +Warning 1105 No sub-item at 'size' +SELECT Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size'); +Bson_Object_Add(Bson_File('test/fx.json', '3'), 70 H, 'size') +{"_id":8,"type":"furniture","item":"table","size":{"W":60,"L":80,"H":70},"ratings":[5,8,7]} +SELECT Bson_Object_List(Bson_File('test/fx.json', '3.size')); +Bson_Object_List(Bson_File('test/fx.json', '3.size')) +["W","L","H"] +# +# Testing new functions +# +SELECT Bson_Item_Merge('["a","b","c"]','["d","e","f"]') as "Result"; +Result +["a","b","c","d","e","f"] +SELECT Bson_Item_Merge(Bson_Make_Array('a','b','c'), Bson_Make_Array('d','e','f')) as "Result"; +Result +["a","b","c","d","e","f"] +SELECT +Bson_Set_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Set", +Bson_Insert_Item('[1,2,3,{"quatre":4}]', 'foo', '$[1]', 5, '$[3].cinq') as "Insert", +Bson_Update_Item(Bson_Make_Array(1,2,3,Bson_Object_Key('quatre',4)),'foo','$[1]',5,'$[3].cinq') "Update"; +Set Insert Update +[1,"foo",3,{"quatre":4,"cinq":5}] [1,2,3,{"quatre":4,"cinq":5}] [1,"foo",3,{"quatre":4}] +SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux'); +bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','1','[2].Deux') +[1,3,{"quatre":4}] +SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]'); +bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','["[1]","[3].Deux"]') +[1,3,{"quatre":4}] +SELECT bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux'); +bson_delete_item('[1,2,3,{"quatre":4,"Deux":2}]','$.[3].Deux') +[1,2,3,{"quatre":4}] +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +SELECT BsonSet_Grp_Size(10); +BsonSet_Grp_Size(10) +10 diff --git a/storage/connect/mysql-test/connect/r/jdbc.result b/storage/connect/mysql-test/connect/r/jdbc.result index 0dbdf851860..b42311b8136 100644 --- a/storage/connect/mysql-test/connect/r/jdbc.result +++ b/storage/connect/mysql-test/connect/r/jdbc.result @@ -1,4 +1,5 @@ -SET GLOBAL time_zone='+1:00'; +SET GLOBAL time_zone='+0:00'; +SET time_zone='+0:00'; CREATE DATABASE connect; USE connect; CREATE TABLE t2 ( @@ -16,7 +17,7 @@ id msg tm dt dtm ts # Testing JDBC connection to MySQL driver # USE test; -CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=t2 CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root'; +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=t2 CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root&useSSL=false'; SELECT * FROM t1; id msg tm dt dtm ts 455000000000 A very big number 18:10:25 2016-03-16 1999-12-11 23:01:52 2015-07-24 09:32:45 @@ -26,7 +27,7 @@ Note 1105 t2: 1 affected rows SELECT * FROM t1; id msg tm dt dtm ts 455000000000 A very big number 18:10:25 2016-03-16 1999-12-11 23:01:52 2015-07-24 09:32:45 -786325481247 Hello! 19:45:03 1933-08-09 1985-11-12 09:02:44 2014-06-17 10:32:01 +786325481247 Hello! 19:45:03 1933-08-10 1985-11-12 09:02:44 2014-06-17 10:32:01 DELETE FROM t1 WHERE msg = 'Hello!'; Warnings: Note 1105 t2: 1 affected rows @@ -37,7 +38,7 @@ DROP TABLE t1; # # Testing JDBC view # -CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC SRCDEF='select id, msg, tm, dt from t2' CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root'; +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC SRCDEF='select id, msg, tm, dt from t2' CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root&useSSL=false'; SELECT * FROM t1; id msg tm dt 455000000000 A very big number 18:10:25 2016-03-16 @@ -74,7 +75,7 @@ SELECT * FROM t3; name city birth hired Donald Atlanta 1999-04-01 2016-03-31 Mick New York 1980-01-20 2002-09-11 -CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=boys CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root' OPTION_LIST='scrollable=1'; +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=boys CONNECTION='jdbc:mysql://localhost:PORT/connect?user=root&useSSL=false' OPTION_LIST='scrollable=1'; SELECT * FROM t1; name city birth hired John Boston 1986-01-25 2010-06-02 @@ -100,9 +101,9 @@ George San Jose 1981-08-10 2010-06-02 Sam Chicago 1979-11-22 2007-10-10 James Dallas 1992-05-13 2009-12-14 Bill Boston 1986-09-11 2008-02-10 -Donald Atlanta 1999-03-31 2016-03-30 -Mick New York 1980-01-20 2002-09-10 -Tom Seatle 2002-03-15 1970-01-01 +Donald Atlanta 1999-04-01 2016-03-31 +Mick New York 1980-01-20 2002-09-11 +Tom Seatle 2002-03-15 NULL DROP TABLE t3; # # Testing JDBC join operations @@ -280,3 +281,4 @@ DROP TABLE t1; DROP TABLE connect.tx1; DROP DATABASE connect; SET GLOBAL time_zone=SYSTEM; +SET time_zone=SYSTEM; diff --git a/storage/connect/mysql-test/connect/r/jdbc_new.result b/storage/connect/mysql-test/connect/r/jdbc_new.result index 33d8bd3b7d8..a47fb9ccaf9 100644 --- a/storage/connect/mysql-test/connect/r/jdbc_new.result +++ b/storage/connect/mysql-test/connect/r/jdbc_new.result @@ -2,7 +2,8 @@ connect master,127.0.0.1,root,,test,$MASTER_MYPORT,; connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; connection master; connection slave; -SET GLOBAL time_zone='+1:00'; +SET GLOBAL time_zone='+0:00'; +SET time_zone='+0:00'; CREATE TABLE t1 (a int, b char(10)); INSERT INTO t1 VALUES (NULL,NULL),(0,'test00'),(1,'test01'),(2,'test02'),(3,'test03'); SELECT * FROM t1; @@ -16,31 +17,32 @@ NULL NULL # Testing errors # connection master; -SET GLOBAL time_zone='+1:00'; +SET GLOBAL time_zone='+0:00'; +SET time_zone='+0:00'; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=unknown'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=unknown&useSSL=false'; ERROR HY000: Connecting: java.sql.SQLException: Access denied for user 'unknown'@'localhost' (using password: NO) rc=-2 CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/unknown?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/unknown?user=root&useSSL=false'; ERROR HY000: Connecting: java.sql.SQLSyntaxErrorException: Unknown database 'unknown' rc=-2 CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='unknown' - CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; + CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; ERROR HY000: Cannot get columns from unknown SHOW CREATE TABLE t1; ERROR 42S02: Table 'test.t1' doesn't exist CREATE TABLE t1 (x int, y char(10)) ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `x` int(11) DEFAULT NULL, `y` char(10) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`=JDBC +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`=JDBC SELECT * FROM t1; ERROR HY000: Got error 174 'ExecuteQuery: java.sql.SQLSyntaxErrorException: Unknown column 'x' in 'field list'' from CONNECT DROP TABLE t1; CREATE TABLE t1 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; connection slave; ALTER TABLE t1 RENAME t1backup; connection master; @@ -54,13 +56,13 @@ DROP TABLE t1; # Testing SELECT, etc. # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(10) DEFAULT NULL, `b` char(10) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`='JDBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`='JDBC' SELECT * FROM t1; a b NULL NULL @@ -70,13 +72,13 @@ NULL NULL 3 test03 DROP TABLE t1; CREATE TABLE t1 (a int, b char(10)) ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='t1' - CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; + CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` char(10) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`=JDBC `TABNAME`='t1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`=JDBC `TABNAME`='t1' SELECT * FROM t1; a b NULL NULL @@ -86,13 +88,13 @@ NULL NULL 3 test03 DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL, b CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` char(10) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`=JDBC +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`=JDBC SELECT * FROM t1; a b 0 @@ -102,13 +104,13 @@ a b 3 test03 DROP TABLE t1; CREATE TABLE t1 (a char(10), b int) ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(10) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`=JDBC +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`=JDBC SELECT * FROM t1; a b NULL NULL @@ -138,7 +140,7 @@ t1 CREATE TABLE `t1` ( INSERT INTO t1 VALUES(100,3333,41235,1234567890,235000000000,3.14159265,3.14159265,3141.59265); connection master; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -150,7 +152,7 @@ t1 CREATE TABLE `t1` ( `f` double(14,0) DEFAULT NULL, `g` double(24,0) DEFAULT NULL, `h` decimal(27,5) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`='JDBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`='JDBC' SELECT * FROM t1; a b c d e f g h 100 3333 41235 1234567890 235000000000 3 3 3141.59265 @@ -173,13 +175,13 @@ a b Welcome Hello, World connection master; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(12) DEFAULT NULL, `b` varchar(12) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`='JDBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`='JDBC' SELECT * FROM t1; a b Welcome Hello, World @@ -209,7 +211,7 @@ a b c d e 2003-05-27 2003-05-27 10:45:23 10:45:23 2003-05-27 10:45:23 2003 connection master; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC -CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root'; +CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -218,13 +220,15 @@ t1 CREATE TABLE `t1` ( `c` time DEFAULT NULL, `d` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `e` year(4) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root' `TABLE_TYPE`='JDBC' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='jdbc:mysql://127.0.0.1:SLAVE_PORT/test?user=root&useSSL=false' `TABLE_TYPE`='JDBC' SELECT * FROM t1; a b c d e -2003-05-27 2003-05-27 11:45:23 10:45:23 2003-05-27 10:45:23 2003 +2003-05-27 2003-05-27 10:45:23 10:45:23 2003-05-27 10:45:23 2003 DROP TABLE t1; connection slave; DROP TABLE t1; SET GLOBAL time_zone=SYSTEM; +SET time_zone=SYSTEM; connection master; SET GLOBAL time_zone=SYSTEM; +SET time_zone=SYSTEM; diff --git a/storage/connect/mysql-test/connect/r/jdbc_oracle.result b/storage/connect/mysql-test/connect/r/jdbc_oracle.result index ec314c5f072..21a2d10fef7 100644 --- a/storage/connect/mysql-test/connect/r/jdbc_oracle.result +++ b/storage/connect/mysql-test/connect/r/jdbc_oracle.result @@ -3,7 +3,7 @@ command varchar(128) not null, number int(5) not null flag=1, message varchar(255) flag=2) ENGINE=CONNECT TABLE_TYPE=JDBC CONNECTION='jdbc:oracle:thin:@localhost:1521:xe' -OPTION_LIST='User=system,Password=manager,Execsrc=1'; +OPTION_LIST='User=system,Password=Biscote01,Execsrc=1'; SELECT * FROM t2 WHERE command = 'drop table employee'; command number message drop table employee 0 Execute: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist @@ -23,14 +23,14 @@ Warnings: Warning 1105 Affected rows CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC CATFUNC=tables CONNECTION='jdbc:oracle:thin:@localhost:1521:xe' -OPTION_LIST='User=system,Password=manager'; +OPTION_LIST='User=system,Password=Biscote01'; SELECT * FROM t1 WHERE table_name='employee'; Table_Cat Table_Schema Table_Name Table_Type Remark NULL SYSTEM EMPLOYEE TABLE NULL DROP TABLE t1; CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='EMPLOYEE' CATFUNC=columns CONNECTION='jdbc:oracle:thin:@localhost:1521:xe' -OPTION_LIST='User=system,Password=manager'; +OPTION_LIST='User=system,Password=Biscote01'; SELECT * FROM t1; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks NULL SYSTEM EMPLOYEE ID 3 NUMBER 38 0 0 10 0 NULL @@ -42,7 +42,7 @@ CREATE SERVER 'oracle' FOREIGN DATA WRAPPER 'oracle.jdbc.driver.OracleDriver' OP HOST 'jdbc:oracle:thin:@localhost:1521:xe', DATABASE 'SYSTEM', USER 'system', -PASSWORD 'manager', +PASSWORD 'Biscote01', PORT 0, SOCKET '', OWNER 'SYSTEM'); diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result index 6b6f40d2c47..dc527acd4a3 100644 --- a/storage/connect/mysql-test/connect/r/json.result +++ b/storage/connect/mysql-test/connect/r/json.result @@ -15,7 +15,7 @@ DATEPUB int(4) ) 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 NULL NULL Eyrolles Paris 1999 +9782212090819 fr applications Jean-Christophe Bernadac, François Knab Construire une application XML NULL NULL Eyrolles Paris 1999 9782840825685 fr applications William J. Pardi XML en Action adapté de l'anglais par James Guerin Microsoft Press Paris 1999 DROP TABLE t1; # @@ -24,15 +24,15 @@ DROP TABLE t1; 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' +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +Authors INT(2) JPATH='$.AUTHOR[#]', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; @@ -46,16 +46,16 @@ DROP TABLE t1; 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' +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[" and "].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[" and "].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; @@ -69,16 +69,16 @@ DROP TABLE t1; CREATE TABLE t1 ( ISBN CHAR(15), -Language CHAR(2) FIELD_FORMAT='$.LANG', -Subject CHAR(32) FIELD_FORMAT='$.SUBJECT', -AuthorFN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].FIRSTNAME', -AuthorLN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].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' +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATION', +Translator CHAR(80) JPATH='$.TRANSLATOR', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB' ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT * FROM t1; @@ -176,17 +176,17 @@ DROP TABLE t1; CREATE TABLE t1 ( ISBN CHAR(15) NOT NULL, -Language CHAR(2) FIELD_FORMAT='$.LANG', -Subject CHAR(32) FIELD_FORMAT='$.SUBJECT', -AuthorFN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].FIRSTNAME', -AuthorLN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].LASTNAME', -Title CHAR(32) FIELD_FORMAT='$.TITLE', -Translation CHAR(32) FIELD_FORMAT='$.TRANSLATED.PREFIX', -TranslatorFN CHAR(80) FIELD_FORMAT='$.TRANSLATED.TRANSLATOR.FIRSTNAME', -TranslatorLN CHAR(80) FIELD_FORMAT='$.TRANSLATED.TRANSLATOR.LASTNAME', -Publisher CHAR(20) FIELD_FORMAT='$.PUBLISHER.NAME', -Location CHAR(16) FIELD_FORMAT='$.PUBLISHER.PLACE', -Year int(4) FIELD_FORMAT='$.DATEPUB', +Language CHAR(2) JPATH='$.LANG', +Subject CHAR(32) JPATH='$.SUBJECT', +AuthorFN CHAR(128) JPATH='$.AUTHOR[*].FIRSTNAME', +AuthorLN CHAR(128) JPATH='$.AUTHOR[*].LASTNAME', +Title CHAR(32) JPATH='$.TITLE', +Translation CHAR(32) JPATH='$.TRANSLATED.PREFIX', +TranslatorFN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.FIRSTNAME', +TranslatorLN CHAR(80) JPATH='$.TRANSLATED.TRANSLATOR.LASTNAME', +Publisher CHAR(20) JPATH='$.PUBLISHER.NAME', +Location CHAR(16) JPATH='$.PUBLISHER.PLACE', +Year int(4) JPATH='$.DATEPUB', INDEX IX(ISBN) ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bib0.json' LRECL=320 OPTION_LIST='Pretty=0'; @@ -209,9 +209,9 @@ DROP TABLE t1; # CREATE TABLE t1 ( WHO CHAR(12), -WEEK INT(2) FIELD_FORMAT='$.WEEK[*].NUMBER', -WHAT CHAR(32) FIELD_FORMAT='$.WEEK[].EXPENSE["+"].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[].EXPENSE[+].AMOUNT') +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[].EXPENSE["+"].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[].EXPENSE[+].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT @@ -230,9 +230,9 @@ DROP TABLE t1; # CREATE TABLE t1 ( WHO CHAR(12), -WEEK INT(2) FIELD_FORMAT='$.WEEK[*].NUMBER', -WHAT CHAR(32) FIELD_FORMAT='$.WEEK[*].EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[*].EXPENSE[*].AMOUNT') +WEEK INT(2) JPATH='$.WEEK[*].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[*].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[*].EXPENSE[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEK WHAT AMOUNT @@ -266,14 +266,14 @@ DROP TABLE t1; # 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[*].AMOUNT') +WEEKS CHAR(12) NOT NULL JPATH='$.WEEK[", "].NUMBER', +SUMS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[+].AMOUNT', +SUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[+].AMOUNT', +AVGS CHAR(64) NOT NULL JPATH='$.WEEK["+"].EXPENSE[!].AMOUNT', +SUMAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[+].EXPENSE[!].AMOUNT', +AVGSUM DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[+].AMOUNT', +AVGAVG DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[!].AMOUNT', +AVERAGE DOUBLE(8,2) NOT NULL JPATH='$.WEEK[!].EXPENSE[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t1; WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVGAVG AVERAGE @@ -286,9 +286,9 @@ DROP TABLE t1; # CREATE TABLE t2 ( WHO CHAR(12), -WEEK INT(2) FIELD_FORMAT='$.WEEK[0].NUMBER', -WHAT CHAR(32) FIELD_FORMAT='$.WEEK[0].EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[0].EXPENSE[*].AMOUNT') +WEEK INT(2) JPATH='$.WEEK[0].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[0].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[0].EXPENSE[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT @@ -302,9 +302,9 @@ Janet 3 Food 18.00 Janet 3 Beer 18.00 CREATE TABLE t3 ( WHO CHAR(12), -WEEK INT(2) FIELD_FORMAT='$.WEEK[1].NUMBER', -WHAT CHAR(32) FIELD_FORMAT='$.WEEK[1].EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[1].EXPENSE[*].AMOUNT') +WEEK INT(2) JPATH='$.WEEK[1].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[1].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[1].EXPENSE[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT @@ -318,9 +318,9 @@ Beth 4 Beer 15.00 Janet 4 Car 17.00 CREATE TABLE t4 ( WHO CHAR(12), -WEEK INT(2) FIELD_FORMAT='$.WEEK[2].NUMBER', -WHAT CHAR(32) FIELD_FORMAT='$.WEEK[2].EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[2].EXPENSE[*].AMOUNT') +WEEK INT(2) JPATH='$.WEEK[2].NUMBER', +WHAT CHAR(32) JPATH='$.WEEK[2].EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.WEEK[2].EXPENSE[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT @@ -374,8 +374,8 @@ DROP TABLE t1, t2, t3, t4; CREATE TABLE t2 ( WHO CHAR(12), WEEK INT(2), -WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT') +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json'; SELECT * FROM t2; WHO WEEK WHAT AMOUNT @@ -390,8 +390,8 @@ Janet 3 Beer 18.00 CREATE TABLE t3 ( WHO CHAR(12), WEEK INT(2), -WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT') +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json'; SELECT * FROM t3; WHO WEEK WHAT AMOUNT @@ -406,8 +406,8 @@ Janet 4 Car 17.00 CREATE TABLE t4 ( WHO CHAR(12), WEEK INT(2), -WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT') +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json'; SELECT * FROM t4; WHO WEEK WHAT AMOUNT @@ -425,8 +425,8 @@ Janet 5 Food 12.00 CREATE TABLE t1 ( WHO CHAR(12), WEEK INT(2), -WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT') +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1; SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT; WHO WEEK WHAT AMOUNT @@ -461,8 +461,8 @@ DROP TABLE t1; CREATE TABLE t1 ( WHO CHAR(12), WEEK INT(2), -WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT', -AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT') +WHAT CHAR(32) JPATH='$.EXPENSE[*].WHAT', +AMOUNT DOUBLE(8,2) JPATH='$.EXPENSE.[*].AMOUNT') ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json'; ALTER TABLE t1 PARTITION BY LIST COLUMNS(WEEK) ( diff --git a/storage/connect/mysql-test/connect/r/json_java_2.result b/storage/connect/mysql-test/connect/r/json_java_2.result index 4bbac236200..c05501013a5 100644 --- a/storage/connect/mysql-test/connect/r/json_java_2.result +++ b/storage/connect/mysql-test/connect/r/json_java_2.result @@ -1,33 +1,34 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 OPTION_LIST='Driver=Java,Version=2' DATA_CHARSET=utf8; SELECT * from t1 limit 3; Document {"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":"2014-03-03T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-09-11T00:00:00.000Z"},"grade":"A","score":6},{"date":{"$date":"2013-01-24T00:00:00.000Z"},"grade":"A","score":10},{"date":{"$date":"2011-11-23T00:00:00.000Z"},"grade":"A","score":9},{"date":{"$date":"2011-03-10T00:00:00.000Z"},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} {"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":"2014-12-30T00:00:00.000Z"},"grade":"A","score":8},{"date":{"$date":"2014-07-01T00:00:00.000Z"},"grade":"B","score":23},{"date":{"$date":"2013-04-30T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2012-05-08T00:00:00.000Z"},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":"2014-09-06T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-07-22T00:00:00.000Z"},"grade":"A","score":11},{"date":{"$date":"2012-07-31T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2011-12-29T00:00:00.000Z"},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985136,40.767692],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":"2014-09-06T00:00:00.000Z"},"grade":"A","score":2},{"date":{"$date":"2013-07-22T00:00:00.000Z"},"grade":"A","score":11},{"date":{"$date":"2012-07-31T00:00:00.000Z"},"grade":"A","score":12},{"date":{"$date":"2011-12-29T00:00:00.000Z"},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} DROP TABLE t1; # # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=Java,Version=2' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +OPTION_LIST='Depth=1,Driver=Java,Version=2' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath _id 1 CHAR 24 24 0 0 _id address_building 1 CHAR 10 10 0 0 address.building -address_coord 1 CHAR 256 256 0 1 address.coord +address_coord 1 CHAR 1024 1024 0 1 address.coord address_street 1 CHAR 38 38 0 0 address.street address_zipcode 1 CHAR 5 5 0 0 address.zipcode borough 1 CHAR 13 13 0 0 cuisine 1 CHAR 64 64 0 0 -grades_date 1 CHAR 256 256 0 1 grades.0.date +grades_date 1 CHAR 1024 1024 0 1 grades.0.date grades_grade 1 CHAR 14 14 0 1 grades.0.grade -grades_score 5 BIGINT 2 2 0 1 grades.0.score +grades_score 7 INTEGER 2 2 0 1 grades.0.score name 1 CHAR 98 98 0 0 restaurant_id 1 CHAR 8 8 0 0 DROP TABLE t1; @@ -60,30 +61,30 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants -OPTION_LIST='Level=1,Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` varchar(256) DEFAULT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_date` varchar(256) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' `LRECL`=4096 +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' `LRECL`=4096 SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856077 Morris Park Ave 10462 Bronx Bakery 2014-03-03T00:00:00.000Z A 2 Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.961704 Flatbush Avenue 11225 Brooklyn Hamburgers 2014-12-30T00:00:00.000Z A 8 Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.98513559999999 West 57 Street 10019 Manhattan Irish 2014-09-06T00:00:00.000Z A 2 Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999 Stillwell Avenue 11224 Brooklyn American 2014-06-10T00:00:00.000Z A 5 Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.8601152 63 Road 11374 Queens Jewish/Kosher 2014-11-24T00:00:00.000Z Z 20 Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 2014-03-03T00:00:00.000Z A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 2014-12-30T00:00:00.000Z A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 2014-09-06T00:00:00.000Z A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 2014-06-10T00:00:00.000Z A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 2014-11-24T00:00:00.000Z Z 20 Tov Kosher Kitchen 40356068 DROP TABLE t1; # # Dropping a column @@ -92,16 +93,16 @@ CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants DATA_CHARSET= COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=2,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; SELECT * FROM t1 LIMIT 10; _id address borough cuisine name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856077 40.848447 Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.961704 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.98513559999999 40.7676919 West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999 40.579505 Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.8601152 40.7311739 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 -58ada47de5a51ddfcd5ed521 8825 -73.8803827 40.7643124 Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 -58ada47de5a51ddfcd5ed522 2206 -74.1377286 40.6119572 Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 -58ada47de5a51ddfcd5ed523 7114 -73.9068506 40.6199034 Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 -58ada47de5a51ddfcd5ed524 6409 -74.00528899999999 40.628886 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 -58ada47de5a51ddfcd5ed525 1839 -73.9482609 40.6408271 Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 DROP TABLE t1; # # Specifying Jpath @@ -111,12 +112,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; @@ -251,15 +252,15 @@ OPTION_LIST='Driver=Java,level=2,version=2'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` double(18,16) DEFAULT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_date` char(24) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` char(24) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=2' `LRECL`=4096 @@ -305,8 +306,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=JSON TABNAME='cities' @@ -344,11 +345,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=2' CONNECTION='mongodb://localhost:27017' LRECL=4096; INSERT INTO t1 VALUES diff --git a/storage/connect/mysql-test/connect/r/json_java_3.result b/storage/connect/mysql-test/connect/r/json_java_3.result index eb8bfc022d6..b74f92bfee2 100644 --- a/storage/connect/mysql-test/connect/r/json_java_3.result +++ b/storage/connect/mysql-test/connect/r/json_java_3.result @@ -1,33 +1,34 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=4096 OPTION_LIST='Driver=Java,Version=3' DATA_CHARSET=utf8; SELECT * from t1 limit 3; Document {"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} {"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.98513559999999,40.7676919],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985136,40.767692],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} DROP TABLE t1; # # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=Java,Version=3' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; +OPTION_LIST='Depth=1,Driver=Java,Version=3' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=4096; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath _id 1 CHAR 24 24 0 0 _id address_building 1 CHAR 10 10 0 0 address.building -address_coord 1 CHAR 256 256 0 1 address.coord +address_coord 1 CHAR 1024 1024 0 1 address.coord address_street 1 CHAR 38 38 0 0 address.street address_zipcode 1 CHAR 5 5 0 0 address.zipcode borough 1 CHAR 13 13 0 0 cuisine 1 CHAR 64 64 0 0 -grades_date 1 CHAR 256 256 0 1 grades.0.date +grades_date 1 CHAR 1024 1024 0 1 grades.0.date grades_grade 1 CHAR 14 14 0 1 grades.0.grade -grades_score 5 BIGINT 2 2 0 1 grades.0.score +grades_score 7 INTEGER 2 2 0 1 grades.0.score name 1 CHAR 98 98 0 0 restaurant_id 1 CHAR 8 8 0 0 DROP TABLE t1; @@ -60,30 +61,30 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants -OPTION_LIST='Level=1,Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096 DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` varchar(256) DEFAULT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_date` varchar(256) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' `LRECL`=4096 +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' `LRECL`=4096 SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856077 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.961704 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.98513559999999 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.8601152 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed51c 1007 -73.856077, 40.848447 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.961704, 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.98513559999999, 40.7676919 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999, 40.579505 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.8601152, 40.7311739 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 DROP TABLE t1; # # Dropping a column @@ -92,16 +93,16 @@ CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants DATA_CHARSET= COLIST='{"grades":0}' OPTION_LIST='Driver=Java,Version=3,level=0' CONNECTION='mongodb://localhost:27017' LRECL=4096; SELECT * FROM t1 LIMIT 10; _id address borough cuisine name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856077 40.848447 Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.961704 40.662942 Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.98513559999999 40.7676919 West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.98241999999999 40.579505 Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.8601152 40.7311739 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 -58ada47de5a51ddfcd5ed521 8825 -73.8803827 40.7643124 Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 -58ada47de5a51ddfcd5ed522 2206 -74.1377286 40.6119572 Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 -58ada47de5a51ddfcd5ed523 7114 -73.9068506 40.6199034 Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 -58ada47de5a51ddfcd5ed524 6409 -74.00528899999999 40.628886 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 -58ada47de5a51ddfcd5ed525 1839 -73.9482609 40.6408271 Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +58ada47de5a51ddfcd5ed51c 1007 (-73.856077, 40.848447) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.961704, 40.662942) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.98513559999999, 40.7676919) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.98241999999999, 40.579505) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.8601152, 40.7311739) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.8803827, 40.7643124) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.1377286, 40.6119572) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.9068506, 40.6199034) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.00528899999999, 40.628886) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.9482609, 40.6408271) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 DROP TABLE t1; # # Specifying Jpath @@ -111,12 +112,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; @@ -251,15 +252,15 @@ OPTION_LIST='Driver=Java,level=2,version=3'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` double(18,16) DEFAULT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(18,16) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_date` bigint(13) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=3' `LRECL`=4096 @@ -305,8 +306,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=JSON TABNAME='cities' @@ -344,11 +345,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=3' CONNECTION='mongodb://localhost:27017' LRECL=4096; INSERT INTO t1 VALUES diff --git a/storage/connect/mysql-test/connect/r/json_mongo_c.result b/storage/connect/mysql-test/connect/r/json_mongo_c.result index 550e94f286e..008e41f63d1 100644 --- a/storage/connect/mysql-test/connect/r/json_mongo_c.result +++ b/storage/connect/mysql-test/connect/r/json_mongo_c.result @@ -1,33 +1,34 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CONNECTION='mongodb://localhost:27017' LRECL=1024 OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; SELECT * from t1 limit 3; Document -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985136,40.767692],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} DROP TABLE t1; # # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=1024; +OPTION_LIST='Depth=1,Driver=C,Version=0' DATA_CHARSET=utf8 CONNECTION='mongodb://localhost:27017' LRECL=1024; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Jpath _id 1 CHAR 24 24 0 0 _id address_building 1 CHAR 10 10 0 0 address.building -address_coord 1 CHAR 256 256 0 1 address.coord +address_coord 1 CHAR 1024 1024 0 1 address.coord address_street 1 CHAR 38 38 0 0 address.street address_zipcode 1 CHAR 5 5 0 0 address.zipcode borough 1 CHAR 13 13 0 0 cuisine 1 CHAR 64 64 0 0 -grades_date 1 CHAR 256 256 0 1 grades.0.date +grades_date 1 CHAR 1024 1024 0 1 grades.0.date grades_grade 1 CHAR 14 14 0 1 grades.0.grade -grades_score 5 BIGINT 2 2 0 1 grades.0.score +grades_score 7 INTEGER 2 2 0 1 grades.0.score name 1 CHAR 98 98 0 0 restaurant_id 1 CHAR 8 8 0 0 DROP TABLE t1; @@ -60,30 +61,30 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants -OPTION_LIST='Level=1,Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` varchar(256) DEFAULT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(1024) DEFAULT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_date` varchar(256) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` varchar(1024) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' `LRECL`=1024 +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' `LRECL`=1024 SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_date grades_grade grades_score name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856076999999999089 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.96170399999999745 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.985135599999992451 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.982419999999990523 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.860115199999995639 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed51c 1007 -73.856076999999999089, 40.848447000000000173 Morris Park Ave 10462 Bronx Bakery 1393804800 A 2 Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 -73.96170399999999745, 40.66294200000000103 Flatbush Avenue 11225 Brooklyn Hamburgers 1419897600 A 8 Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 -73.985135599999992451, 40.767691900000002647 West 57 Street 10019 Manhattan Irish 1409961600 A 2 Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 -73.982419999999990523, 40.579504999999997494 Stillwell Avenue 11224 Brooklyn American 1402358400 A 5 Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 -73.860115199999995639, 40.731173900000001709 63 Road 11374 Queens Jewish/Kosher 1416787200 Z 20 Tov Kosher Kitchen 40356068 DROP TABLE t1; # # Dropping a column @@ -92,16 +93,16 @@ CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=JSON TABNAME=restaurants DATA_CHARSET= COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; SELECT * FROM t1 LIMIT 10; _id address borough cuisine name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 -73.856076999999999089 40.848447000000000173 Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 -73.96170399999999745 40.66294200000000103 Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 -73.985135599999992451 40.767691900000002647 West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 -73.982419999999990523 40.579504999999997494 Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 -73.860115199999995639 40.731173900000001709 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 -58ada47de5a51ddfcd5ed521 8825 -73.880382699999998408 40.764312400000001446 Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 -58ada47de5a51ddfcd5ed522 2206 -74.137728600000002643 40.611957199999999091 Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 -58ada47de5a51ddfcd5ed523 7114 -73.906850599999998508 40.619903399999998328 Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 -58ada47de5a51ddfcd5ed524 6409 -74.005288999999990551 40.628886000000001388 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 -58ada47de5a51ddfcd5ed525 1839 -73.948260899999993967 40.640827100000002758 Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +58ada47de5a51ddfcd5ed51c 1007 (-73.856076999999999089, 40.848447000000000173) Morris Park Ave 10462 Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 (-73.96170399999999745, 40.66294200000000103) Flatbush Avenue 11225 Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 (-73.985135599999992451, 40.767691900000002647) West 57 Street 10019 Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 (-73.982419999999990523, 40.579504999999997494) Stillwell Avenue 11224 Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 (-73.860115199999995639, 40.731173900000001709) 63 Road 11374 Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 8825 (-73.880382699999998408, 40.764312400000001446) Astoria Boulevard 11369 Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 2206 (-74.137728600000002643, 40.611957199999999091) Victory Boulevard 10314 Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 7114 (-73.906850599999998508, 40.619903399999998328) Avenue U 11234 Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 6409 (-74.005288999999990551, 40.628886000000001388) 11 Avenue 11219 Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 1839 (-73.948260899999993967, 40.640827100000002758) Nostrand Avenue 11226 Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 DROP TABLE t1; # # Specifying Jpath @@ -111,12 +112,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; @@ -251,15 +252,15 @@ OPTION_LIST='Driver=C,level=2,version=0'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `_id` char(24) NOT NULL `FIELD_FORMAT`='_id', - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` double(23,20) DEFAULT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `_id` char(24) NOT NULL `JPATH`='_id', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` double(23,20) DEFAULT NULL `JPATH`='address.coord.0', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_date` bigint(13) DEFAULT NULL `FIELD_FORMAT`='grades.0.date', - `grades_grade` char(14) DEFAULT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_score` bigint(2) DEFAULT NULL `FIELD_FORMAT`='grades.0.score', + `grades_date` bigint(13) DEFAULT NULL `JPATH`='grades.0.date', + `grades_grade` char(14) DEFAULT NULL `JPATH`='grades.0.grade', + `grades_score` int(2) DEFAULT NULL `JPATH`='grades.0.score', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='mongodb://localhost:27017' `TABLE_TYPE`='JSON' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' `LRECL`=1024 @@ -305,8 +306,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=JSON TABNAME='cities' @@ -344,11 +345,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=JSON TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' CONNECTION='mongodb://localhost:27017' LRECL=1024; INSERT INTO t1 VALUES @@ -362,7 +363,7 @@ _id item prices_0 prices_1 prices_2 prices_3 prices_4 1 journal 87 45 63 12 78 2 notebook 123 456 789 NULL NULL 3 paper 5 7 3 8 NULL -4 planner 25 71 44 27 NULL +4 planner 25 71 NULL 44 27 5 postcard 5 7 3 8 NULL DROP TABLE t1; # diff --git a/storage/connect/mysql-test/connect/r/json_udf.result b/storage/connect/mysql-test/connect/r/json_udf.result index 09544bb1ecb..e3ee84d9084 100644 --- a/storage/connect/mysql-test/connect/r/json_udf.result +++ b/storage/connect/mysql-test/connect/r/json_udf.result @@ -187,11 +187,11 @@ DATEPUB int(4) ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json'; SELECT Json_Make_Array(AUTHOR, TITLE, DATEPUB) FROM t2; Json_Make_Array(AUTHOR, TITLE, DATEPUB) -["Jean-Christophe Bernadac","Construire une application XML",1999] +[" Jean-Christophe Bernadac, François Knab","Construire une application XML",1999] ["William J. Pardi","XML en Action",1999] SELECT Json_Make_Object(AUTHOR, TITLE, DATEPUB) FROM t2; Json_Make_Object(AUTHOR, TITLE, DATEPUB) -{"AUTHOR":"Jean-Christophe Bernadac","TITLE":"Construire une application XML","DATEPUB":1999} +{"AUTHOR":" Jean-Christophe Bernadac, François Knab","TITLE":"Construire une application XML","DATEPUB":1999} {"AUTHOR":"William J. Pardi","TITLE":"XML en Action","DATEPUB":1999} SELECT Json_Array_Grp(TITLE, DATEPUB) FROM t2; ERROR HY000: Can't initialize function 'json_array_grp'; This function can only accept 1 argument @@ -322,7 +322,7 @@ JsonGet_String(Json_Make_Array(45,28,36,45,89),'3') 45 SELECT JsonGet_String(Json_Make_Array(45,28,36,45,89),'["+"]') "list",'=' as "egal",JsonGet_String(Json_Make_Array(45,28,36,45,89),'[+]') "sum"; list egal sum -45+28+36+45+89 = 243.00 +45+28+36+45+89 = 243 SELECT JsonGet_String(Json_Make_Array(Json_Make_Array(45,28),Json_Make_Array(36,45,89)),'1.0'); JsonGet_String(Json_Make_Array(Json_Make_Array(45,28),Json_Make_Array(36,45,89)),'1.0') 36 @@ -349,10 +349,10 @@ Warnings: Warning 1105 SELECT department, JsonGet_String(Json_Make_Object(department, Json_Array_Grp(salary) "Json_salaries"),'salaries.[+]') Sumsal FROM t3 GROUP BY department; department Sumsal -0021 28500.00 -0318 72230.00 -0319 89800.95 -2452 45900.00 +0021 28500.000000 +0318 72230.000000 +0319 89800.950000 +2452 45900.000000 SELECT JsonGet_Int(@j1, '4'); JsonGet_Int(@j1, '4') 89 @@ -610,7 +610,7 @@ JsonGet_String(Json_File('test/fx.json'), '1.*') {"_id":6,"type":"car","item":"roadster","mileage":56000,"ratings":[6,9]} SELECT JsonGet_String(Json_File('test/fx.json'), '1'); JsonGet_String(Json_File('test/fx.json'), '1') -6 car roadster 56000 6 9 +6 car roadster 56000 (6, 9) SELECT JsonGet_Int(Json_File('test/fx.json'), '1.mileage') AS Mileage; Mileage 56000 diff --git a/storage/connect/mysql-test/connect/r/json_udf_bin.result b/storage/connect/mysql-test/connect/r/json_udf_bin.result index d0819619c33..c20cf7ce632 100644 --- a/storage/connect/mysql-test/connect/r/json_udf_bin.result +++ b/storage/connect/mysql-test/connect/r/json_udf_bin.result @@ -87,7 +87,7 @@ Json_Get_Item(Jbin_File('gloss.json'),'$.glossary.GlossDiv') {"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}} SELECT JsonGet_String(Json_File('gloss.json'),'$.glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso') lang; lang -GML +GML, XML SELECT Json_Get_Item(Jbin_File('gloss.json'),'$.glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso') "See also"; See also ["GML","XML"] diff --git a/storage/connect/mysql-test/connect/r/misc.result b/storage/connect/mysql-test/connect/r/misc.result new file mode 100644 index 00000000000..6b6372f6e41 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/misc.result @@ -0,0 +1,54 @@ +execute immediate concat('create table t engine=CONNECT table_type=JSON',REPEAT('1',5000), +' FILE_NAME=''users.json'' HTTP=''http://localhost:4142'' URI=''/users'''); +ERROR HY000: Unsupported table type JSON1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +execute immediate concat('create table t engine=CONNECT table_type=OEM module=''libname'' +Option_list=''Myopt=foo'' subtype=''MYTYPE',REPEAT('1', 10000), ''''); +ERROR HY000: Subtype string too long +execute immediate concat('create table t engine=CONNECT table_type=DBF file_name=''', +REPLACE(@@secure_file_priv,'\\','/'),'cust.dbf', REPEAT('1', 10000), ''''); +ERROR HY000: Cannot open +create table t engine=connect table_type=mysql +CONNECTION='mysql://root@localhost:MASTER_MYPORT/test/foobar' + SRCDEF='SELECT 1,''%n'' FROM DUAL WHERE %s'; +select *from t; +ERROR HY000: Got error 174 'MakeSQL: Wrong place holders specification' from CONNECT +drop table t; +create table t engine=connect table_type=mysql +CONNECTION='mysql://root@localhost:MASTER_MYPORT/test/foobar' + SRCDEF='SELECT 1,%n FROM DUAL WHERE %s'; +ERROR HY000: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%n FROM DUAL WHERE 1=1 LIMIT 0' at line 1 [SELECT 1,%n FROM DUAL WHERE 1=1 LIMIT 0] +create table t engine=connect table_type=mysql +CONNECTION='mysql://root@localhost:MASTER_MYPORT/test/foobar' + SRCDEF='SELECT 1 FROM DUAL WHERE %s'; +select *from t; +1 +1 +drop table t; +create table beers ( +`Name` char(16) xpath='brandName', +`Origin` char(16) xpath='origin', +`Description` char(32) xpath='details') +engine=CONNECT table_type=XML file_name='MYSQLTEST_VARDIR/tmp/beer.xml' +tabname='table' option_list='rownode=tr,colnode=td%n'; +select * from beers; +Name Origin Description +NULL NULL NULL +NULL NULL NULL +drop table beers; +create table beers ( +`Name` char(16) xpath='brandName', +`Origin` char(16) xpath='origin', +`Description` char(32) xpath='details') +engine=CONNECT table_type=XML file_name='MYSQLTEST_VARDIR/tmp/beer.xml' +tabname='table' option_list='rownode=tr,colnode=td'; +insert into beers values('11','22','33'); +drop table beers; +execute immediate CONCAT('create table jsampall +(Author char(128) jpath=''$.AUTHOR["', REPEAT('a',10000),'"]'') +engine=CONNECT table_type=JSON +file_name=''',REPLACE(@@secure_file_priv,'\\','/'),'tmp/test.json'''); +select author from jsampall; +author +Jean-Christophe Bernadacaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +William J. Pardi +drop table jsampall; diff --git a/storage/connect/mysql-test/connect/r/mongo_c.result b/storage/connect/mysql-test/connect/r/mongo_c.result index 132bb34ce64..5b29bb54d3a 100644 --- a/storage/connect/mysql-test/connect/r/mongo_c.result +++ b/storage/connect/mysql-test/connect/r/mongo_c.result @@ -1,21 +1,22 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; SELECT * from t1 limit 3; Document -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris ParkBakeShop", "restaurant_id":"30075445"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} -{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj ReynoldsPubAndRestaurant", "restaurant_id":"30191841"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000.000000},"grade":"A","score":2},{"date":{"$date":1378857600000.000000},"grade":"A","score":6},{"date":{"$date":1358985600000.000000},"grade":"A","score":10},{"date":{"$date":1322006400000.000000},"grade":"A","score":9},{"date":{"$date":1299715200000.000000},"grade":"B","score":14}],"name":"Morris Park Bake Shop","restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000.000000},"grade":"A","score":8},{"date":{"$date":1404172800000.000000},"grade":"B","score":23},{"date":{"$date":1367280000000.000000},"grade":"A","score":12},{"date":{"$date":1336435200000.000000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985136,40.767692],"street":"West 57 Street","zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000.000000},"grade":"A","score":2},{"date":{"$date":1374451200000.000000},"grade":"A","score":11},{"date":{"$date":1343692800000.000000},"grade":"A","score":12},{"date":{"$date":1325116800000.000000},"grade":"A","score":12}],"name":"Dj Reynolds Pub And Restaurant","restaurant_id":"30191841"} DROP TABLE t1; # # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8 ; +OPTION_LIST='Depth=1,Driver=C,Version=0' DATA_CHARSET=utf8 ; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Bpath _id 1 CHAR 24 24 0 0 @@ -58,28 +59,28 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants -OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=C,Version=0' DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` varchar(512) NOT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` varchar(512) NOT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_0` varchar(512) DEFAULT NULL `FIELD_FORMAT`='grades.0', + `grades_0` varchar(512) DEFAULT NULL `JPATH`='grades.0', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_0 name restaurant_id -58ada47de5a51ddfcd5ed51c 1007 Morris Park Ave 10462 Bronx Bakery {"date":{"$date":1393804800000},"grade":"A","score":2} Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d 469 Flatbush Avenue 11225 Brooklyn Hamburgers {"date":{"$date":1419897600000},"grade":"A","score":8} Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e 351 West 57 Street 10019 Manhattan Irish {"date":{"$date":1409961600000},"grade":"A","score":2} Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f 2780 Stillwell Avenue 11224 Brooklyn American {"date":{"$date":1402358400000},"grade":"A","score":5} Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 97-22 63 Road 11374 Queens Jewish/Kosher {"date":{"$date":1416787200000},"grade":"Z","score":20} Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed51c 1007 [-73.856077,40.848447] Morris Park Ave 10462 Bronx Bakery {"date":{"$date":1393804800000},"grade":"A","score":2} Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 [-73.961704,40.662942] Flatbush Avenue 11225 Brooklyn Hamburgers {"date":{"$date":1419897600000},"grade":"A","score":8} Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 [-73.985136,40.767692] West 57 Street 10019 Manhattan Irish {"date":{"$date":1409961600000},"grade":"A","score":2} Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 [-73.982420,40.579505] Stillwell Avenue 11224 Brooklyn American {"date":{"$date":1402358400000},"grade":"A","score":5} Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 [-73.860115,40.731174] 63 Road 11374 Queens Jewish/Kosher {"date":{"$date":1416787200000},"grade":"Z","score":20} Tov Kosher Kitchen 40356068 DROP TABLE t1; # # Dropping a column @@ -88,16 +89,16 @@ CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants DATA_CHARSET COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' ; SELECT * FROM t1 LIMIT 10; _id address borough cuisine name restaurant_id -58ada47de5a51ddfcd5ed51c {"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"} Bronx Bakery Morris Park Bake Shop 30075445 -58ada47de5a51ddfcd5ed51d {"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"} Brooklyn Hamburgers Wendy'S 30112340 -58ada47de5a51ddfcd5ed51e {"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"} Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 -58ada47de5a51ddfcd5ed51f {"building":"2780","coord":[-73.982419999999990523,40.579504999999997494],"street":"Stillwell Avenue", "zipcode":"11224"} Brooklyn American Riviera Caterer 40356018 -58ada47de5a51ddfcd5ed520 {"building":"97-22","coord":[-73.860115199999995639,40.731173900000001709],"street":"63 Road", "zipcode":"11374"} Queens Jewish/Kosher Tov Kosher Kitchen 40356068 -58ada47de5a51ddfcd5ed521 {"building":"8825","coord":[-73.880382699999998408,40.764312400000001446],"street":"Astoria Boulevard", "zipcode":"11369"} Queens American Brunos On The Boulevard 40356151 -58ada47de5a51ddfcd5ed522 {"building":"2206","coord":[-74.137728600000002643,40.611957199999999091],"street":"Victory Boulevard", "zipcode":"10314"} Staten Island Jewish/Kosher Kosher Island 40356442 -58ada47de5a51ddfcd5ed523 {"building":"7114","coord":[-73.906850599999998508,40.619903399999998328],"street":"Avenue U", "zipcode":"11234"} Brooklyn Delicatessen Wilken'S Fine Food 40356483 -58ada47de5a51ddfcd5ed524 {"building":"6409","coord":[-74.005288999999990551,40.628886000000001388],"street":"11 Avenue", "zipcode":"11219"} Brooklyn American Regina Caterers 40356649 -58ada47de5a51ddfcd5ed525 {"building":"1839","coord":[-73.948260899999993967,40.640827100000002758],"street":"Nostrand Avenue", "zipcode":"11226"} Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +58ada47de5a51ddfcd5ed51c {"building":"1007","coord":[-73.856077,40.848447],"street":"Morris Park Ave","zipcode":"10462"} Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d {"building":"469","coord":[-73.961704,40.662942],"street":"Flatbush Avenue","zipcode":"11225"} Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e {"building":"351","coord":[-73.985136,40.767692],"street":"West 57 Street","zipcode":"10019"} Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f {"building":"2780","coord":[-73.982420,40.579505],"street":"Stillwell Avenue","zipcode":"11224"} Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 {"building":"97-22","coord":[-73.860115,40.731174],"street":"63 Road","zipcode":"11374"} Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 {"building":"8825","coord":[-73.880383,40.764312],"street":"Astoria Boulevard","zipcode":"11369"} Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 {"building":"2206","coord":[-74.137729,40.611957],"street":"Victory Boulevard","zipcode":"10314"} Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 {"building":"7114","coord":[-73.906851,40.619903],"street":"Avenue U","zipcode":"11234"} Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 {"building":"6409","coord":[-74.005289,40.628886],"street":"11 Avenue","zipcode":"11219"} Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 {"building":"1839","coord":[-73.948261,40.640827],"street":"Nostrand Avenue","zipcode":"11226"} Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 DROP TABLE t1; # # Specifying Jpath @@ -107,12 +108,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' ; @@ -248,14 +249,14 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(6) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord_0` double(12,6) NOT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(25) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(6) NOT NULL `JPATH`='address.building', + `address_coord_0` double(12,6) NOT NULL `JPATH`='address.coord.0', + `address_street` char(25) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_0_date` datetime NOT NULL `FIELD_FORMAT`='grades.0.date', - `grades_0_grade` char(14) NOT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_0_score` int(11) NOT NULL `FIELD_FORMAT`='grades.0.score', + `grades_0_date` datetime NOT NULL `JPATH`='grades.0.date', + `grades_0_grade` char(14) NOT NULL `JPATH`='grades.0.grade', + `grades_0_score` int(11) NOT NULL `JPATH`='grades.0.score', `name` char(32) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' @@ -301,8 +302,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=MONGO TABNAME='cities' @@ -340,11 +341,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=C,Version=0' ; INSERT INTO t1 VALUES diff --git a/storage/connect/mysql-test/connect/r/mongo_java_2.result b/storage/connect/mysql-test/connect/r/mongo_java_2.result index bc186d7137e..bcedd717cc4 100644 --- a/storage/connect/mysql-test/connect/r/mongo_java_2.result +++ b/storage/connect/mysql-test/connect/r/mongo_java_2.result @@ -1,8 +1,9 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants OPTION_LIST='Driver=Java,Version=2' DATA_CHARSET=utf8; SELECT * from t1 limit 3; @@ -15,7 +16,7 @@ DROP TABLE t1; # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=Java,Version=2' DATA_CHARSET=utf8 ; +OPTION_LIST='Depth=1,Driver=Java,Version=2' DATA_CHARSET=utf8 ; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Bpath _id 1 CHAR 24 24 0 0 @@ -58,21 +59,21 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants -OPTION_LIST='Level=1,Driver=Java,Version=2' DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=Java,Version=2' DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` char(41) NOT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` char(41) NOT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_0` char(99) DEFAULT NULL `FIELD_FORMAT`='grades.0', + `grades_0` char(99) DEFAULT NULL `JPATH`='grades.0', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=2' `DATA_CHARSET`='utf8' SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_0 name restaurant_id 58ada47de5a51ddfcd5ed51c 1007 [ -73.856077 , 40.848447] Morris Park Ave 10462 Bronx Bakery { "date" : { "$date" : "2014-03-03T00:00:00.000Z"} , "grade" : "A" , "score" : 2} Morris Park Bake Shop 30075445 @@ -107,12 +108,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=2' ; @@ -248,14 +249,14 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(6) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord_0` double(18,14) NOT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(25) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(6) NOT NULL `JPATH`='address.building', + `address_coord_0` double(18,14) NOT NULL `JPATH`='address.coord.0', + `address_street` char(25) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_0_date` datetime NOT NULL `FIELD_FORMAT`='grades.0.date', - `grades_0_grade` char(14) NOT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_0_score` int(2) NOT NULL `FIELD_FORMAT`='grades.0.score', + `grades_0_date` datetime NOT NULL `JPATH`='grades.0.date', + `grades_0_grade` char(14) NOT NULL `JPATH`='grades.0.grade', + `grades_0_score` int(2) NOT NULL `JPATH`='grades.0.score', `name` char(32) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=2' @@ -301,8 +302,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=MONGO TABNAME='cities' @@ -340,11 +341,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=2' ; INSERT INTO t1 VALUES diff --git a/storage/connect/mysql-test/connect/r/mongo_java_3.result b/storage/connect/mysql-test/connect/r/mongo_java_3.result index 30c696fc9eb..3183d4984d6 100644 --- a/storage/connect/mysql-test/connect/r/mongo_java_3.result +++ b/storage/connect/mysql-test/connect/r/mongo_java_3.result @@ -1,8 +1,9 @@ set connect_enable_mongo=1; +set connect_json_all_path=0; # # Test the MONGO table type # -CREATE TABLE t1 (Document varchar(1024) field_format='*') +CREATE TABLE t1 (Document varchar(1024) JPATH='*') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants OPTION_LIST='Driver=Java,Version=3' DATA_CHARSET=utf8; SELECT * from t1 limit 3; @@ -15,7 +16,7 @@ DROP TABLE t1; # Test catfunc # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CATFUNC=columns -OPTION_LIST='Level=1,Driver=Java,Version=3' DATA_CHARSET=utf8 ; +OPTION_LIST='Depth=1,Driver=Java,Version=3' DATA_CHARSET=utf8 ; SELECT * from t1; Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Bpath _id 1 CHAR 24 24 0 0 @@ -58,21 +59,21 @@ DROP TABLE t1; # Test discovery # CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants -OPTION_LIST='Level=1,Driver=Java,Version=3' DATA_CHARSET=utf8; +OPTION_LIST='Depth=1,Driver=Java,Version=3' DATA_CHARSET=utf8; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord` char(39) NOT NULL `FIELD_FORMAT`='address.coord', - `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(10) NOT NULL `JPATH`='address.building', + `address_coord` char(39) NOT NULL `JPATH`='address.coord', + `address_street` char(38) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, `cuisine` char(64) NOT NULL, - `grades_0` char(84) DEFAULT NULL `FIELD_FORMAT`='grades.0', + `grades_0` char(84) DEFAULT NULL `JPATH`='grades.0', `name` char(98) NOT NULL, `restaurant_id` char(8) NOT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Depth=1,Driver=Java,Version=3' `DATA_CHARSET`='utf8' SELECT * FROM t1 LIMIT 5; _id address_building address_coord address_street address_zipcode borough cuisine grades_0 name restaurant_id 58ada47de5a51ddfcd5ed51c 1007 [-73.856077, 40.848447] Morris Park Ave 10462 Bronx Bakery { "date" : { "$date" : 1393804800000 }, "grade" : "A", "score" : 2 } Morris Park Bake Shop 30075445 @@ -107,12 +108,12 @@ _id VARCHAR(24) NOT NULL, name VARCHAR(64) NOT NULL, cuisine CHAR(200) NOT NULL, borough CHAR(16) NOT NULL, -street VARCHAR(65) FIELD_FORMAT='address.street', -building CHAR(16) FIELD_FORMAT='address.building', -zipcode CHAR(5) FIELD_FORMAT='address.zipcode', -grade CHAR(1) FIELD_FORMAT='grades.0.grade', -score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', -`date` DATE FIELD_FORMAT='grades.0.date', +street VARCHAR(65) JPATH='address.street', +building CHAR(16) JPATH='address.building', +zipcode CHAR(5) JPATH='address.zipcode', +grade CHAR(1) JPATH='grades.0.grade', +score INT(4) NOT NULL JPATH='grades.0.score', +`date` DATE JPATH='grades.0.date', restaurant_id VARCHAR(255) NOT NULL) ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=3' ; @@ -248,14 +249,14 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `_id` char(24) NOT NULL, - `address_building` char(6) NOT NULL `FIELD_FORMAT`='address.building', - `address_coord_0` double(18,14) NOT NULL `FIELD_FORMAT`='address.coord.0', - `address_street` char(25) NOT NULL `FIELD_FORMAT`='address.street', - `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `address_building` char(6) NOT NULL `JPATH`='address.building', + `address_coord_0` double(18,14) NOT NULL `JPATH`='address.coord.0', + `address_street` char(25) NOT NULL `JPATH`='address.street', + `address_zipcode` char(5) NOT NULL `JPATH`='address.zipcode', `borough` char(13) NOT NULL, - `grades_0_date` datetime NOT NULL `FIELD_FORMAT`='grades.0.date', - `grades_0_grade` char(14) NOT NULL `FIELD_FORMAT`='grades.0.grade', - `grades_0_score` int(2) NOT NULL `FIELD_FORMAT`='grades.0.score', + `grades_0_date` datetime NOT NULL `JPATH`='grades.0.date', + `grades_0_grade` char(14) NOT NULL `JPATH`='grades.0.grade', + `grades_0_score` int(2) NOT NULL `JPATH`='grades.0.score', `name` char(32) NOT NULL, `restaurant_id` char(8) NOT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `COLIST`='{"cuisine":0}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=Java,level=2,version=3' @@ -301,8 +302,8 @@ false CREATE TABLE t1 ( _id char(5) NOT NULL, city char(16) NOT NULL, -loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', -loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +loc_0 double(12,6) NOT NULL `JPATH`='loc.0', +loc_1 char(12) NOT NULL `JPATH`='loc.1', pop int(11) NOT NULL, state char(2) NOT NULL) ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=MONGO TABNAME='cities' @@ -340,11 +341,11 @@ true CREATE TABLE t1 ( _id int(4) NOT NULL, item CHAR(8) NOT NULL, -prices_0 INT(6) FIELD_FORMAT='prices.0', -prices_1 INT(6) FIELD_FORMAT='prices.1', -prices_2 INT(6) FIELD_FORMAT='prices.2', -prices_3 INT(6) FIELD_FORMAT='prices.3', -prices_4 INT(6) FIELD_FORMAT='prices.4') +prices_0 INT(6) JPATH='prices.0', +prices_1 INT(6) JPATH='prices.1', +prices_2 INT(6) JPATH='prices.2', +prices_3 INT(6) JPATH='prices.3', +prices_4 INT(6) JPATH='prices.4') ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' DATA_CHARSET=utf8 OPTION_LIST='Driver=Java,Version=3' ; INSERT INTO t1 VALUES diff --git a/storage/connect/mysql-test/connect/r/mysql.result b/storage/connect/mysql-test/connect/r/mysql.result index 3ac23394cbc..4377d2ad014 100644 --- a/storage/connect/mysql-test/connect/r/mysql.result +++ b/storage/connect/mysql-test/connect/r/mysql.result @@ -294,3 +294,15 @@ a 30 DROP TABLE t2; DROP TABLE t1; +# +# MDEV-27766 CONNECT Engine Support for INSERT IGNORE with Mysql Table type +# +CREATE TABLE t1 (a INT PRIMARY KEY); +INSERT INTO t1 VALUES (10),(20),(30); +CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=MYSQL CONNECTION='mysql://root@localhost:PORT/test/t1' OPTION_LIST="Delayed=1,Ignored=1"; +INSERT INTO t2 VALUES (10),(20),(30),(40); +DROP TABLE t2; +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/storage/connect/mysql-test/connect/r/odbc_oracle.result b/storage/connect/mysql-test/connect/r/odbc_oracle.result index 8dc7dc07bb1..317aacef7a9 100644 --- a/storage/connect/mysql-test/connect/r/odbc_oracle.result +++ b/storage/connect/mysql-test/connect/r/odbc_oracle.result @@ -10,7 +10,7 @@ SET NAMES utf8; # All tables in all schemas (filtered with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -20,7 +20,7 @@ NULL MTR V1 VIEW NULL DROP TABLE t1; # All tables in all schemas (filtered with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables TABNAME='%.%'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -30,7 +30,7 @@ NULL MTR V1 VIEW NULL DROP TABLE t1; # All tables "T1" in all schemas (filtered with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables TABNAME='%.T1'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -38,7 +38,7 @@ NULL MTR T1 TABLE NULL DROP TABLE t1; # All tables "T1" in all schemas (filtered with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables TABNAME='T1'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -46,7 +46,7 @@ NULL MTR T1 TABLE NULL DROP TABLE t1; # Table "T1" in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables TABNAME='MTR.T1'; SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -54,7 +54,7 @@ NULL MTR T1 TABLE NULL DROP TABLE t1; # All tables in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Tables TABNAME='MTR.%'; SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Table_Type Remark @@ -68,7 +68,7 @@ DROP TABLE t1; # All columns in all schemas (limited with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Columns; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks @@ -80,7 +80,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu DROP TABLE t1; # All columns in all schemas (limited with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Columns TABNAME='%.%'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks @@ -91,7 +91,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu MTR V1 B 6 NUMBER 38 40 NULL NULL 1 DROP TABLE t1; # All tables "T1" in all schemas (limited with WHERE) -CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' CATFUNC=Columns TABNAME='%.T1'; +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Columns TABNAME='%.T1'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks MTR T1 A 3 DECIMAL 38 40 0 10 1 @@ -99,7 +99,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu DROP TABLE t1; # Table "T1" in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Columns TABNAME='MTR.T1'; SELECT * FROM t1 ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks @@ -108,7 +108,7 @@ Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Bu DROP TABLE t1; # All tables "T1" in all schemas (filtered with WHERE) CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' CATFUNC=Columns TABNAME='%.T1'; SELECT * FROM t1 WHERE Table_Schema='MTR' ORDER BY Table_Schema, Table_Name; Table_Cat Table_Schema Table_Name Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Radix Nullable Remarks @@ -121,14 +121,14 @@ DROP TABLE t1; # Table "T1" in the default schema ("MTR") CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' TABNAME='T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `A` decimal(40,0) DEFAULT NULL, `B` double DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='T1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' `TABLE_TYPE`='ODBC' `TABNAME`='T1' SELECT * FROM t1 ORDER BY A; A B 10 1000000000 @@ -157,14 +157,14 @@ DROP VIEW v1; DROP TABLE t1; # Table "T1" in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' TABNAME='MTR.T1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `A` decimal(40,0) DEFAULT NULL, `B` double DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T1' SELECT * FROM t1; A B 10 1000000000 @@ -173,14 +173,14 @@ A B DROP TABLE t1; # View "V1" in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' TABNAME='MTR.V1'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `A` decimal(40,0) DEFAULT NULL, `B` double DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.V1' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.V1' SELECT * FROM t1; A B 10 1000000000 @@ -209,13 +209,13 @@ DROP VIEW v1; DROP TABLE t1; # Table "T2" in the schema "MTR" CREATE TABLE t1 ENGINE=CONNECT -TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' +TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' TABNAME='MTR.T2'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `A` varchar(64) DEFAULT NULL -) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T2' +) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtrnew' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.T2' SELECT * FROM t1; A test diff --git a/storage/connect/mysql-test/connect/r/rest.result b/storage/connect/mysql-test/connect/r/rest.result new file mode 100644 index 00000000000..3c4ec80ce71 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/rest.result @@ -0,0 +1,19 @@ +# +# Testing REST query +# +CREATE TABLE t1 +ENGINE=CONNECT DATA_CHARSET=utf8 TABLE_TYPE=JSON FILE_NAME='users.json' +HTTP='http://jsonplaceholder.typicode.com/users'; +SELECT * FROM t1; +id name username email address_street address_suite address_city address_zipcode address_geo_lat address_geo_lng phone website company_name company_catchPhrase company_bs +1 Leanne Graham Bret Sincere@april.biz Kulas Light Apt. 556 Gwenborough 92998-3874 -37.3159 81.1496 1-770-736-8031 x56442 hildegard.org Romaguera-Crona Multi-layered client-server neural-net harness real-time e-markets +2 Ervin Howell Antonette Shanna@melissa.tv Victor Plains Suite 879 Wisokyburgh 90566-7771 -43.9509 -34.4618 010-692-6593 x09125 anastasia.net Deckow-Crist Proactive didactic contingency synergize scalable supply-chains +3 Clementine Bauch Samantha Nathan@yesenia.net Douglas Extension Suite 847 McKenziehaven 59590-4157 -68.6102 -47.0653 1-463-123-4447 ramiro.info Romaguera-Jacobson Face to face bifurcated interface e-enable strategic applications +4 Patricia Lebsack Karianne Julianne.OConner@kory.org Hoeger Mall Apt. 692 South Elvis 53919-4257 29.4572 -164.2990 493-170-9623 x156 kale.biz Robel-Corkery Multi-tiered zero tolerance productivity transition cutting-edge web services +5 Chelsey Dietrich Kamren Lucio_Hettinger@annie.ca Skiles Walks Suite 351 Roscoeview 33263 -31.8129 62.5342 (254)954-1289 demarco.info Keebler LLC User-centric fault-tolerant solution revolutionize end-to-end systems +6 Mrs. Dennis Schulist Leopoldo_Corkery Karley_Dach@jasper.info Norberto Crossing Apt. 950 South Christy 23505-1337 -71.4197 71.7478 1-477-935-8478 x6430 ola.org Considine-Lockman Synchronised bottom-line interface e-enable innovative applications +7 Kurtis Weissnat Elwyn.Skiles Telly.Hoeger@billy.biz Rex Trail Suite 280 Howemouth 58804-1099 24.8918 21.8984 210.067.6132 elvis.io Johns Group Configurable multimedia task-force generate enterprise e-tailers +8 Nicholas Runolfsdottir V Maxime_Nienow Sherwood@rosamond.me Ellsworth Summit Suite 729 Aliyaview 45169 -14.3990 -120.7677 586.493.6943 x140 jacynthe.com Abernathy Group Implemented secondary concept e-enable extensible e-tailers +9 Glenna Reichert Delphine Chaim_McDermott@dana.io Dayna Park Suite 449 Bartholomebury 76495-3109 24.6463 -168.8889 (775)976-6794 x41206 conrad.com Yost and Sons Switchable contextually-based project aggregate real-time technologies +10 Clementina DuBuque Moriah.Stanton Rey.Padberg@karina.biz Kattie Turnpike Suite 198 Lebsackbury 31428-2261 -38.2386 57.2232 024-648-3804 ambrose.net Hoeger LLC Centralized empowering task-force target end-to-end models +DROP TABLE t1; diff --git a/storage/connect/mysql-test/connect/r/updelx.result b/storage/connect/mysql-test/connect/r/updelx.result index 2aed1e06928..bb82afcc1a8 100644 --- a/storage/connect/mysql-test/connect/r/updelx.result +++ b/storage/connect/mysql-test/connect/r/updelx.result @@ -978,7 +978,7 @@ DROP TABLE t1; # FIX table CREATE TABLE t1 ( id INT(4) KEY NOT NULL, -msg VARCHAR(16) CHARSET BINARY DISTRIB=CLUSTERED) +msg VARCHAR(16) DISTRIB=CLUSTERED) ENGINE=CONNECT TABLE_TYPE=FIX BLOCK_SIZE=4; Warnings: Warning 1105 No file name. Table will use t1.fix @@ -1345,7 +1345,7 @@ DROP TABLE t1; # BIN table CREATE TABLE t1 ( id INT(4) KEY NOT NULL, -msg VARCHAR(16) CHARSET BINARY DISTRIB=CLUSTERED) +msg VARCHAR(16) DISTRIB=CLUSTERED) ENGINE=CONNECT TABLE_TYPE=BIN BLOCK_SIZE=8; Warnings: Warning 1105 No file name. Table will use t1.bin diff --git a/storage/connect/mysql-test/connect/r/xml.result b/storage/connect/mysql-test/connect/r/xml.result index 6a0c9db27b3..575c903bbbc 100644 --- a/storage/connect/mysql-test/connect/r/xml.result +++ b/storage/connect/mysql-test/connect/r/xml.result @@ -374,8 +374,7 @@ INSERT INTO t1 VALUES (_cp1251 0xC0C1C2C3); Warnings: Level Warning Code 1105 -Message Com error: Unable to save character to 'iso-8859-1' encoding.
- +Message warning about characters outside of iso-8859-1 INSERT INTO t1 VALUES ('&<>"\''); SELECT node, hex(node) FROM t1; node &<>"' diff --git a/storage/connect/mysql-test/connect/r/xml2.result b/storage/connect/mysql-test/connect/r/xml2.result index f7bbc17c8a0..891c6e6f8dd 100644 --- a/storage/connect/mysql-test/connect/r/xml2.result +++ b/storage/connect/mysql-test/connect/r/xml2.result @@ -87,9 +87,9 @@ DROP TABLE t1; # Testing mixed tag and attribute values # CREATE TABLE t1 ( -ISBN CHAR(15) FIELD_FORMAT='@', -LANG CHAR(2) FIELD_FORMAT='@', -SUBJECT CHAR(32) FIELD_FORMAT='@', +ISBN CHAR(15) XPATH='@', +LANG CHAR(2) XPATH='@', +SUBJECT CHAR(32) XPATH='@', AUTHOR CHAR(50), TITLE CHAR(32), TRANSLATOR CHAR(40), @@ -120,9 +120,9 @@ DROP TABLE t1; # Testing INSERT on mixed tag and attribute values # CREATE TABLE t1 ( -ISBN CHAR(15) FIELD_FORMAT='@', -LANG CHAR(2) FIELD_FORMAT='@', -SUBJECT CHAR(32) FIELD_FORMAT='@', +ISBN CHAR(15) XPATH='@', +LANG CHAR(2) XPATH='@', +SUBJECT CHAR(32) XPATH='@', AUTHOR CHAR(50), TITLE CHAR(32), TRANSLATOR CHAR(40), @@ -207,18 +207,18 @@ DROP TABLE t1; # Testing XPath # CREATE TABLE t1 ( -isbn CHAR(15) FIELD_FORMAT='@ISBN', -language CHAR(2) FIELD_FORMAT='@LANG', -subject CHAR(32) FIELD_FORMAT='@SUBJECT', -authorfn CHAR(20) FIELD_FORMAT='AUTHOR/FIRSTNAME', -authorln CHAR(20) FIELD_FORMAT='AUTHOR/LASTNAME', -title CHAR(32) FIELD_FORMAT='TITLE', -translated CHAR(32) FIELD_FORMAT='TRANSLATOR/@PREFIX', -tranfn CHAR(20) FIELD_FORMAT='TRANSLATOR/FIRSTNAME', -tranln CHAR(20) FIELD_FORMAT='TRANSLATOR/LASTNAME', -publisher CHAR(20) FIELD_FORMAT='PUBLISHER/NAME', -location CHAR(20) FIELD_FORMAT='PUBLISHER/PLACE', -year INT(4) FIELD_FORMAT='DATEPUB' +isbn CHAR(15) XPATH='@ISBN', +language CHAR(2) XPATH='@LANG', +subject CHAR(32) XPATH='@SUBJECT', +authorfn CHAR(20) XPATH='AUTHOR/FIRSTNAME', +authorln CHAR(20) XPATH='AUTHOR/LASTNAME', +title CHAR(32) XPATH='TITLE', +translated CHAR(32) XPATH='TRANSLATOR/@PREFIX', +tranfn CHAR(20) XPATH='TRANSLATOR/FIRSTNAME', +tranln CHAR(20) XPATH='TRANSLATOR/LASTNAME', +publisher CHAR(20) XPATH='PUBLISHER/NAME', +location CHAR(20) XPATH='PUBLISHER/PLACE', +year INT(4) XPATH='DATEPUB' ) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=libxml2'; SELECT * FROM t1; @@ -260,7 +260,7 @@ DROP TABLE t1; # CREATE TABLE t1 ( -isbn CHAR(15) FIELD_FORMAT='@isbn' +isbn CHAR(15) XPATH='@isbn' ) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample.xml' TABNAME='BIBLIO' OPTION_LIST='rownode=BOOK,skipnull=1,xmlsup=libxml2'; SELECT * FROM t1; diff --git a/storage/connect/mysql-test/connect/r/xml2_html.result b/storage/connect/mysql-test/connect/r/xml2_html.result index 143f46529f6..499108b724d 100644 --- a/storage/connect/mysql-test/connect/r/xml2_html.result +++ b/storage/connect/mysql-test/connect/r/xml2_html.result @@ -5,9 +5,9 @@ SET NAMES utf8; # Testing HTML like XML file # CREATE TABLE beers ( -`Name` CHAR(16) FIELD_FORMAT='brandName', -`Origin` CHAR(16) FIELD_FORMAT='origin', -`Description` CHAR(32) FIELD_FORMAT='details') +`Name` CHAR(16) XPATH='brandName', +`Origin` CHAR(16) XPATH='origin', +`Description` CHAR(32) XPATH='details') ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='beers.xml' TABNAME='table' OPTION_LIST='xmlsup=libxml2,rownode=tr,colnode=td'; SELECT * FROM beers; diff --git a/storage/connect/mysql-test/connect/r/xml2_mult.result b/storage/connect/mysql-test/connect/r/xml2_mult.result index 87d1118edd5..ac4cdea4e7f 100644 --- a/storage/connect/mysql-test/connect/r/xml2_mult.result +++ b/storage/connect/mysql-test/connect/r/xml2_mult.result @@ -5,9 +5,9 @@ SET NAMES utf8; # Testing expanded values # CREATE TABLE `bookstore` ( -`category` CHAR(16) NOT NULL FIELD_FORMAT='@', +`category` CHAR(16) NOT NULL XPATH='@', `title` VARCHAR(50) NOT NULL, -`lang` char(2) NOT NULL FIELD_FORMAT='title/@', +`lang` char(2) NOT NULL XPATH='title/@', `author` VARCHAR(24) NOT NULL, `year` INT(4) NOT NULL, `price` DOUBLE(8,2) NOT NULL) diff --git a/storage/connect/mysql-test/connect/r/xml2_zip.result b/storage/connect/mysql-test/connect/r/xml2_zip.result index f176149c53f..e743af32418 100644 --- a/storage/connect/mysql-test/connect/r/xml2_zip.result +++ b/storage/connect/mysql-test/connect/r/xml2_zip.result @@ -4,20 +4,20 @@ Warning 1105 No file name. Table will use t1.xml # Testing zipped XML tables # CREATE TABLE t1 ( -ISBN CHAR(13) NOT NULL FIELD_FORMAT='@', -LANG CHAR(2) NOT NULL FIELD_FORMAT='@', -SUBJECT CHAR(12) NOT NULL FIELD_FORMAT='@', -AUTHOR_FIRSTNAME CHAR(15) NOT NULL FIELD_FORMAT='AUTHOR/FIRSTNAME', -AUTHOR_LASTNAME CHAR(8) NOT NULL FIELD_FORMAT='AUTHOR/LASTNAME', -TRANSLATOR_PREFIX CHAR(24) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/@PREFIX', -TRANSLATOR_FIRSTNAME CHAR(6) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/FIRSTNAME', -TRANSLATOR_LASTNAME CHAR(6) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/LASTNAME', +ISBN CHAR(13) NOT NULL XPATH='@', +LANG CHAR(2) NOT NULL XPATH='@', +SUBJECT CHAR(12) NOT NULL XPATH='@', +AUTHOR_FIRSTNAME CHAR(15) NOT NULL XPATH='AUTHOR/FIRSTNAME', +AUTHOR_LASTNAME CHAR(8) NOT NULL XPATH='AUTHOR/LASTNAME', +TRANSLATOR_PREFIX CHAR(24) DEFAULT NULL XPATH='TRANSLATOR/@PREFIX', +TRANSLATOR_FIRSTNAME CHAR(6) DEFAULT NULL XPATH='TRANSLATOR/FIRSTNAME', +TRANSLATOR_LASTNAME CHAR(6) DEFAULT NULL XPATH='TRANSLATOR/LASTNAME', TITLE CHAR(30) NOT NULL, -PUBLISHER_NAME CHAR(15) NOT NULL FIELD_FORMAT='PUBLISHER/NAME', -PUBLISHER_PLACE CHAR(5) NOT NULL FIELD_FORMAT='PUBLISHER/PLACE', +PUBLISHER_NAME CHAR(15) NOT NULL XPATH='PUBLISHER/NAME', +PUBLISHER_PLACE CHAR(5) NOT NULL XPATH='PUBLISHER/PLACE', DATEPUB CHAR(4) NOT NULL ) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.zip' ZIPPED=YES -OPTION_LIST='entry=xsample2.xml,load=xsample2.xml,rownode=BOOK,xmlsup=libxml2,expand=1,mulnode=AUTHOR'; +OPTION_LIST='depth=0,entry=xsample2.xml,load=xsample2.xml,rownode=BOOK,xmlsup=libxml2,expand=1,mulnode=AUTHOR'; SELECT * FROM t1; ISBN 9782212090819 LANG fr @@ -69,7 +69,7 @@ PUBLISHER_PLACE Paris DATEPUB 2003 CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.zip' ZIPPED=YES -OPTION_LIST='xmlsup=libxml2'; +OPTION_LIST='depth=0,xmlsup=libxml2'; SELECT * FROM t2; ISBN 9782212090819 LANG fr diff --git a/storage/connect/mysql-test/connect/r/xml_html.result b/storage/connect/mysql-test/connect/r/xml_html.result index 4b984a49901..308c67ffc28 100644 --- a/storage/connect/mysql-test/connect/r/xml_html.result +++ b/storage/connect/mysql-test/connect/r/xml_html.result @@ -3,9 +3,9 @@ SET NAMES utf8; # Testing HTML like XML file # CREATE TABLE beers ( -`Name` CHAR(16) FIELD_FORMAT='brandName', -`Origin` CHAR(16) FIELD_FORMAT='origin', -`Description` CHAR(32) FIELD_FORMAT='details') +`Name` CHAR(16) XPATH='brandName', +`Origin` CHAR(16) XPATH='origin', +`Description` CHAR(32) XPATH='details') ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='beers.xml' TABNAME='table' OPTION_LIST='xmlsup=domdoc,rownode=tr,colnode=td'; SELECT * FROM beers; diff --git a/storage/connect/mysql-test/connect/r/xml_mult.result b/storage/connect/mysql-test/connect/r/xml_mult.result index 9922b40060c..427adf94313 100644 --- a/storage/connect/mysql-test/connect/r/xml_mult.result +++ b/storage/connect/mysql-test/connect/r/xml_mult.result @@ -3,9 +3,9 @@ SET NAMES utf8; # Testing expanded values # CREATE TABLE `bookstore` ( -`category` CHAR(16) NOT NULL FIELD_FORMAT='@', +`category` CHAR(16) NOT NULL XPATH='@', `title` VARCHAR(50) NOT NULL, -`lang` char(2) NOT NULL FIELD_FORMAT='title/@', +`lang` char(2) NOT NULL XPATH='title/@', `author` VARCHAR(24) NOT NULL, `year` INT(4) NOT NULL, `price` DOUBLE(8,2) NOT NULL) diff --git a/storage/connect/mysql-test/connect/r/xml_zip.result b/storage/connect/mysql-test/connect/r/xml_zip.result index f7790e4cfff..5f17249b390 100644 --- a/storage/connect/mysql-test/connect/r/xml_zip.result +++ b/storage/connect/mysql-test/connect/r/xml_zip.result @@ -2,20 +2,20 @@ # Testing zipped XML tables # CREATE TABLE t1 ( -ISBN CHAR(13) NOT NULL FIELD_FORMAT='@', -LANG CHAR(2) NOT NULL FIELD_FORMAT='@', -SUBJECT CHAR(12) NOT NULL FIELD_FORMAT='@', -AUTHOR_FIRSTNAME CHAR(15) NOT NULL FIELD_FORMAT='AUTHOR/FIRSTNAME', -AUTHOR_LASTNAME CHAR(8) NOT NULL FIELD_FORMAT='AUTHOR/LASTNAME', -TRANSLATOR_PREFIX CHAR(24) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/@PREFIX', -TRANSLATOR_FIRSTNAME CHAR(6) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/FIRSTNAME', -TRANSLATOR_LASTNAME CHAR(6) DEFAULT NULL FIELD_FORMAT='TRANSLATOR/LASTNAME', +ISBN CHAR(13) NOT NULL XPATH='@', +LANG CHAR(2) NOT NULL XPATH='@', +SUBJECT CHAR(12) NOT NULL XPATH='@', +AUTHOR_FIRSTNAME CHAR(15) NOT NULL XPATH='AUTHOR/FIRSTNAME', +AUTHOR_LASTNAME CHAR(8) NOT NULL XPATH='AUTHOR/LASTNAME', +TRANSLATOR_PREFIX CHAR(24) DEFAULT NULL XPATH='TRANSLATOR/@PREFIX', +TRANSLATOR_FIRSTNAME CHAR(6) DEFAULT NULL XPATH='TRANSLATOR/FIRSTNAME', +TRANSLATOR_LASTNAME CHAR(6) DEFAULT NULL XPATH='TRANSLATOR/LASTNAME', TITLE CHAR(30) NOT NULL, -PUBLISHER_NAME CHAR(15) NOT NULL FIELD_FORMAT='PUBLISHER/NAME', -PUBLISHER_PLACE CHAR(5) NOT NULL FIELD_FORMAT='PUBLISHER/PLACE', +PUBLISHER_NAME CHAR(15) NOT NULL XPATH='PUBLISHER/NAME', +PUBLISHER_PLACE CHAR(5) NOT NULL XPATH='PUBLISHER/PLACE', DATEPUB CHAR(4) NOT NULL ) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.zip' ZIPPED=YES -OPTION_LIST='entry=xsample2.xml,load=xsample2.xml,rownode=BOOK,xmlsup=domdoc,expand=1,mulnode=AUTHOR'; +OPTION_LIST='depth=0,entry=xsample2.xml,load=xsample2.xml,rownode=BOOK,xmlsup=domdoc,expand=1,mulnode=AUTHOR'; SELECT * FROM t1; ISBN 9782212090819 LANG fr @@ -67,7 +67,7 @@ PUBLISHER_PLACE Paris DATEPUB 2003 CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='xsample2.zip' ZIPPED=YES -OPTION_LIST='xmlsup=domdoc'; +OPTION_LIST='depth=0,xmlsup=domdoc'; SELECT * FROM t2; ISBN 9782212090819 LANG fr diff --git a/storage/connect/mysql-test/connect/r/zip.result b/storage/connect/mysql-test/connect/r/zip.result index c81546a4689..c696252ca43 100644 --- a/storage/connect/mysql-test/connect/r/zip.result +++ b/storage/connect/mysql-test/connect/r/zip.result @@ -171,32 +171,32 @@ DROP TABLE t1,t2,t3,t4; # CREATE TABLE t1 ( _id INT(2) NOT NULL, -name_first CHAR(9) NOT NULL FIELD_FORMAT='$.name.first', -name_aka CHAR(4) DEFAULT NULL FIELD_FORMAT='$.name.aka', -name_last CHAR(10) NOT NULL FIELD_FORMAT='$.name.last', +name_first CHAR(9) NOT NULL JPATH='$.name.first', +name_aka CHAR(4) DEFAULT NULL JPATH='$.name.aka', +name_last CHAR(10) NOT NULL JPATH='$.name.last', title CHAR(12) DEFAULT NULL, birth CHAR(20) DEFAULT NULL, death CHAR(20) DEFAULT NULL, -contribs CHAR(7) NOT NULL FIELD_FORMAT='$.contribs', -awards_award CHAR(42) DEFAULT NULL FIELD_FORMAT='$.awards.award', -awards_year CHAR(4) DEFAULT NULL FIELD_FORMAT='$.awards.year', -awards_by CHAR(38) DEFAULT NULL FIELD_FORMAT='$.awards.by' +contribs CHAR(50) NOT NULL JPATH='$.contribs', +awards_award CHAR(42) DEFAULT NULL JPATH='$.awards.award', +awards_year CHAR(4) DEFAULT NULL JPATH='$.awards.year', +awards_by CHAR(38) DEFAULT NULL JPATH='$.awards.by' ) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bios.zip' OPTION_LIST='ENTRY=bios.json,LOAD=bios.json' ZIPPED=YES; SELECT * FROM t1; _id name_first name_aka name_last title birth death contribs awards_award awards_year awards_by -1 John NULL Backus NULL 1924-12-03T05:00:00Z 2007-03-17T04:00:00Z Fortran W.W. McDowell Award 1967 IEEE Computer Society -2 John NULL McCarthy NULL 1927-09-04T04:00:00Z 2011-12-24T05:00:00Z Lisp Turing Award 1971 ACM -3 Grace NULL Hopper Rear Admiral 1906-12-09T05:00:00Z 1992-01-01T05:00:00Z UNIVAC Computer Sciences Man of the Year 1969 Data Processing Management Association -4 Kristen NULL Nygaard NULL 1926-08-27T04:00:00Z 2002-08-10T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association -5 Ole-Johan NULL Dahl NULL 1931-10-12T04:00:00Z 2002-06-29T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association +1 John NULL Backus NULL 1924-12-03T05:00:00Z 2007-03-17T04:00:00Z Fortran, ALGOL, Backus-Naur Form, FP W.W. McDowell Award 1967 IEEE Computer Society +2 John NULL McCarthy NULL 1927-09-04T04:00:00Z 2011-12-24T05:00:00Z Lisp, Artificial Intelligence, ALGOL Turing Award 1971 ACM +3 Grace NULL Hopper Rear Admiral 1906-12-09T05:00:00Z 1992-01-01T05:00:00Z UNIVAC, compiler, FLOW-MATIC, COBOL Computer Sciences Man of the Year 1969 Data Processing Management Association +4 Kristen NULL Nygaard NULL 1926-08-27T04:00:00Z 2002-08-10T04:00:00Z OOP, Simula Rosing Prize 1999 Norwegian Data Association +5 Ole-Johan NULL Dahl NULL 1931-10-12T04:00:00Z 2002-06-29T04:00:00Z OOP, Simula Rosing Prize 1999 Norwegian Data Association 6 Guido NULL van Rossum NULL 1956-01-31T05:00:00Z NULL Python Award for the Advancement of Free Software 2001 Free Software Foundation -7 Dennis NULL Ritchie NULL 1941-09-09T04:00:00Z 2011-10-12T04:00:00Z UNIX Turing Award 1983 ACM +7 Dennis NULL Ritchie NULL 1941-09-09T04:00:00Z 2011-10-12T04:00:00Z UNIX, C Turing Award 1983 ACM 8 Yukihiro Matz Matsumoto NULL 1965-04-14T04:00:00Z NULL Ruby Award for the Advancement of Free Software 2011 Free Software Foundation 9 James NULL Gosling NULL 1955-05-19T04:00:00Z NULL Java The Economist Innovation Award 2002 The Economist 10 Martin NULL Odersky NULL NULL NULL Scala NULL NULL NULL CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bios.zip' ZIPPED=1 -OPTION_LIST='LEVEL=5'; +OPTION_LIST='DEPTH=5'; SELECT * FROM t2; _id name_first name_aka name_last title birth death contribs awards_award awards_year awards_by 1 John NULL Backus NULL 1924-12-03T05:00:00Z 2007-03-17T04:00:00Z Fortran W.W. McDowell Award 1967 IEEE Computer Society @@ -211,16 +211,16 @@ _id name_first name_aka name_last title birth death contribs awards_award awards 10 Martin NULL Odersky NULL NULL NULL Scala NULL NULL NULL CREATE TABLE t3 ( _id INT(2) NOT NULL, -firstname CHAR(9) NOT NULL FIELD_FORMAT='$.name.first', -aka CHAR(4) DEFAULT NULL FIELD_FORMAT='$.name.aka', -lastname CHAR(10) NOT NULL FIELD_FORMAT='$.name.last', +firstname CHAR(9) NOT NULL JPATH='$.name.first', +aka CHAR(4) DEFAULT NULL JPATH='$.name.aka', +lastname CHAR(10) NOT NULL JPATH='$.name.last', title CHAR(12) DEFAULT NULL, birth date DEFAULT NULL date_format="YYYY-DD-MM'T'hh:mm:ss'Z'", death date DEFAULT NULL date_format="YYYY-DD-MM'T'hh:mm:ss'Z'", -contribs CHAR(64) NOT NULL FIELD_FORMAT='$.contribs.[", "]', -award CHAR(42) DEFAULT NULL FIELD_FORMAT='$.awards[*].award', -year CHAR(4) DEFAULT NULL FIELD_FORMAT='$.awards[*].year', -`by` CHAR(38) DEFAULT NULL FIELD_FORMAT='$.awards[*].by' +contribs CHAR(64) NOT NULL JPATH='$.contribs.[", "]', +award CHAR(42) DEFAULT NULL JPATH='$.awards[*].award', +year CHAR(4) DEFAULT NULL JPATH='$.awards[*].year', +`by` CHAR(38) DEFAULT NULL JPATH='$.awards[*].by' ) ENGINE=CONNECT TABLE_TYPE='json' FILE_NAME='bios.zip' ZIPPED=YES; SELECT * FROM t3 WHERE _id = 1; _id firstname aka lastname title birth death contribs award year by |