Modern SQL: Evolution of a dinosaur @MarkusWinand @ModernSQL - - PowerPoint PPT Presentation
Modern SQL: Evolution of a dinosaur @MarkusWinand @ModernSQL http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf Modern SQL: Evolution of a dinosaur @MarkusWinand @ModernSQL
http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf
http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf
Relational Data Model
Relational Data Model
Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png
Relational Data Model
A B C
Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png
Relational Data Model
A B C
Relational Data Model
processing purposes
A B C
Relational Data Model
processing purposes
A B C C D B E
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E A B C D E
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E
A B E
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E A B E
C D E
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E A B E C D E
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data model is like saying that an intercontinental ballistic missile is merely an “extended interpretation” of a spear.
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data model is like saying that an intercontinental ballistic missile is merely an “extended interpretation” of a spear. With SQL/99 you can get the best of both worlds and
It’s up to the database practitioners to do the right thing.
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
Relational Model?
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
I was as confused as anyone else
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
I was as confused as anyone else
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
A
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
A B
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
A B
C
C D C D C D
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
A B C D
{x: , y: }
{x: , y: }
{x: , y: }
C D C D C D
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Non-Relational Operations
queries that process their own output
Chris Date
Date on Database: Writings 2000-2006
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
CREATE TABLE t ( id INTEGER, parent INTEGER, )
CREATE TABLE t ( id INTEGER, parent INTEGER, )
CREATE TABLE t ( id INTEGER, parent INTEGER, )
SELECT t.id, t.parent FROM t WHERE t.id = ?
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.2
MariaDB
8.0
MySQL
8.4
PostgreSQL
3.8.3
[0]
SQLite
7.0
DB2 LUW
11gR2
Oracle
2005
SQL Server
[0]Only for top-level SELECT statements
http://www.acm.org:80/sigmod/record/issues/0206/standard.pdf (via Wayback machine)
Schemaless
documents in a single column
Schemaless
documents in a single column
Later:
Analytical
window functions
Schemaless
documents in a single column
Later:
Analytical
window functions
Schemaless
documents in a single column
Later:
Cockroach DB, VoltDB
Later:
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
+10
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
, SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.2
MariaDB
8.0
MySQL
8.4
PostgreSQL
3.25.0
SQLite
7.0
DB2 LUW
8i
Oracle
2005[0] 2012
SQL Server
[0]No framing
http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
id a1 42 foo 43 bar [ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
Bind Parameter
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SQL/JSON Path
select elements from a JSON document
SQL standard
Bind Parameter
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SQL/JSON Path
select elements from a JSON document
SQL standard
Bind Parameter
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SQL/JSON Path
select elements from a JSON document
SQL standard
Bind Parameter
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r INSERT INTO target_table
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
SELECT * FROM JSON_TABLE ( ? , '$[*]' COLUMNS ( id INT PATH '$.id' , a1 VARCHAR(…) PATH '$.a1' ) ) r INSERT INTO target_table
[ { "id": 42, "a1": "foo" }, { "id": 43, "a1": "bar" } ]
id a1 42 foo 43 bar
Session tip: How Well Do Relational Database Engines Support JSON? Today 15:30!
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
MariaDB
8.0
MySQL PostgreSQL SQLite DB2 LUW
12cR1
Oracle
2016[0]
SQL Server
[0]OPENJSON provides similar functionality
Time 30 minutes
Example: Logfile
Example: Logfile
Time 30 minutes
Session 1 Session 2 Session 3 Session 4
Time 30 minutes
SELECT count(*) sessions, avg(duration) avg_duration FROM (SELECT MAX(ts) - MIN(ts) duration FROM (SELECT ts, SUM(grp_start) OVER(ORDER BY ts) session_no FROM (SELECT ts, CASE WHEN ts >= LAG( ts, 1, DATE'1900-01-01' ) OVER( ORDER BY ts ) + INTERVAL '30' minute THEN 1 END grp_start FROM log ) tagged ) numbered GROUP BY session_no ) grouped
Time 30 minutes
SELECT count(*) sessions, avg(duration) avg_duration FROM (SELECT MAX(ts) - MIN(ts) duration FROM (SELECT ts, SUM(grp_start) OVER(ORDER BY ts) session_no FROM (SELECT ts, CASE WHEN ts >= LAG( ts, 1, DATE'1900-01-01' ) OVER( ORDER BY ts ) + INTERVAL '30' minute THEN 1 END grp_start FROM log ) tagged ) numbered GROUP BY session_no ) grouped
Time 30 minutes
Start-of-group tags
SELECT count(*) sessions, avg(duration) avg_duration FROM (SELECT MAX(ts) - MIN(ts) duration FROM (SELECT ts, SUM(grp_start) OVER(ORDER BY ts) session_no FROM (SELECT ts, CASE WHEN ts >= LAG( ts, 1, DATE'1900-01-01' ) OVER( ORDER BY ts ) + INTERVAL '30' minute THEN 1 END grp_start FROM log ) tagged ) numbered GROUP BY session_no ) grouped
Time 30 minutes
Start-of-group tags
SELECT count(*) sessions, avg(duration) avg_duration FROM (SELECT MAX(ts) - MIN(ts) duration FROM (SELECT ts, SUM(grp_start) OVER(ORDER BY ts) session_no FROM (SELECT ts, CASE WHEN ts >= LAG( ts, 1, DATE'1900-01-01' ) OVER( ORDER BY ts ) + INTERVAL '30' minute THEN 1 END grp_start FROM log ) tagged ) numbered GROUP BY session_no ) grouped
Time 30 minutes
number sessions
SELECT count(*) sessions, avg(duration) avg_duration FROM (SELECT MAX(ts) - MIN(ts) duration FROM (SELECT ts, SUM(grp_start) OVER(ORDER BY ts) session_no FROM (SELECT ts, CASE WHEN ts >= LAG( ts, 1, DATE'1900-01-01' ) OVER( ORDER BY ts ) + INTERVAL '30' minute THEN 1 END grp_start FROM log ) tagged ) numbered GROUP BY session_no ) grouped
Time 30 minutes
number sessions 2222 2 33 3 44 4 2 3 4 1
any character non-white space
{
Rail track diagram by regexper.com
any character non-white space
{
Rail track diagram by regexper.com
any character non-white space any character non-white space
{
{
Rail track diagram by regexper.com
SELECT COUNT(*) sessions, AVG(duration) avg_duration FROM log MATCH_RECOGNIZE( ORDER BY ts MEASURES LAST(ts) - FIRST(ts) AS duration ONE ROW PER MATCH PATTERN ( any cont* ) DEFINE cont AS ts < PREV(ts) + INTERVAL '30' MINUTE ) t
Time 30 minutes
Oracle doesn’t support avg on intervals — query doesn’t work as shown
SELECT COUNT(*) sessions, AVG(duration) avg_duration FROM log MATCH_RECOGNIZE( ORDER BY ts MEASURES LAST(ts) - FIRST(ts) AS duration ONE ROW PER MATCH PATTERN ( any cont* ) DEFINE cont AS ts < PREV(ts) + INTERVAL '30' MINUTE ) t
Time 30 minutes
define continued
Oracle doesn’t support avg on intervals — query doesn’t work as shown
SELECT COUNT(*) sessions, AVG(duration) avg_duration FROM log MATCH_RECOGNIZE( ORDER BY ts MEASURES LAST(ts) - FIRST(ts) AS duration ONE ROW PER MATCH PATTERN ( any cont* ) DEFINE cont AS ts < PREV(ts) + INTERVAL '30' MINUTE ) t
Time 30 minutes
any number
rows
Oracle doesn’t support avg on intervals — query doesn’t work as shown
SELECT COUNT(*) sessions, AVG(duration) avg_duration FROM log MATCH_RECOGNIZE( ORDER BY ts MEASURES LAST(ts) - FIRST(ts) AS duration ONE ROW PER MATCH PATTERN ( any cont* ) DEFINE cont AS ts < PREV(ts) + INTERVAL '30' MINUTE ) t
Time 30 minutes
Very much like GROUP BY
Oracle doesn’t support avg on intervals — query doesn’t work as shown
SELECT COUNT(*) sessions, AVG(duration) avg_duration FROM log MATCH_RECOGNIZE( ORDER BY ts MEASURES LAST(ts) - FIRST(ts) AS duration ONE ROW PER MATCH PATTERN ( any cont* ) DEFINE cont AS ts < PREV(ts) + INTERVAL '30' MINUTE ) t
Time 30 minutes
Very much like SELECT
Oracle doesn’t support avg on intervals — query doesn’t work as shown
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
MariaDB MySQL PostgreSQL SQLite DB2 LUW
12cR1
Oracle SQL Server
http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
Application Versioning
happen in the real world?
http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
Application Versioning
happen in the real world?
New syntax (excerpt)
UPDATE and DELETE
constraints & PRIMARY KEYS
OVERLAPS in WHERE,HAVING,…
System Versioning
(almost) transparent
about something Application Versioning
happen in the real world?
New syntax (excerpt)
UPDATE and DELETE
constraints & PRIMARY KEYS
OVERLAPS in WHERE,HAVING,…
System Versioning
(almost) transparent
about something Application Versioning
happen in the real world?
New syntax (excerpt)
UPDATE and DELETE
constraints & PRIMARY KEYS
OVERLAPS in WHERE,HAVING,… Transparent changes, new syntax for queries
use the system time automatically
SYSTEM_TIME AS OF
CREATE TABLE t ( ... , from TIMESTAMP(9) GENERATED ALWAYS AS ROW START , till TIMESTAMP(9) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (from, till) ) WITH SYSTEM VERSIONING
INSERT INTO t (id, data) VALUES (1 , 'X' )
id data from till 1 X 10:00
INSERT INTO t (id, data) VALUES (1 , 'X' )
id data from till 1 X 10:00
UPDATE t SET data = 'Y' WHERE id = 1
id data from till 1 X 10:00 11:00 1 Y 11:00
INSERT INTO t (id, data) VALUES (1 , 'X' )
id data from till 1 X 10:00
UPDATE t SET data = 'Y' WHERE id = 1
id data from till 1 X 10:00 11:00 1 Y 11:00
DELETE FROM t WHERE id = 1
id data from till 1 X 10:00 11:00 1 Y 11:00 12:00
id data from till 1 X 10:00 11:00 1 Y 11:00 12:00
id data from till 1 X 10:00 11:00 1 Y 11:00 12:00
SELECT * FROM t
id data from till
id data from till 1 X 10:00 11:00 1 Y 11:00 12:00
SELECT * FROM t
id data from till
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'…10:30:00'
id data from till 1 X 10:00 11:00
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.3
MariaDB MySQL PostgreSQL SQLite
10.1
DB2 LUW
10gR1[0] 11gR1[1]
Oracle
2016
SQL Server
[0]Short term using Flashback. [1]Flashback Archive. Proprietery syntax.
https://webstore.iec.ch/publication/59685
A lot has happened since SQL-92
SQL has evolved beyond the relational idea A lot has happened since SQL-92
SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92
SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92
https://modern-sql.com
@ModernSQL by @MarkusWinand
Training: https://winand.at/