Re: New CORRESPONDING clause design

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@mail.gmail.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
>>>
>>>
>>
>