Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)

Hi

2016-06-29 1:51 GMT+02:00 Stefan Keller <sfkeller@gmail.com>:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

:Stefan

[1]
https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2]
http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

Can be nice if we can write API with respects ANSI SQL. The main problem is
query language - we doesn't support JSONPath. But if we have a own regular
expression library, probably we can have i own implementation of JSONPath

Pavel

On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

I' very dissapointed with this.

:Stefan

[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1 July 2016 at 17:52, Oleg Bartunov <obartunov@gmail.com> wrote:

On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

I' very dissapointed with this.

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2016-07-01 19:06:06 +0800, Craig Ringer wrote:

On 1 July 2016 at 17:52, Oleg Bartunov <obartunov@gmail.com> wrote:

On Wed, Jun 29, 2016 at 2:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

I' very dissapointed with this.

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

Especially if nobody really lobbies on part of postgresql.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/1/16 7:06 AM, Craig Ringer wrote:

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

The SQL/JSON thing is pretty much straight from Oracle and Microsoft
(and notably completely different from DB2).

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/01/2016 07:31 PM, Andres Freund wrote:

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

Especially if nobody really lobbies on part of postgresql.

Has any of the major PostgreSQL companies looked into sending members to
the ISO committee? It would be nice if we could be represented.

Andreas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi

2016-07-01 20:46 GMT+02:00 Peter Eisentraut <
peter.eisentraut@2ndquadrant.com>:

On 7/1/16 7:06 AM, Craig Ringer wrote:

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

The SQL/JSON thing is pretty much straight from Oracle and Microsoft (and
notably completely different from DB2).

I checked standard, and it looks like not a significant problem to
implement it in Postgres. The implementation should be similar to XML -
requires parser support. We doesn't use a identifier of important SQL/JSON
functions: JSON_EXISTS, JSON_VALUE, JSON_QUERY, JSON_TABLE, JSON_ARRAY. The
problem should be with function JSON_OBJECT - standard is based on variadic
function of pairs (name, value), but it should be solvable, because our
first argument is a array, what is not possible in standard. So ANSI SQL
conform implementation of JSON support is still possible in Postgres.

Regards

Pavel

Andreas Karlsson <andreas@proxel.se> writes:

Has any of the major PostgreSQL companies looked into sending members to
the ISO committee? It would be nice if we could be represented.

Peter E. had observer status at one point, don't know if he still does.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

:Stefan

[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

Thanks for these pointers. On the "standards under development"
page[1]http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&amp;development=on, I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60
"Close of voting/ comment period". But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like
MATCH_RECOGNIZE in Oracle?

[1]: http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&amp;development=on

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/4/16 12:44 AM, Thomas Munro wrote:

ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like
MATCH_RECOGNIZE in Oracle?

That's exactly what that is.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/1/16 10:42 PM, Andreas Karlsson wrote:

On 07/01/2016 07:31 PM, Andres Freund wrote:

Yeah, but since when has the SQL standard adopted any existing
implementation's spelling of a feature? It seems to be politically
impossible.

Especially if nobody really lobbies on part of postgresql.

Has any of the major PostgreSQL companies looked into sending members to
the ISO committee? It would be nice if we could be represented.

It has been considered from time to time, but it would require some
significant time investment from people whose time is better spent
elsewhere.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 7/2/16 12:40 AM, Pavel Stehule wrote:

So ANSI SQL conform implementation of JSON support is still possible in
Postgres.

It is possible, but in this case I think it's not worth it. Our
built-in stuff is significantly simpler and more powerful and already in
widespread use.

It is worth keeping an eye on it and not create gratuitous conflicts.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-07-06 5:17 GMT+02:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com

:

On 7/2/16 12:40 AM, Pavel Stehule wrote:

So ANSI SQL conform implementation of JSON support is still possible in
Postgres.

It is possible, but in this case I think it's not worth it. Our built-in
stuff is significantly simpler and more powerful and already in widespread
use.

I can't to agree. JSONPath is little bit powerful than our JSON path
expression. JSON_TABLE is pretty good function for extracting values from
JSON (note: I missing XML_TABLE too much).

More - Postgres is not famous due not respecting standard.

Regards

Pavel

Thomas

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

... But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like

Peter surely would know: https://www.jacobs-university.de/directory/pbaumann

:Stefan

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

:Stefan

[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

Thanks for these pointers. On the "standards under development"
page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60
"Close of voting/ comment period". But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like
MATCH_RECOGNIZE in Oracle?

[1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&amp;development=on

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Peter, hi all,

I wrote 2016-07-06 13:19 GMT+02:00:

...
2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

... But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? ...

@Peter B. Can you clarify the stage?

Regarding the differences of array functions of the ISO proposal, I
have no clue why the working group did not follow the implementation
of PostgreSQL.

@Peter B. You know PostgreSQL well. Can you explain?

:Stefan

P.S. 2016-07-02 17:11 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter E. had observer status at one point, don't know if he still does.

@Peter E.: Do you still have observer status at the ISO committee?

2016-07-06 13:19 GMT+02:00 Stefan Keller <sfkeller@gmail.com>:

Thomas

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

... But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like

Peter surely would know: https://www.jacobs-university.de/directory/pbaumann

:Stefan

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

:Stefan

[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

Thanks for these pointers. On the "standards under development"
page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60
"Close of voting/ comment period". But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like
MATCH_RECOGNIZE in Oracle?

[1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&amp;development=on

--
Thomas Munro
http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Stefan,

On 07/09/2016 04:03 PM, Stefan Keller wrote:

Hi Peter, hi all,

I wrote 2016-07-06 13:19 GMT+02:00:

...
2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

... But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? ...

@Peter B. Can you clarify the stage?

there were comments from the national delegations which have to be worked
through by providing a draft that implements changes where considered
applicable, and justifying when not implemented. This is in accordance with ISO
"bureaucracy", if you will, which defines a fine-grain, therefore transparent,
process. Personally, I find this many-eyes principle very fruitful.

Regarding the differences of array functions of the ISO proposal, I
have no clue why the working group did not follow the implementation
of PostgreSQL.

@Peter B. You know PostgreSQL well. Can you explain?

the WG has discussed all relevant approaches and decided in favour of the one it
found most suitable.

cheers,
Peter

:Stefan

P.S. 2016-07-02 17:11 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter E. had observer status at one point, don't know if he still does.

@Peter E.: Do you still have observer status at the ISO committee?

2016-07-06 13:19 GMT+02:00 Stefan Keller <sfkeller@gmail.com>:

Thomas

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

... But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like

Peter surely would know: https://www.jacobs-university.de/directory/pbaumann

:Stefan

2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.munro@enterprisedb.com>:

On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkeller@gmail.com> wrote:

Hi,

FYI: I'd just like to point you to following two forthcoming standard
parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on
"Multi-Dimensional Arrays" (SQL/MDA).

They define there some things different as already in PG. See also
Peter Baumann's slides [1] and e.g. [2]

:Stefan

[1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql
[2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

Thanks for these pointers. On the "standards under development"
page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60
"Close of voting/ comment period". But ISO/IEC CD 9075-15
(Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to
Working Group". Is that how they say "returned with feedback"?
ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage
30.60. Does anyone know what that one is about? Maybe something like
MATCH_RECOGNIZE in Oracle?

[1] http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342&amp;development=on

--
Thomas Munro
http://www.enterprisedb.com

--
Dr. Peter Baumann
- Professor of Computer Science, Jacobs University Bremen
www.faculty.jacobs-university.de/pbaumann
mail: p.baumann@jacobs-university.de
tel: +49-421-200-3178, fax: +49-421-200-493178
- Executive Director, rasdaman GmbH Bremen (HRB 26793)
www.rasdaman.com, mail: baumann@rasdaman.com
tel: 0800-rasdaman, fax: 0800-rasdafax, mobile: +49-173-5837882
"Si forte in alienas manus oberraverit hec peregrina epistola incertis ventis
dimissa, sed Deo commendata, precamur ut ei reddatur cui soli destinata, nec
preripiat quisquam non sibi parata." (mail disclaimer, AD 1083)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers