[prev in list] [next in list] [prev in thread] [next in thread]
List: mondrian
Subject: Re: [Mondrian] Parenthesized queries in UNION
From: damien hostin <damien.hostin () axege ! com>
Date: 2012-12-04 13:08:13
Message-ID: 50BDF5BD.5020409 () axege ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I had the same issue with postgresql when I ran the regression tests
last week. This commit should also fix postgresql union queries.
Damien Hostin
Le 04/12/2012 13:45, Tiago Ferreira a écrit :
> I put the parenthesis because MySQL wasn't accepting the order by in
> the first select. It was far from a good solution but seemed slightly
> better than before and allowed to test for more issues on multiple
> measure groups.
>
> Meanwhile I made it output only one order by for the whole union as
> you suggest. Before it was not just generating an order by for each
> select, but the last one would have two, both by column name and by
> ordinal. Now it only does the ordinal order for unions
>
> It's one of the commits in the last pull request I made:
>
> https://github.com/webdetails/mondrian/commit/e37cc17f6a112da3639e5591c5a7de193c368766
>
> From what I tested it runs ok in MySQL, Hypersonic and Oracle.
>
> On 4 December 2012 07:04, Julian Hyde <jhyde@pentaho.com
> <mailto:jhyde@pentaho.com>> wrote:
>
> By the way, this was the commit:
> https://github.com/pentaho/mondrian/commit/8837434cc059a0023da25288abfe5e9ec21f38fc.
>
> Julian
>
> On Dec 3, 2012, at 10:44 PM, Julian Hyde <jhyde@pentaho.com
> <mailto:jhyde@pentaho.com>> wrote:
>
> > Tiago,
> >
> > What was the error that made you make the following change, and
> on what database?
> >
> > Before your change, the code used to generate
> >
> > SELECT ... ORDER BY UNION SELECT ... ORDER BY
> >
> > and after your change generates
> >
> > (SELECT ... ORDER BY) UNION (SELECT ... ORDER BY)
> >
> > ORDER BY inside a subquery is illegal per the SQL standard (and
> as it happens makes MongoDB's parser barf), so I wonder if that
> was the real problem you were facing. I am fixing it to generate
> >
> > (SELECT ...) UNION (SELECT ...) ORDER BY
> >
> > and, since simpler is better, I wonder if I could go one step
> further and generate
> >
> > SELECT ... UNION SELECT ... ORDER BY
> >
> > To help me decide, it would help to know why you added the
> parentheses in the first place.
> >
> > Julian
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian@pentaho.org <mailto:Mondrian@pentaho.org>
> > http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
>
> --
> Tiago G.F.
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian@pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
[Attachment #5 (text/html)]
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix"><br>
I had the same issue with postgresql when I ran the regression
tests last week. This commit should also fix postgresql union
queries.<br>
<br>
Damien Hostin<br>
<br>
Le 04/12/2012 13:45, Tiago Ferreira a écrit :<br>
</div>
<blockquote
cite="mid:CAJr-Ee0iEQb+z1GV5BwoU89Lgzj1ra4zY1Sb8xXp4Kk71D5GkA@mail.gmail.com"
type="cite">I put the parenthesis because MySQL wasn't accepting
the order by in the first select. It was far from a good solution
but seemed slightly better than before and allowed to test for
more issues on multiple measure groups.<br>
<br>
Meanwhile I made it output only one order by for the whole union
as you suggest. Before it was not just generating an order by for
each select, but the last one would have two, both by column name
and by ordinal. Now it only does the ordinal order for unions<br>
<br>
It's one of the commits in the last pull request I made:<br>
<br>
<a moz-do-not-send="true"
href="https://github.com/webdetails/mondrian/commit/e37cc17f6a112da3639e5591c5a7de193c \
368766">https://github.com/webdetails/mondrian/commit/e37cc17f6a112da3639e5591c5a7de193c368766</a><br>
<br>
From what I tested it runs ok in MySQL, Hypersonic and Oracle.<br>
<br>
<div class="gmail_quote">On 4 December 2012 07:04, Julian Hyde <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:jhyde@pentaho.com" \
target="_blank">jhyde@pentaho.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">By the way,
this was the commit: <a moz-do-not-send="true"
href="https://github.com/pentaho/mondrian/commit/8837434cc059a0023da25288abfe5e9ec21f38fc"
target="_blank">https://github.com/pentaho/mondrian/commit/8837434cc059a0023da25288abfe5e9ec21f38fc</a>.<br>
<br>
Julian<br>
<div>
<div class="h5"><br>
On Dec 3, 2012, at 10:44 PM, Julian Hyde <<a
moz-do-not-send="true" \
href="mailto:jhyde@pentaho.com">jhyde@pentaho.com</a>> wrote:<br>
<br>
> Tiago,<br>
><br>
> What was the error that made you make the following
change, and on what database?<br>
><br>
> Before your change, the code used to generate<br>
><br>
> SELECT ... ORDER BY UNION SELECT ... ORDER BY<br>
><br>
> and after your change generates<br>
><br>
> (SELECT ... ORDER BY) UNION (SELECT ... ORDER BY)<br>
><br>
> ORDER BY inside a subquery is illegal per the SQL
standard (and as it happens makes MongoDB's parser barf),
so I wonder if that was the real problem you were facing.
I am fixing it to generate<br>
><br>
> (SELECT ...) UNION (SELECT ...) ORDER BY<br>
><br>
> and, since simpler is better, I wonder if I could go
one step further and generate<br>
><br>
> SELECT ... UNION SELECT ... ORDER BY<br>
><br>
> To help me decide, it would help to know why you
added the parentheses in the first place.<br>
><br>
> Julian<br>
</div>
</div>
> _______________________________________________<br>
> Mondrian mailing list<br>
> <a moz-do-not-send="true"
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
> <a moz-do-not-send="true"
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
</blockquote>
</div>
<br>
<br clear="all">
<br>
-- <br>
<div>Tiago G.F.</div>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Mondrian mailing list
<a class="moz-txt-link-abbreviated" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <a \
class="moz-txt-link-freetext" \
href="http://lists.pentaho.org/mailman/listinfo/mondrian">http://lists.pentaho.org/mailman/listinfo/mondrian</a>
</pre>
</blockquote>
<br>
</body>
</html>
_______________________________________________
Mondrian mailing list
Mondrian@pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic