Here's a valid SQL query where an empty projection naturally arises:
SELECT d.dname
FROM dept AS d
JOIN emp AS e ON e.deptno = d.deptno
WHERE d.deptno = 10
You want the name of department 10 to be printed N times, where N is
the number of employees in the department, but you don't need any
attributes from the employee table. So it can be optimized to the
following pseudo-SQL:
SELECT d.dname
FROM (
SELECT d.deptno, d.dname FROM dept WHERE deptno = 10) AS d
CROSS JOIN (
SELECT /* no columns */ FROM emp WHERE deptno = 10) AS e
If there are 4 employees in department 10, then the emp side of this
query would return 4 empty records:
()
()
()
()
An Aggregate with no group keys and no aggregate calls is less
interesting, because it always returns one row, and can therefore be
eliminated.
Julian
On Fri, Apr 30, 2021 at 2:53 PM Stamatis Zampetakis <[email protected]> wrote:
>
> Hello,
>
> The first operator that comes to mind when we are talking about empty
> records is a projection that projects nothing. In theory and in most
> database books the projection is allowed to have arity 0 but in practice I
> never had to use it; neither aggregate nor any other operator. In fact an
> aggregate with empty group keys and aggregate calls resembles a lot an
> empty projection.
>
> @Konstantin how did you come with the empty aggregate? Was it also a result
> of trimming?
>
> If we have to make a decision now I would prefer to disallow them globally
> unless somebody comes up with a compelling use-case.
> In order to express other query languages (e.g,, datalog, conjunctive
> queries, etc) with the algebraic operators we may need 0-arity operators
> but I don't have an example readily in mind.
>
> Best,
> Stamatis
>
> On Thu, Apr 29, 2021 at 7:07 PM Julian Hyde <[email protected]> wrote:
>
> > > So the question is whether an empty record is a legal row type for an
> > aggregation node?
> >
> > As that comment indicates, we have tried to avoid empty records — that is,
> > a relational expression that produces a row type with zero fields — but as
> > you have just discovered, we have failed to go all the way.
> >
> > Mathematically, it is purer to allow empty records. SQL does not allow
> > them, they crop up naturally in quite a lot of corner cases, especially
> > after trimming fields.
> >
> > Pragmatically, I assumed that quite a lot of code was making the
> > assumption that records were not empty. And that empty records are
> > sufficiently rare that we would never be able to find all of those places
> > via testing.
> >
> > Is it time to decide? If we allow empty records, we should test that all
> > relational operators can handle them. If we ban them, then we should (say)
> > throw whenever someone registers a RelNode that has an empty row type.
> >
> > Julian
> >
> >
> >
> >
> > > On Apr 29, 2021, at 7:25 AM, Konstantin Orlov <[email protected]>
> > wrote:
> > >
> > > Hi all.
> > >
> > > I faced a problem preventing certain queries being planned because
> > RelFieldTrimmer throws
> > > an ArrayIndexOutOfBoundsException with message "Index -1 out of bounds
> > for length 0”.
> > >
> > > The problem is here [1]:
> > >
> > > // If they are asking for no fields, we can't give them what they
> > want,
> > > // because zero-column records are illegal. Give them the last field,
> > > // which is unlikely to be a system field.
> > > if (fieldsUsed.isEmpty()) {
> > > fieldsUsed = ImmutableBitSet.range(fieldCount - 1, fieldCount);
> > > }
> > >
> > > In case fieldsUsed.isEmpty we returns last field, but it is currently
> > possible that fieldCount=0 as well.
> > >
> > > After some investigation I find out that the reason is empty record
> > derived as row type for Aggregate.
> > > It is possible when an aggregate has an empty group key and no aggregate
> > calls.
> > >
> > > So the question is whether an empty record is a legal row type for an
> > aggregation node?
> > >
> > > Below is a reproducer for this problem, just put it at
> > RelFieldTrimmerTest:
> > >
> > > @Test void test() {
> > > class ContextImpl implements Context {
> > > final Object target;
> > >
> > > ContextImpl(Object target) {
> > > this.target = Objects.requireNonNull(target, "target");
> > > }
> > >
> > > @Override public <T extends Object> @Nullable T unwrap(Class<T>
> > clazz) {
> > > if (clazz.isInstance(target)) {
> > > return clazz.cast(target);
> > > }
> > > return null;
> > > }
> > > }
> > >
> > > // RelBuilder hides problem when simplifyValues=true, hence we need
> > to disable it
> > > final RelBuilder builder = RelBuilder.create(config()
> > > .context(new
> > ContextImpl(RelBuilder.Config.DEFAULT.withSimplifyValues(false))).build());
> > >
> > > final RelNode root =
> > > builder.scan("EMP")
> > > .aggregate(builder.groupKey())
> > > .filter(builder.literal(false))
> > > .project(builder.literal(42))
> > > .build();
> > >
> > > final RelFieldTrimmer fieldTrimmer = new RelFieldTrimmer(null,
> > builder);
> > > fieldTrimmer.trim(root); // fails with
> > ArrayIndexOutOfBoundsException: Index -1 out of bounds for length 0
> > > }
> > >
> > >
> > > [1]
> > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L1197
> > >
> > > --
> > > Regards,
> > > Konstantin Orlov
> > >
> > >
> > >
> > >
> >
> >
