[Postgres-xl-bugs] Postgres XL 95 Bug with Rollup
Pavan Deolasee
pavan.deolasee at gmail.com
Thu Sep 3 22:31:59 PDT 2015
Hi Frederic,
I don't think the bugs ML is moderated, may be you don't see anything until
someone responds back. I've seen especially if you use gmail.
As I said in the other email, GROUPING/ROLLUP/CUBE are mostly broken in XL
9.5. The grouping regression test also throws a core today. These features
will take non-trivial efforts to fix. For now, at least disabling them and
throwing a user friendly error is on plate.
Is this something very important for you?
Thanks,
Pavan
On Thu, Sep 3, 2015 at 10:29 PM, Frederic Vander Elst <fve at vander-elst.org>
wrote:
> Hi - sorry, resend so the postgres-xl-bugs mailing list software doesn't
> have to get a moderator out of bed (which it will for mails from
> fve at phgroup.com, but won't from this email).
>
> ---------
>
> Hi Pavan, All,
>
>
>
> The problem I found yesterday actually showed two bugs. I submitted the
> first, sum(bigint) -> nulls yesterday.
>
>
>
> This one is to dowith ROLLUP(). I supply 3 files herewith, but the
> summary is:
>
>
>
> -- prepare tables:
>
> drop table fact_r;
>
> create table fact_r (mailable text, phoneable text, vf0 integer);
>
> insert into fact_r values ('y', 'y', 46142), ('y', '', 1418);
>
>
>
> -- NOTE all lines like -- XL: blah blah are my MANUAL comments
>
> -- This works everywhere:
>
> -- ONE rollup()
>
> SELECT
>
> grouping(phoneable) AS grouping_1,
>
> phoneable,
>
> SUM(vf0) AS sum_1
>
> FROM
>
> fact_r T
>
> GROUP BY
>
> rollup(phoneable)
>
> ;
>
> -- XL: WORKS
>
> -- PG95: WORKS
>
> -- AND: same result:
>
> grouping_1 | phoneable | sum_1
>
> ------------+-----------+-------
>
> 0 | | 1418
>
> 0 | y | 46142
>
> 1 | | 47560
>
>
>
> -- Now let's try TWO rollups():
>
> ------------------------------------------------------------------------
>
> SELECT
>
> grouping(phoneable) AS grouping_1,
>
> grouping(mailable) AS grouping_2,
>
> phoneable,
>
> mailable,
>
> SUM(vf0) AS sum_1
>
> FROM
>
> fact_r T
>
> GROUP BY
>
> rollup(phoneable),
>
> rollup(mailable)
>
> ;
>
> -- XL: ERROR: variable not found in subplan target list
>
> -- XL: and I sometimes got: This connection has been closed.
>
> -- XL: on server: LOG: server process (PID 15392) was terminated by
> signal 11: Segmentation fault
>
> -- PG95: WORKS and yields
>
>
>
> grouping_1 | grouping_2 | phoneable | mailable | sum_1
>
> ------------+------------+-----------+----------+-------
>
> 0 | 0 | | y | 1418
>
> 0 | 1 | | | 1418
>
> 0 | 0 | y | y | 46142
>
> 0 | 1 | y | | 46142
>
> 1 | 1 | | | 47560
>
> 1 | 0 | | y | 47560
>
>
>
>
>
> -- Let's try expressing ROLLUP(a), ROLLUP(b), as ROLLUP(a, b):
>
> SELECT
>
> grouping(phoneable) AS grouping_1,
>
> grouping(mailable) AS grouping_2,
>
> phoneable,
>
> mailable,
>
> SUM(vf0) AS sum_1
>
> FROM
>
> fact_r T
>
> GROUP BY
>
> rollup(phoneable, mailable)
>
> ;
>
> -- XL: works
>
> -- PG95: works, and same result:
>
> grouping_1 | grouping_2 | phoneable | mailable | sum_1
>
> ------------+------------+-----------+----------+-------
>
> 0 | 0 | | y | 1418
>
> 0 | 1 | | | 1418
>
> 0 | 0 | y | y | 46142
>
> 0 | 1 | y | | 46142
>
> 1 | 1 | | | 47560
>
> -- but what we want is really the result of rollup(a), rollup(b)
>
> -- rollup(a,b) yields 5 rows, rollup(a), rollup(b) yields 6 rows
>
> -- the missing one is the 1..0.. one:
>
> grouping_1 | grouping_2 | phoneable | mailable | sum_1
>
> ------------+------------+-----------+----------+-------
>
> 1 | 0 | | y | 47560
>
>
>
>
>
> Soooo.. There is a problem with using more than one ROLLUP() expression in
> a group by.
>
>
>
> Most of the time XL flags an error while PG95 does the right thing.
>
>
>
> Sometimes (1/4 of the time, approx.) PG-XL dies and I lose the connection.
>
>
>
> You’ll be reassured I have no more bugs for today !!
>
>
>
> Best regards
>
>
>
> Freddie
>
>
>
> ps re the 3 attached files:
>
> on XL:
>
> psql -a -U fve databox -f 2-show-grouping-bug.sql 2&>1 > 2-grouping-xl.out
>
> on PG 95:
>
> psql -a -U fve databox -f 2-show-grouping-bug.sql 2&>1 > 2-grouping-95.out
>
>
>
>
>
>
>
--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-bugs-postgres-xl.org/attachments/20150904/20bfb982/attachment.htm>
More information about the postgres-xl-bugs
mailing list