Re: New CORRESPONDING clause design

2017-03-10 10:13 GMT+01:00 Surafel Temesgen <surafel3000(at)gmail(dot)com>:

> Yes, you are correct it should to work on CORRESPONDING clause case. SQL
> 20nn standard draft only said each query to be of the same degree in a case
> of set operation without corresponding clause. The attached patch is
> corrected as such .I add those new test case to regression test too
>

Thank you - I will recheck it.

Regards

Pavel

>
> Regards
>
>
> Surafel
>
> On Thu, Mar 9, 2017 at 9:49 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> hi
>>
>> 2017-03-09 17:19 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>
>>>
>>>
>>> 2017-03-09 13:18 GMT+01:00 Surafel Temesgen <surafel3000(at)gmail(dot)com>:
>>>
>>>> Hi ,
>>>>
>>>> Here is a patch corrected as your feedback except missed tests case
>>>> because corresponding by clause is implemented on the top of set operation
>>>> and you can’t do that to set operation without corresponding by clause too
>>>>
>>>
>>> I don't understand.
>>>
>>> The following statement should to work
>>>
>>> postgres=# select 10 as a, 20 as b union corresponding select 20 as a,
>>> 30 as b, 40 as c;
>>>
>>> ERROR: each UNION query must have the same number of columns
>>> LINE 1: ...elect 10 as a, 20 as b union corresponding select 20 as a, 3...
>>>
>>>
>>> Corresponding clause should to work like projection filter.
>>>
>>
>> I found a link to postgresql mailing list related to some older try to
>> this feature implementation
>>
>> https://www.postgresql.org/message-id/CAJZSWkX7C6Wmfo9Py4Ba
>> F8vHz_Ofko3AFSOsJPsb17rGmgBuDQ(at)mail(dot)gmail(dot)com
>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>>
>>>> Eg
>>>>
>>>>
>>>> postgres=# SELECT 1 a, 2 b, 3 c UNION SELECT 4 a, 5 b, 6 c, 8 d;
>>>>
>>>> ERROR: each UNION query must have the same number of columns
>>>>
>>>> LINE 1: SELECT 1 a, 2 b, 3 c UNION SELECT 4 a, 5 b, 6 c, 8 d;
>>>>
>>>> ^
>>>>
>>>> postgres=# create table t1(a int, b int, c int);
>>>>
>>>> CREATE TABLE
>>>>
>>>> postgres=# create table t2(a int, b int);
>>>>
>>>> CREATE TABLE
>>>>
>>>>
>>>>
>>>> postgres=# select * from t1 union select * from t2;
>>>>
>>>> ERROR: each UNION query must have the same number of columns
>>>>
>>>> LINE 1: select * from t1 union select * from t2;
>>>>
>>>>
>>>>
>>>> Thanks
>>>>
>>>> Surafel
>>>>
>>>> On Tue, Mar 7, 2017 at 10:26 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
>>>> > wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> I am sending a review of this interesting feature.
>>>>>
>>>>> I found following issues, questions:
>>>>>
>>>>> 1. unclosed tags <optional> in documentation
>>>>> 2. bad name "changeTargetEntry" - should be makeTargetEntry?
>>>>> 3. Why you removed lot of asserts in prepunion.c? These asserts should
>>>>> be valid still
>>>>> 4. make_coresponding_target has wrong formatting
>>>>> 5. error "%s queries with a CORRESPONDING clause must have at least
>>>>> one column with the same name" has wrong formatting, you can show position
>>>>> 6. previous issue is repeated - look on formatting ereport function,
>>>>> please, you can use DETAIL and HINT fields
>>>>> 7. corresponding clause should to contain column list (I am looking to
>>>>> ANSI/SQL 99) - you are using expr_list, what has not sense and probably it
>>>>> has impact on all implementation.
>>>>> 8. typo orderCorrespondingLsit(List *targetlist)
>>>>> 9. I miss more tests for CORRESPONDING BY
>>>>> 10. if I understand to this feature, this query should to work
>>>>>
>>>>> postgres=# SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY (c,b) SELECT 4 a, 5 b, 6 c, 8 d;
>>>>> ERROR: each UNION query must have the same number of columns
>>>>> LINE 1: ...1 a, 2 b, 3 c UNION CORRESPONDING BY (c,b) SELECT 4 a, 5 b, ...
>>>>>
>>>>> postgres=# create table t1(a int, b int, c int);
>>>>> CREATE TABLE
>>>>> Time: 63,260 ms
>>>>> postgres=# create table t2(a int, b int);
>>>>> CREATE TABLE
>>>>> Time: 57,120 ms
>>>>> postgres=# select * from t1 union corresponding select * from t2;
>>>>> ERROR: each UNION query must have the same number of columns
>>>>> LINE 1: select * from t1 union corresponding select * from t2;
>>>>>
>>>>> If it is your first patch to Postgres, then it is perfect work!
>>>>>
>>>>> The @7 is probably most significant - I dislike a expression list
>>>>> there. name_list should be better there.
>>>>>
>>>>> Regards
>>>>>
>>>>> Pavel
>>>>>
>>>>>
>>>>
>>>
>>
>