Re: [HACKERS] New CORRESPONDING clause design - Mailing list pgsql-hackers

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.stehule@gmail.com> wrote:

hi

2017-03-09 17:19 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2017-03-09 13:18 GMT+01:00 Surafel Temesgen <surafel3000@gmail.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.stehule@gmail.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