ClassAlias does not generate AS in FROM clause

[Aaron Bentley]

I have a script that needs to run on Packagings where the product and the sourcepackage each have POTemplates. The POTemplates for the product and the sourcepackage are different. The SQL version looks like this:

SELECT DISTINCT Product.name, ProductSeries.name, SourcePackageName.name, DistroSeries.name
  FROM POTemplate as UpstreamTemplate, POTemplate, ProductSeries, Product, SourcePackageName, DistroSeries, Packaging
  WHERE ProductSeries.product = Product.id AND
    UpstreamTemplate.productseries = ProductSeries.id AND
    POTemplate.distroseries = Distroseries.id AND
    POTemplate.sourcepackagename = SourcePackageName.id AND
    Packaging.productseries = Productseries.id AND
    Packaging.distroseries = DistroSeries.id AND
    Packaging.sourcepackagename = SourcePackageName.id;

Crucially, the POTemplate column is listed twice in the FROM clause-- once using AS to make it distinct.

I tried to Stormify the query using ClassAlias:
 PackageTemplate = ClassAlias(POTemplate)
        return store.find(
            Packaging,
            Packaging.productseries == ProductSeries.id,
            Packaging.sourcepackagename == SourcePackageName.id,
            Packaging.distroseries == DistroSeries.id,
            POTemplate.productseries == ProductSeries.id,
            PackageTemplate.distroseries == DistroSeries.id,
            PackageTemplate.sourcepackagename == SourcePackageName.id,
            )

The result had no AS in the FROM clause:
SELECT Packaging.datecreated, Packaging.distroseries, Packaging.id, Packaging.owner, Packaging.packaging, Packaging.productseries, Packaging.sourcepackagename
FROM DistroSeries, POTemplate, Packaging, ProductSeries, SourcePackageName
WHERE Packaging.productseries = ProductSeries.id AND
Packaging.sourcepackagename = SourcePackageName.id AND
Packaging.distroseries = DistroSeries.id AND
POTemplate.productseries = ProductSeries.id AND
POTemplate.distroseries = DistroSeries.id AND
POTemplate.sourcepackagename = SourcePackageName.id

It appears ClassAlias had no effect, and this changes the meaning completely, because while a Product and a sourcepackage may each have POTemplates, they will never have *the same* POTemplate.

I worked around this bug using a sub-select.

[James Henstridge (jamesh)]

There must be something else going on here. I tried to produce a simplified test case and can not reproduce the problem:

    >>> from storm.expr import compile, Select
    >>> from storm.info import ClassAlias
    >>> from storm.properties import Int
    >>> class Foo(object):
    ... __storm_table__ = 'foo'
    ... id = Int(primary=True)
    ...
    >>> Bar = ClassAlias(Foo)
    >>> compile(Select([Foo.id], [Foo.id == Bar.id]))
    u'SELECT foo.id FROM foo, foo AS "_1" WHERE foo.id = "_1".id'

Are there any other special details about the classes or attributes?

[James Henstridge (jamesh)]

Actually, I guess you're using references rather than columns in your comparisons here. Consider:

    >>> class Bar(object):
    ... __storm_table__ = 'bar'
    ... id = Int(primary=True)
    ...
    >>> class Foo(object):
    ... __storm_table__ = 'foo'
    ... id = Int(primary=True)
    ... bar_id = Int()
    ... bar = Reference(bar_id, Bar.id)
    ...
    >>> FooAlias = ClassAlias(Foo, "foo_alias")
    >>> compile(FooAlias.bar_id == Bar.id)
    'foo_alias.bar_id = bar.id'
    >>> compile(FooAlias.bar == Bar.id)
    'foo.bar_id = bar.id'

So this is probably a duplicate of bug 682989. Does that sound right to you?

[Aaron Bentley (abentley)]

Yes, replacing the references with columns makes a suitable AS clause appear, so I guess it's a dupe.

        result = store.find(
            Packaging,
            Packaging.productseries == ProductSeries.id,
            Packaging.sourcepackagename == SourcePackageName.id,
            Packaging.distroseries == DistroSeries.id,
            POTemplate.productseries == ProductSeries.id,
            PackageTemplate.distroseriesID == DistroSeries.id,
            PackageTemplate.sourcepackagenameID == SourcePackageName.id,
            )

SELECT DISTINCT Packaging.datecreated, Packaging.distroseries, Packaging.id, Packaging.owner, Packaging.packaging, Packaging.productseries, Packaging.sourcepackagename FROM DistroSeries, POTemplate, POTemplate AS "_1", Packaging, ProductSeries, SourcePackageName WHERE Packaging.productseries = ProductSeries.id AND Packaging.sourcepackagename = SourcePackageName.id AND Packaging.distroseries = DistroSeries.id AND POTemplate.productseries = ProductSeries.id AND "_1".distroseries = DistroSeries.id AND "_1".sourcepackagename = SourcePackageName.id