[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17806239#comment-17806239
]
Julian Hyde commented on CALCITE-6044:
--
Looks good. Final testing and will merge to main shortly.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Assignee: Paul Jackson
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17790648#comment-17790648
]
Paul Jackson commented on CALCITE-6044:
---
[~julianhyde] the PR is ready for another review.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Assignee: Paul Jackson
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17786969#comment-17786969
]
Julian Hyde commented on CALCITE-6044:
--
I reviewed the PR. It needs a few revisions, but I'm sure we can complete it
before 1.37.
Should each implementation of {{getUniqueKeys}} check that the result is
minimal, per the contract? Maybe use the following function:
{code}
Set assertMinimal(Set sets) {
for (ImmutableBitSet set0 : sets) {
for (ImmutableBitSet set1 : sets) {
if (set0 != set 1 && set0.contains(set1))) {
throw new AssertionError();
}
}
}
}
{code}
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Assignee: Paul Jackson
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17779221#comment-17779221
]
Julian Hyde commented on CALCITE-6044:
--
Unsetting fix version. There's no PR.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Priority: Major
> Fix For: 1.36.0
>
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17774298#comment-17774298
]
Julian Hyde commented on CALCITE-6044:
--
I see; every other combination of columns is a superset of that, and therefore
a key. If you do that, you should make sure the javadoc is clear.
And while you are fixing the javadoc, change 'Handler.classinitely' to
'definitely'; there was an over-zealous search-and-replace in CALCITE-4928.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Priority: Major
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17774297#comment-17774297
]
Paul Jackson commented on CALCITE-6044:
---
It would make sense to me if {{getUniqueKeys}} returned a set containing a
single, empty set.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Priority: Major
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6044) Column uniqueness for single-row relations
[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17774267#comment-17774267
]
Julian Hyde commented on CALCITE-6044:
--
This sounds right.
However, beware trying to make {{getUniqueKeys}} consistent. If a table has N
columns and 1 row, then any permutation/combination of columns is a key. There
are 2^N of those, and that would take too much memory.
> Column uniqueness for single-row relations
> --
>
> Key: CALCITE-6044
> URL: https://issues.apache.org/jira/browse/CALCITE-6044
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Paul Jackson
>Priority: Major
>
> A single-row relation can result from a {{LIMIT 1}} or an aggregation without
> {{{}GROUP BY{}}}. Every column in one of these relations should be unique by
> virtue of having a max row count of 1.
> When joining with a single-row relation on a key field, the join result
> should no longer require that key field for uniqueness. For example, suppose
> the {{emp}} table had a composite key {{{}(empno,hiredate){}}}. If we join on
> {{hiredate=max(hiredate)}} then {{empno}} alone should be a unique column:
>
> {code:java}
> SELECT * FROM emp A
> JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B
> ON A.hiredate = B.last_hired
> {code}
> {{join(A,B).empno}} should be unique because {{(A.empno,A.hiredate)}} is
> unique and {{A.hiredate}} is effectively constant because it is equal to
> {{{}B.last_hired{}}}.
> Here are some {{RelMetadataTests}} cases that I think should pass.
> {code:java}
> @Test void testColumnUniquenessForLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp\n"
> + "limit 1";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForJoinOnAggregation() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select max(empno) AS maxno from emp) B\n"
> + "on A.empno = B.maxno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> }
> @Test void testColumnUniquenessForCorrelatedSubquery() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "where empno = (\n"
> + " select max(empno) from emp)";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true));
> } {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
