Date: 2019-09-13 16:35:53 +0200
From: @yzchang
To: SQL devs <>
Version: 11.33.11 (Apr2019-SP1)
CC: @PedroTadim, @yzchang

Last updated: 2019-11-28 10:00:05 +0100

Comment 27285

Date: 2019-09-13 16:35:53 +0200
From: @yzchang

The json_loader() function (from: https://www.monetdb.org/blog/monetdbpython-loader-functions) does not create correct column type for STRINGs. STRINGs are declared as "CHAR" columns. Such tables cannot be dump-restored using 'msqldump', because the dumpt table has incorrect schema.

To reproduce:

$ cat test.json
{"col1": ["apple", "peer"], "col2":["orange", "banana nananana"]}

$ mserver5 --set embedded_py=3

$ cat test.sql
DROP TABLE if exists tbl;
DROP LOADER if exists json_loader;

CREATE LOADER json_loader(filename STRING) LANGUAGE PYTHON {
import json
f = open(filename)
_emit.emit(json.load(f))
f.close()
};

CREATE TABLE tbl FROM LOADER json_loader('/Users/jennie/tmp/test.json');
SELECT * FROM tbl;

$ mclient < test.sql

$ msqldump -t tbl > tbl.sql

$ cat tbl.sql
-- msqldump version 11.33.8 (hg id: 16667eb75b08) dump table tbl
-- server: MonetDB v11.33.8 (hg id: 16667eb75b08), 'demo'
-- Fri Sep 13 16:33:58 2019
CREATE TABLE "sys"."tbl" (
"col1" CHAR,
"col2" CHAR
);
COPY 2 RECORDS INTO "sys"."tbl" FROM stdin USING DELIMITERS E'\t',E'\n','"';
"apple" "orange"
"peer" "banana nananana"

$ mclient -s 'drop table tbl;'
operation successful

$ mclient < tbl.sql
operation successful
Failed to import table 'tbl', line 1 field col1 'char(1)' expected in 'apple'

Comment 27286

Date: 2019-09-13 16:36:56 +0200
From: @yzchang

Created attachment 631
test.json

sample JSON data

Attached file: test.json (text/plain, 66 bytes)
Description: test.json

Comment 27287

Date: 2019-09-13 16:37:34 +0200
From: @yzchang

Created attachment 632
test.sql

The query

Attached file: test.sql (text/plain, 302 bytes)
Description: test.sql

Comment 27288

Date: 2019-09-16 10:57:57 +0200
From: MonetDB Mercurial Repository <>

Changeset 1dd5b7a992e7 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1dd5b7a992e7

Changeset description:

Added test and fix for bug #6759, (i.e. when creating a string column from the loader, use clob type instead of char)