Changeset: f2b54689b53a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f2b54689b53a
Modified Files:
sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.sql
sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.stable.out
Branch: Aug2018
Log Message:
Added more queries to analyse the problem and finds some workarounds (see query
8 and 9):
-- 7. Same query as 5 but using qualified column names in subquery.
-- (produces incorrect results, same as 5)
SELECT C1, C2, C3
FROM T1
WHERE (C1, C2) IN
(
SELECT T1.C1, T1.C2
FROM T1
GROUP BY T1.C1, T1.C2
HAVING COUNT(*) > 1
);
-- 8. Same query as 5 but using alias for table and qualified column names
-- in subquery. (produces correct result, so can be used as a workaround)
SELECT C1, C2, C3
FROM T1
WHERE (C1, C2) IN
(
SELECT T.C1, T.C2
FROM T1 AS T
GROUP BY T.C1, T.C2
HAVING COUNT(*) > 1
);
-- 9. Query using NOT IN instead of IN (and change COUNT(*) = 1)
-- (produces correct result, so can be used as a workaround)
SELECT C1, C2, C3
FROM T1
WHERE (C1, C2) NOT IN
(
SELECT C1, C2
FROM T1
GROUP BY C1, C2
HAVING COUNT(*) = 1
);
Also added more data rows to test whether the data influences incorrect
processing, which is not the case.
>From queries 7 and 8 we can see it has to do with name scoping problem.
Query 8 shows how you can workaround it (till there is a software patch
released).
diffs (199 lines):
diff --git a/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.sql
b/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.sql
--- a/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.sql
+++ b/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.sql
@@ -57,4 +57,83 @@ SELECT C1, C2, C3
HAVING COUNT(*) > 1
);
+-- 7. Same query as 5 but using qualified column names in subquery.
+-- (produces incorrect results, same as 5)
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C1, C2) IN
+ (
+ SELECT T1.C1, T1.C2
+ FROM T1
+ GROUP BY T1.C1, T1.C2
+ HAVING COUNT(*) > 1
+ );
+
+-- 8. Same query as 5 but using alias for table and qualified column names
+-- in subquery. (produces correct result, so can be used as a workaround)
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C1, C2) IN
+ (
+ SELECT T.C1, T.C2
+ FROM T1 AS T
+ GROUP BY T.C1, T.C2
+ HAVING COUNT(*) > 1
+ );
+
+-- 9. Query using NOT IN instead of IN (and change COUNT(*) = 1)
+-- (produces correct result, so can be used as a workaround)
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C1, C2) NOT IN
+ (
+ SELECT C1, C2
+ FROM T1
+ GROUP BY C1, C2
+ HAVING COUNT(*) = 1
+ );
+
+
+-- add more data to test whether the data influences the processing
+INSERT INTO T1
+VALUES (21, 22, 3),
+ (21, 22, 4),
+ (22, 22, 5),
+ (21, 23, 6);
+
+-- only repeat the queries which produced wrong results
+
+-- query 5. results are incorrect, two rows (1, 3, 6) (21, 23, 6) should not
be there.
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C1, C2) IN
+ (
+ SELECT C1, C2
+ FROM T1
+ GROUP BY C1, C2
+ HAVING COUNT(*) > 1
+ );
+
+-- query 6. results are incorrect, two rows (2, 2, 5) (22, 22, 5) should not
be there.
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C2, C1) IN
+ (
+ SELECT C2, C1
+ FROM T1
+ GROUP BY C1, C2
+ HAVING COUNT(*) > 1
+ );
+
+-- query 7. results are incorrect, two rows (1, 3, 6) (21, 23, 6) should not
be there.
+SELECT C1, C2, C3
+ FROM T1
+ WHERE (C1, C2) IN
+ (
+ SELECT T1.C1, T1.C2
+ FROM T1
+ GROUP BY T1.C1, T1.C2
+ HAVING COUNT(*) > 1
+ );
+
ROLLBACK;
diff --git
a/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.stable.out
b/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.stable.out
--- a/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.stable.out
+++ b/sql/test/BugTracker-2018/Tests/in-subquery-having-Bug-6651.stable.out
@@ -130,6 +130,108 @@ Ready.
% 1, 1, 1 # length
[ 1, 2, 3 ]
[ 1, 2, 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C1, C2) IN
+# (
+# SELECT T1.C1, T1.C2
+# FROM T1
+# GROUP BY T1.C1, T1.C2
+# HAVING COUNT(*) > 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 1, 1, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C1, C2) IN
+# (
+# SELECT T.C1, T.C2
+# FROM T1 AS T
+# GROUP BY T.C1, T.C2
+# HAVING COUNT(*) > 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 1, 1, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C1, C2) NOT IN
+# (
+# SELECT C1, C2
+# FROM T1
+# GROUP BY C1, C2
+# HAVING COUNT(*) = 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 1, 1, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+#INSERT INTO T1
+#VALUES (21, 22, 3),
+# (21, 22, 4),
+# (22, 22, 5),
+# (21, 23, 6);
+[ 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C1, C2) IN
+# (
+# SELECT C1, C2
+# FROM T1
+# GROUP BY C1, C2
+# HAVING COUNT(*) > 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 2, 2, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+[ 21, 22, 3 ]
+[ 21, 22, 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C2, C1) IN
+# (
+# SELECT C2, C1
+# FROM T1
+# GROUP BY C1, C2
+# HAVING COUNT(*) > 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 2, 2, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+[ 21, 22, 3 ]
+[ 21, 22, 4 ]
+#SELECT C1, C2, C3
+# FROM T1
+# WHERE (C1, C2) IN
+# (
+# SELECT T1.C1, T1.C2
+# FROM T1
+# GROUP BY T1.C1, T1.C2
+# HAVING COUNT(*) > 1
+# );
+% sys.t1, sys.t1, sys.t1 # table_name
+% c1, c2, c3 # name
+% int, int, int # type
+% 2, 2, 1 # length
+[ 1, 2, 3 ]
+[ 1, 2, 4 ]
+[ 21, 22, 3 ]
+[ 21, 22, 4 ]
#ROLLBACK;
# 11:21:55 >
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
