From 6581d23c63f190645d3c9a61392309e14a9a01f2 Mon Sep 17 00:00:00 2001
From: David Kaufmann <astra@ionic.at>
Date: Mon, 8 Apr 2019 03:45:20 +0200
Subject: [PATCH] add test scripts from bordo

---
 ex1/ex15a.sh |  9 +++++++++
 ex1/ex15b.sh |  9 +++++++++
 ex1/ex15c.sh |  9 +++++++++
 ex1/ex15d.sh | 17 +++++++++++++++++
 4 files changed, 44 insertions(+)
 create mode 100755 ex1/ex15a.sh
 create mode 100755 ex1/ex15b.sh
 create mode 100755 ex1/ex15c.sh
 create mode 100755 ex1/ex15d.sh

diff --git a/ex1/ex15a.sh b/ex1/ex15a.sh
new file mode 100755
index 0000000..391a49c
--- /dev/null
+++ b/ex1/ex15a.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.a unoptimized"
+echo "${EXPLAIN} SELECT distinct(displayname) FROM users u WHERE id IN (SELECT owneruserid FROM posts p WHERE p.viewcount > u.views);" | psql
+
+echo "ex1.5.a optimized"
+echo "${EXPLAIN} SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;" | psql
diff --git a/ex1/ex15b.sh b/ex1/ex15b.sh
new file mode 100755
index 0000000..6aefd85
--- /dev/null
+++ b/ex1/ex15b.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.b unoptimized"
+echo "${EXPLAIN} SELECT score FROM comments WHERE lower(substring(text for 3)) = 'yes';" | psql
+
+echo "ex1.5.b optimized"
+echo "${EXPLAIN} SELECT score FROM comments WHERE text ILIKE 'yes\%';" | psql
diff --git a/ex1/ex15c.sh b/ex1/ex15c.sh
new file mode 100755
index 0000000..230a74f
--- /dev/null
+++ b/ex1/ex15c.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.c unoptimized"
+echo "${EXPLAIN} SELECT DISTINCT postid FROM votes v WHERE (SELECT COUNT(*) FROM votes v2 WHERE v2.postid = v.postid AND v2.votetypeid = 2) = (SELECT COUNT(*) FROM votes v2 WHERE v2.postid = v.postid);" | psql
+
+echo "ex1.5.c optimized"
+echo "${EXPLAIN} SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);" | psql
diff --git a/ex1/ex15d.sh b/ex1/ex15d.sh
new file mode 100755
index 0000000..ad413c1
--- /dev/null
+++ b/ex1/ex15d.sh
@@ -0,0 +1,17 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+#EXPLAIN=""
+
+echo "ex1.5.d unoptimized"
+echo "${EXPLAIN} SELECT p.*, c.*, u.* FROM posts p, comments c, users u, badges b WHERE c.postid=p.id AND u.id=p.owneruserid AND u.upvotes+3 >= (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate) AND EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid > p.id) AND u.id = b.userid AND (b.name SIMILAR TO 'Autobiographer' OR b.name SIMILAR TO 'Supporter');" | psql
+
+echo "ex1.5.d optimized"
+echo "${EXPLAIN} SELECT p.*, c.*, u.* FROM posts p
+JOIN users u ON p.owneruserid = u.id
+JOIN badges b on u.id = b.userid
+JOIN comments c ON p.id = c.postid
+WHERE b.name IN ('Autobiographer','Supporter')
+	AND p.id < (SELECT MAX(relatedpostid) FROM postlinks)
+	AND u.upvotes+3 >=
+		(SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate);" | psql
-- 
2.43.0