Re: [Postgres-xc-developers] Fwd: patch for bug#3126459:select error ： (group by .. order by.. )

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Dears,

Still relative with patch for bug#3126459.

postgres=# select avg(q1) from test group by q1;
 avg
-----
 0.0
 0.0
(2 rows)

It's confused that avg always returns 0.

Regards,
Benny
在 2010年12月21日 上午10:57，xiong wang <wan...@gm...> 写道：
> Dears,
>
> After I apply my patch on bug#3126459, another aggregate bug occured.
> The following are some basic infomation:
> steps:
> create table bb(a int , b int );                       ^
> insert into bb values(1,2);
> insert into bb values(1,3);
> insert into bb values(4,3);
> insert into bb values(4,5);
> select sum(sum(a)) over (partition by a),count(a) from bb group by a,b
> order by a,b;
>
> core dump:
>
> Core was generated by `postgres: shench postgres [local] SELECT
>                '.
> Program terminated with signal 11, Segmentation fault.
> [New process 29290]
> #0  pg_detoast_datum (datum=0x0) at fmgr.c:2217
> 2217 if (VARATT_IS_EXTENDED(datum))
> (gdb) bt
> #0  pg_detoast_datum (datum=0x0) at fmgr.c:2217
> #1  0x0000000000451188 in printtup (slot=0x14e80538, self=0x14e6d3c0)
> at printtup.c:342
> #2  0x00000000005370f8 in ExecutePlan (estate=0x14e80320,
> planstate=0x14e80640, operation=CMD_SELECT, numberTuples=0,
>    direction=<value optimized out>, dest=0x14e6d3c0) at execMain.c:1774
> #3  0x000000000053763c in standard_ExecutorRun (queryDesc=0x14e2e520,
> direction=ForwardScanDirection, count=0)
>    at execMain.c:312
> #4  0x00000000005ecb24 in PortalRunSelect (portal=0x14e7c300,
> forward=<value optimized out>, count=0, dest=0x14e6d3c0)
>    at pquery.c:967
> #5  0x00000000005edd40 in PortalRun (portal=0x14e7c300,
> count=9223372036854775807, isTopLevel=1 '\001', dest=0x14e6d3c0,
>    altdest=0x14e6d3c0, completionTag=0x7fffc47a7710 "") at pquery.c:793
> #6  0x00000000005e91a1 in exec_simple_query (
>    query_string=0x14e18880 "select sum(sum(a)) over (partition by
> a),count(a) from bb group by a,b order by a,b;")
>    at postgres.c:1053
> #7  0x00000000005ea7b6 in PostgresMain (argc=4, argv=<value optimized
> out>, username=0x14d6e290 "shench") at postgres.c:3766
> #8  0x00000000005c07cc in ServerLoop () at postmaster.c:3607
> #9  0x00000000005c2a1c in PostmasterMain (argc=9, argv=0x14d6b730) at
> postmaster.c:1098
> #10 0x000000000056d5ae in main (argc=9, argv=<value optimized out>) at
> main.c:188
> (gdb)
>
> Regads,
> Benny
>
>
> 在 2010年12月21日 上午10:43，xiong wang <wan...@gm...> 写道：
>> Deras,
>>
>> A bug that Mason told is caused by alias processed actually as Mason
>> suggested.  It's has nothing to with the bug#3126459:select error ：
>> (group by ..
>> order by.. ) becuase the statement like select count(t2.*) from test
>> t1 left join test t2 on (t1.q2 = t2.q1) has the same problem.
>>
>> The problem is caused by the line 2239 in create_remotequery_plan
>> function that deparse_context =
>> deparse_context_for_remotequery(get_rel_name(rte->relid), rte->relid).
>>
>> If the first paramter of function deparse_context_for_remotequery
>> get_rel_name(rte->relid) is changed into rte->eref->aliasname. The
>> problem that Mason mentioned will be resolved.
>>
>> But when I excuted the statement after fixed the bug. It introduced a
>> segment fault. Here is some basic infomation during gdb:
>>  $28 = {type = T_TupleTableSlot, tts_isempty = 0 '\0', tts_shouldFree
>> = 0 '\0', tts_shouldFreeMin = 0 '\0',
>>  tts_slow = 0 '\0', tts_tuple = 0x0, tts_dataRow = 0x0, tts_dataLen =
>> -1, tts_dataNode = 0,
>>  tts_shouldFreeRow = 0 '\0', tts_attinmeta = 0x0, tts_tupleDescriptor
>> = 0xc797060, tts_mcxt = 0xc782db0,
>>  tts_buffer = 0, tts_nvalid = 2, tts_values = 0xc797270, tts_isnull =
>> 0xc797290 "", tts_mintuple = 0x0,
>>  tts_minhdr = {t_len = 0, t_self = {ip_blkid = {bi_hi = 0, bi_lo =
>> 0}, ip_posid = 0}, t_tableOid = 0,
>>    t_data = 0x0}, tts_off = 0}
>>
>> Generally, tts_dataRow should have a value as I think. But as you can
>> see above, tts_dataRow is null. Therefore, it results in Postgres-XC
>> cann't deform tts_dataRow into datum arrays. I don't know whether I am
>> right. I don't know why such a problem occurred. I hope you could give
>> me some advice. Only the count(t2.*) will result in such a problem.
>> Other aggregates function such as count(t2.q1) or sum(t2.q1) will not
>> cause the problem.
>>
>> Btw, the following is the core dump infomation:
>> (gdb) bt
>> #0  0x0000000000450ec9 in heap_form_minimal_tuple
>> (tupleDescriptor=0x8b061d8, values=0x8b063e8, isnull=0x8b06408 "")
>>    at heaptuple.c:1565
>> #1  0x0000000000598cae in ExecCopySlotMinimalTuple (slot=0x8b040a8) at
>> execTuples.c:790
>> #2  0x00000000007a4e22 in tuplestore_puttupleslot (state=0x8b13d90,
>> slot=0x8b040a8) at tuplestore.c:546
>> #3  0x00000000005a5b5a in ExecMaterial (node=0x8b05930) at nodeMaterial.c:109
>> #4  0x000000000058d563 in ExecProcNode (node=0x8b05930) at execProcnode.c:428
>> #5  0x00000000005a7bb0 in ExecNestLoop (node=0x8b049f0) at nodeNestloop.c:154
>> #6  0x000000000058d52d in ExecProcNode (node=0x8b049f0) at execProcnode.c:413
>> #7  0x000000000059e28d in agg_fill_hash_table (aggstate=0x8b04430) at
>> nodeAgg.c:1054
>> #8  0x000000000059de85 in ExecAgg (node=0x8b04430) at nodeAgg.c:833
>> #9  0x000000000058d599 in ExecProcNode (node=0x8b04430) at execProcnode.c:440
>> #10 0x000000000058ac36 in ExecutePlan (estate=0x8b03b10,
>> planstate=0x8b04430, operation=CMD_SELECT, numberTuples=0,
>>    direction=ForwardScanDirection, dest=0x8af1de0) at execMain.c:1520
>> #11 0x0000000000588edc in standard_ExecutorRun (queryDesc=0x8a8bcc0,
>> direction=ForwardScanDirection, count=0)
>>    at execMain.c:312
>> #12 0x0000000000588de5 in ExecutorRun (queryDesc=0x8a8bcc0,
>> direction=ForwardScanDirection, count=0)
>>    at execMain.c:261
>> #13 0x000000000068f7a5 in PortalRunSelect (portal=0x8b01b00, forward=1
>> '\001', count=0, dest=0x8af1de0)
>>    at pquery.c:967
>> #14 0x000000000068f448 in PortalRun (portal=0x8b01b00,
>> count=9223372036854775807, isTopLevel=1 '\001',
>>    dest=0x8af1de0, altdest=0x8af1de0, completionTag=0x7fff49c60db0
>> "") at pquery.c:793
>> #15 0x000000000068983a in exec_simple_query (
>>    query_string=0x8a75f40 "select count(t2.*), t2.q1 from test t1
>> left join test t2 on (t1.q2 = t2.q1) group by t2.q1;") at
>> postgres.c:1053
>> #16 0x000000000068d7a8 in PostgresMain (argc=4, argv=0x89cb560,
>> username=0x89cb520 "postgres") at postgres.c:3766
>> #17 0x000000000065619e in BackendRun (port=0x89ecbf0) at postmaster.c:3607
>> ---Type <return> to continue, or q <return> to quit---
>> #18 0x00000000006556fb in BackendStartup (port=0x89ecbf0) at postmaster.c:3216
>> #19 0x0000000000652ac6 in ServerLoop () at postmaster.c:1445
>> #20 0x000000000065226c in PostmasterMain (argc=9, argv=0x89c8910) at
>> postmaster.c:1098
>> #21 0x00000000005d9bcf in main (argc=9, argv=0x89c8910) at main.c:188
>>
>> Looking forward your reply.
>>
>> Regards,
>> Benny
>>
>>
>>
>>
>> 在 2010年12月17日 下午11:20，Mason Sharp <mas...@en...> 写道：
>>> On 12/16/10 9:00 PM, xiong wang wrote:
>>>> Hi Mason,
>>>>
>>>> I also found some other errors after I submit the patch, which is
>>>> relative with such a bug. I will fix the problems your mentioned and
>>>> we found.
>>>
>>> OK. If it involves multiple remote queries (or join reduction) and looks
>>> difficult, it might make more sense to let us know. I think Pavan is
>>> very familiar with that code and might be able to fix it quickly.
>>>
>>> Mason
>>>
>>>
>>>> Regards,
>>>> Benny
>>>>
>>>> 在 2010年12月17日 上午3:05，Mason Sharp <mas...@en...> 写道：
>>>>>
>>>>> ---------- 已转发邮件 ----------
>>>>> 发件人： xiong wang <wan...@gm...>
>>>>> 日期： 2010年12月15日 上午11:02
>>>>> 主题： patch for bug#3126459:select error ： (group by .. order by.. )
>>>>> 收件人： pos...@li...
>>>>> Dears,
>>>>> The enclosure is the patch for bug#3126459:select error ： (group by ..
>>>>> order by.. ).
>>>>> Your advice will be appreiciated.
>>>>> Btw, I modified an error in my view that the variable standardPlan is
>>>>> always a free pointer.
>>>>> Regards,
>>>>> Benny
>>>>>
>>>>> Thanks, Benny.
>>>>>
>>>>> You definitely are addressing a bug that got introduced at some point, but
>>>>> now I get a different error for the case in question:
>>>>>
>>>>> mds=# select t1.q2,
>>>>> count(t2.*)
>>>>> from int8_tbl t1 left join int8_tbl t2 on (t1.q2 =
>>>>> t2.q1)
>>>>> group by t1.q2 order by 1;
>>>>> ERROR:  invalid reference to FROM-clause entry for table "int8_tbl"
>>>>>
>>>>> That is probably due to general RemoteQuery handling and aliasing.
>>>>>
>>>>> Anyway, I can imagine that your fix also addresses other reported issues.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Mason
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Lotusphere 2011
>>>>> Register now for Lotusphere 2011 and learn how
>>>>> to connect the dots, take your collaborative environment
>>>>> to the next level, and enter the era of Social Business.
>>>>> http://p.sf.net/sfu/lotusphere-d2d
>>>>>
>>>>> _______________________________________________
>>>>> Postgres-xc-developers mailing list
>>>>> Pos...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>
>>>>>
>>>>> --
>>>>> Mason Sharp
>>>>> EnterpriseDB Corporation
>>>>> The Enterprise Postgres Company
>>>>> This e-mail message (and any attachment) is intended for the use of
>>>>> the individual or entity to whom it is addressed. This message
>>>>> contains information from EnterpriseDB Corporation that may be
>>>>> privileged, confidential, or exempt from disclosure under applicable
>>>>> law. If you are not the intended recipient or authorized to receive
>>>>> this for the intended recipient, any use, dissemination, distribution,
>>>>> retention, archiving, or copying of this communication is strictly
>>>>> prohibited. If you have received this e-mail in error, please notify
>>>>> the sender immediately by reply e-mail and delete this message.
>>>>>
>>>
>>>
>>> --
>>> Mason Sharp
>>> EnterpriseDB Corporation
>>> The Enterprise Postgres Company
>>>
>>>
>>> This e-mail message (and any attachment) is intended for the use of
>>> the individual or entity to whom it is addressed. This message
>>> contains information from EnterpriseDB Corporation that may be
>>> privileged, confidential, or exempt from disclosure under applicable
>>> law. If you are not the intended recipient or authorized to receive
>>> this for the intended recipient, any use, dissemination, distribution,
>>> retention, archiving, or copying of this communication is strictly
>>> prohibited. If you have received this e-mail in error, please notify
>>> the sender immediately by reply e-mail and delete this message.
>>>
>>>
>>
>