Problem with schema-qualified tables and eager_graph

41 views
Skip to first unread message

John Firebaugh

unread,
Feb 8, 2010, 3:45:25 PM2/8/10
to seque...@googlegroups.com
I'm having a problem with schema-qualified tables used in eager graphs
under MSSQL:

http://pastie.org/815193

The last line in that example produces the following SQL:

SELECT [s].[a].[id], [s].[a].[b_id], [s].[a].[c_id], [s].[a].[b_id],
[c].[id] AS [c_id_0] FROM (SELECT * FROM [s].[a] INNER JOIN [s].[b] ON
([s].[b].[b_id] = [s].[a].[b_id])) AS [s__a] LEFT OUTER JOIN [s].[c]
AS [c] ON ([c].[id] = [s].[a].[c_id])

Note the table alias [s__a].

This statement produces multiple errors from the DBMS when executed:

The column 'b_id' was specified multiple times for 's__a'.
The multi-part identifier "s.a.c_id" could not be bound.
The multi-part identifier "s.a.id" could not be bound.
... so on for s.a.b_id, s.a.c_id, s.a.b_id

Jeremy Evans

unread,
Feb 8, 2010, 6:32:43 PM2/8/10
to sequel-talk
On Feb 8, 12:45 pm, John Firebaugh <john.fireba...@gmail.com> wrote:
> I'm having a problem with schema-qualified tables used in eager graphs
> under MSSQL:
>
> http://pastie.org/815193
>
> The last line in that example produces the following SQL:
>
> SELECT [s].[a].[id], [s].[a].[b_id], [s].[a].[c_id], [s].[a].[b_id],
> [c].[id] AS [c_id_0] FROM (SELECT * FROM [s].[a] INNER JOIN [s].[b] ON
> ([s].[b].[b_id] = [s].[a].[b_id])) AS [s__a] LEFT OUTER JOIN [s].[c]
> AS [c] ON ([c].[id] = [s].[a].[c_id])
>
> Note the table alias [s__a].

Wow, that is a complex case. I don't think that situations where you
are joining separately from graphing are fully tested, even without
throwing in schemas. At least you didn't throw in self referential
tables.

Anyway, please test the patch at http://pastie.org/815474.txt.

Jeremy

John Firebaugh

unread,
Feb 9, 2010, 2:35:18 PM2/9/10
to seque...@googlegroups.com

The patch is causing problems in post-loading less-complex
schema-qualified eager graphs -- I get a nil primary_record in
eager_graph_build_associations. It looks like 'master' is now schema
unqualified but the record_graph is still keyed by a unsplit symbol
with qualification.

Jeremy Evans

unread,
Feb 9, 2010, 5:33:16 PM2/9/10
to sequel-talk
On Feb 9, 11:35 am, John Firebaugh <john.fireba...@gmail.com> wrote:
> > Wow, that is a complex case.  I don't think that situations where you
> > are joining separately from graphing are fully tested, even without
> > throwing in schemas.  At least you didn't throw in self referential
> > tables.
>
> > Anyway, please test the patch athttp://pastie.org/815474.txt.

>
> The patch is causing problems in post-loading less-complex
> schema-qualified eager graphs -- I get a nil primary_record in
> eager_graph_build_associations. It looks like 'master' is now schema
> unqualified but the record_graph is still keyed by a unsplit symbol
> with qualification.

I'd like as much information as you can give me about it. If you
could pastie or send me the association part of your model files, the
migrations/database schema, and the eager graph queries, I could
probably get things working.

Jeremy

John Firebaugh

unread,
Feb 9, 2010, 7:16:25 PM2/9/10
to seque...@googlegroups.com
On Tue, Feb 9, 2010 at 2:33 PM, Jeremy Evans <jeremy...@gmail.com> wrote:
> I'd like as much information as you can give me about it.  If you
> could pastie or send me the association part of your model files, the
> migrations/database schema, and the eager graph queries, I could
> probably get things working.

Test case: http://pastie.org/817379

Mike Luu

unread,
Feb 9, 2010, 7:21:43 PM2/9/10
to sequel-talk
On Feb 9, 4:16 pm, John Firebaugh <john.fireba...@gmail.com> wrote:

> On Tue, Feb 9, 2010 at 2:33 PM, Jeremy Evans <jeremyeva...@gmail.com> wrote:
> > I'd like as much information as you can give me about it.  If you
> > could pastie or send me the association part of your model files, the
> > migrations/database schema, and the eager graph queries, I could
> > probably get things working.
>
> Test case:http://pastie.org/817379


this seems to be the missing part of the patch
http://pastie.org/817382.txt

Jeremy, your patch fixes the implicit qualifier for the conditions and
record graph keys but we still needed to select the source dataset
columns without the schema.

Jeremy Evans

unread,
Feb 19, 2010, 11:07:44 PM2/19/10
to sequel-talk
On Feb 9, 4:21 pm, Mike Luu <munky...@gmail.com> wrote:
> this seems to be the missing part of the patchhttp://pastie.org/817382.txt

>
> Jeremy, your patch fixes the implicit qualifier for the conditions and
> record graph keys but we still needed to select the source dataset
> columns without the schema.

I'm sorry for taking so long to get back to you on this, but I've been
swamped at work recently. I finally had time tonight to work on a
proper patch, located at http://pastie.org/833854.txt. I've applied
this patch to my private branch, and will probably push it to github
next week after more testing. Please test it with your code and make
sure it works correctly.

Thanks,
Jeremy

John Firebaugh

unread,
Feb 22, 2010, 5:20:34 PM2/22/10
to seque...@googlegroups.com

It's passing all our tests.

Thanks!

Jeremy Evans

unread,
Feb 22, 2010, 7:47:02 PM2/22/10
to sequel-talk
On Feb 22, 2:20 pm, John Firebaugh <john.fireba...@gmail.com> wrote:

> On Fri, Feb 19, 2010 at 8:07 PM, Jeremy Evans <jeremyeva...@gmail.com> wrote:
> > On Feb 9, 4:21 pm, Mike Luu <munky...@gmail.com> wrote:
> >> this seems to be the missing part of the patchhttp://pastie.org/817382.txt
>
> >> Jeremy, your patch fixes the implicit qualifier for the conditions and
> >> record graph keys but we still needed to select the source dataset
> >> columns without the schema.
>
> > I'm sorry for taking so long to get back to you on this, but I've been
> > swamped at work recently.  I finally had time tonight to work on a
> > proper patch, located athttp://pastie.org/833854.txt.  I've applied

> > this patch to my private branch, and will probably push it to github
> > next week after more testing.  Please test it with your code and make
> > sure it works correctly.
>
> It's passing all our tests.

Great! I just pushed it to GitHub along with quite a few other
patches: http://github.com/jeremyevans/sequel/commits/master

Jeremy

Reply all
Reply to author
Forward
0 new messages