Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Duplicate
-
None
-
None
Description
On this page:
https://mariadb.com/kb/en/mariadb/connect-json-table-type/
Even after fixing the biblio3.json file (as described in https://mariadb.atlassian.net/browse/MDEV-7665), examples #2 and #3 have an issue with both 10.0.16 and 10.0.17.
Both CREATE TABLE commands run fine.
However, both SELECT statements return slightly different results because there are 2 warnings:
=============
= Example #2: =
=============
mysql> create table jsampall ( |
-> ISBN char(15), |
-> Language char(2) field_format='LANG', |
-> Subject char(32) field_format='SUBJECT', |
-> Author char(128) field_format='AUTHOR:[" and "]', |
-> Title char(32) field_format='TITLE', |
-> Translation char(32) field_format='TRANSLATOR :PREFIX', |
-> Translator char(80) field_format='TRANSLATOR', |
-> Publisher char(20) field_format='PUBLISHER:NAME', |
-> Location char(16) field_format='PUBLISHER:PLACE', |
-> Year int(4) field_format='DATEPUB') |
-> engine=CONNECT table_type=JSON File_name='biblio3.json'; |
Query OK, 0 rows affected (0.01 sec) |
mysql> select title, author, publisher, location from jsampall; |
+--------------------------------+-----------------------------------+-----------+----------+ |
| title | author | publisher | location |
|
+--------------------------------+-----------------------------------+-----------+----------+ |
| Construire une application XML | Jean-Christophe Bernadac and Fran | Eyrolles | Paris | |
| XML en Action | William J. Pardi | | | |
+--------------------------------+-----------------------------------+-----------+----------+ |
2 rows in set, 2 warnings (0.00 sec) |
|
|
mysql> show warnings;
|
+---------+------+--------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+--------------------------------------------------------------------------------------------+ |
| Warning | 1366 | Incorrect string value: '\xE7ois K...' for column 'Author' at row 1 | |
| Warning | 1105 | Out of range value Jean-Christophe Bernadac and Frantois Knab for column 'Author' at row 1 | |
+---------+------+--------------------------------------------------------------------------------------------+ |
2 rows in set (0.00 sec) |
=============
= Example #3: =
=============
Here, the SELECT actually needs inferred as it is omitted (that needs added too, so please use the SELECT from below to update the manual).
mysql> create table jsampex ( |
-> ISBN char(15), |
-> Title char(32) field_format='TITLE', |
-> AuthorFN char(128) field_format='AUTHOR:[X]:FIRSTNAME', |
-> AuthorLN char(128) field_format='AUTHOR:[X]:LASTNAME', |
-> Year int(4) field_format='DATEPUB') |
-> engine=CONNECT table_type=JSON File_name='biblio3.json'; |
Query OK, 0 rows affected (0.01 sec) |
mysql> SELECT ISBN, Title, AuthorFN, AuthorLN, Year FROM jsampex; |
+---------------+--------------------------------+-----------------+----------+------+ |
| ISBN | Title | AuthorFN | AuthorLN | Year | |
+---------------+--------------------------------+-----------------+----------+------+ |
| 9782212090819 | Construire une application XML | Jean-Christophe | Bernadac | 1999 |
|
| 9782212090819 | Construire une application XML | Fran | Knab | 1999 |
|
| 9782840825685 | XML en Action | William J. | Pardi | 0 | |
+---------------+--------------------------------+-----------------+----------+------+ |
3 rows in set, 2 warnings (0.00 sec) |
|
|
mysql> show warnings;
|
+---------+------+------------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+------------------------------------------------------------------+ |
| Warning | 1366 | Incorrect string value: '\xE7ois' for column 'AuthorFN' at row 2 | |
| Warning | 1105 | Out of range value Frantois for column 'AuthorFN' at row 2 | |
+---------+------+------------------------------------------------------------------+ |
2 rows in set (0.00 sec) |
Both report "Incorrect string value: '\xE7ois..." so I suspect the cause is the same in each case.
But this needs fixed.