Re: New CORRESPONDING clause design

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

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/CAJZSWkX7C6Wmfo9Py4BaF8vHz_
> 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
>>>>
>>>>
>>>
>>
>