[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