Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Python JSON loader creates invalid data type for strings #6759

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Python JSON loader creates invalid data type for strings #6759

monetdb-team opened this issue Nov 30, 2020 · 0 comments


Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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: 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;

import json
f = open(filename)

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

$ 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

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

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 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant