summaryrefslogtreecommitdiff
path: root/storage/connect/mysql-test/connect/r/json.result
diff options
context:
space:
mode:
Diffstat (limited to 'storage/connect/mysql-test/connect/r/json.result')
-rw-r--r--storage/connect/mysql-test/connect/r/json.result116
1 files changed, 80 insertions, 36 deletions
diff --git a/storage/connect/mysql-test/connect/r/json.result b/storage/connect/mysql-test/connect/r/json.result
index 094bb669d18..acb74c38e26 100644
--- a/storage/connect/mysql-test/connect/r/json.result
+++ b/storage/connect/mysql-test/connect/r/json.result
@@ -12,10 +12,10 @@ TRANSLATION CHAR(32),
TRANSLATOR CHAR(80),
PUBLISHER CHAR(32),
DATEPUB int(4)
-) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
ISBN LANG SUBJECT AUTHOR TITLE TRANSLATION TRANSLATOR PUBLISHER DATEPUB
-9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999
+9782212090819 fr applications Jean-Christophe Bernadac 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;
#
@@ -34,10 +34,10 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
ISBN Language Subject Authors Title Translation Translator Publisher Location Year
-9782212090819 fr applications 2 Construire une application XML Eyrolles Paris 1999
+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;
#
@@ -57,10 +57,10 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year
-9782212090819 fr applications Jean-Christophe and François Bernadac and Knab Construire une application XML Eyrolles Paris 1999
+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;
#
@@ -80,32 +80,32 @@ Publisher CHAR(20) FIELD_FORMAT='PUBLISHER:NAME',
Location CHAR(16) FIELD_FORMAT='PUBLISHER:PLACE',
Year int(4) FIELD_FORMAT='DATEPUB'
)
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
ISBN Language Subject AuthorFN AuthorLN Title Translation Translator Publisher Location Year
-9782212090819 fr applications Jean-Christophe Bernadac Construire une application XML Eyrolles Paris 1999
-9782212090819 fr applications François Knab Construire une application XML Eyrolles Paris 1999
+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 Eyrolles Paris 1999
-9782212090819 fr applications Philippe Knab Construire une application XML Eyrolles Paris 1999
+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=JSON FILE_NAME='biblio.jsn' OPTION_LIST='Object=[2]:AUTHOR';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=[1]:AUTHOR';
SELECT * FROM t2;
FIRSTNAME LASTNAME
William J. Pardi
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 Eyrolles Paris 1999
-9782212090819 fr applications Philippe Knab Construire une application XML Eyrolles Paris 1999
+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;
@@ -117,7 +117,7 @@ CREATE TABLE t1
(
line char(255)
)
-ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.jsn';
+ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json';
SELECT * FROM t1;
line
[
@@ -178,7 +178,7 @@ WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK::EXPENSE:["+"]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK::EXPENSE:[+]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t1;
WHO WEEK WHAT AMOUNT
Joe 3 Beer+Food+Food+Car 69.00
@@ -192,26 +192,70 @@ Janet 4 Car 17.00
Janet 5 Beer+Car+Beer+Food 57.00
DROP TABLE t1;
#
-# Cannot be fully expanded
+# Now it can be fully expanded
#
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='WEEK:[X]:NUMBER',
WHAT CHAR(32) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[X]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t1;
-ERROR HY000: Got error 174 'Cannot expand more than one array' from CONNECT
+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 FIELD_FORMAT='WEEK:[", "]:NUMBER',
+SUMS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[+]:AMOUNT',
+SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[+]:AMOUNT',
+AVGS CHAR(64) NOT NULL FIELD_FORMAT='WEEK:["+"]:EXPENSE:[!]:AMOUNT',
+SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[+]:EXPENSE:[!]:AMOUNT',
+AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[+]:AMOUNT',
+AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[!]:AMOUNT',
+AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='WEEK:[!]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
+SELECT * FROM t1;
+WHO WEEKS SUMS SUM AVGS SUMAVG AVGSUM AVGAVG AVERAGE
+Joe 3, 4, 5 69.00+83.00+26.00 178.00 17.25+16.60+13.00 46.85 59.33 15.62 16.18
+Beth 3, 4, 5 16.00+32.00+32.00 80.00 16.00+16.00+16.00 48.00 26.67 16.00 16.00
+Janet 3, 4, 5 55.00+17.00+57.00 129.00 18.33+17.00+14.25 49.58 43.00 16.53 16.12
DROP TABLE t1;
#
# Expand expense in 3 one week tables
#
CREATE TABLE t2 (
WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+WEEK INT(2) FIELD_FORMAT='WEEK:[0]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[0]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t2;
WHO WEEK WHAT AMOUNT
Joe 3 Beer 18.00
@@ -224,10 +268,10 @@ Janet 3 Food 18.00
Janet 3 Beer 18.00
CREATE TABLE t3 (
WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+WEEK INT(2) FIELD_FORMAT='WEEK:[1]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[1]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t3;
WHO WEEK WHAT AMOUNT
Joe 4 Beer 19.00
@@ -240,10 +284,10 @@ Beth 4 Beer 15.00
Janet 4 Car 17.00
CREATE TABLE t4 (
WHO CHAR(12),
-WEEK INT(2) FIELD_FORMAT='WEEK:[3]:NUMBER',
-WHAT CHAR(32) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:WHAT',
-AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[3]:EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.jsn';
+WEEK INT(2) FIELD_FORMAT='WEEK:[2]:NUMBER',
+WHAT CHAR(32) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:WHAT',
+AMOUNT DOUBLE(8,2) FIELD_FORMAT='WEEK:[2]:EXPENSE:[X]:AMOUNT')
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t4;
WHO WEEK WHAT AMOUNT
Joe 5 Beer 14.00
@@ -298,7 +342,7 @@ WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json';
SELECT * FROM t2;
WHO WEEK WHAT AMOUNT
Joe 3 Beer 18.00
@@ -314,7 +358,7 @@ WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json';
SELECT * FROM t3;
WHO WEEK WHAT AMOUNT
Joe 4 Beer 19.00
@@ -330,7 +374,7 @@ WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json';
SELECT * FROM t4;
WHO WEEK WHAT AMOUNT
Joe 5 Beer 14.00
@@ -349,7 +393,7 @@ WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.jsn' MULTIPLE=1;
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1;
SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT;
WHO WEEK WHAT AMOUNT
Beth 3 Beer 16.00
@@ -385,7 +429,7 @@ WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='EXPENSE:[X]:WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='EXPENSE:[X]:AMOUNT')
-ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.jsn';
+ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json';
ALTER TABLE t1
PARTITION BY LIST COLUMNS(WEEK) (
PARTITION `3` VALUES IN(3),
View Lorry Controller Status