Postgres Pro
Downloads
Home   >   mailing lists

Thread: [BUGS] Something strang on "left join"

[BUGS] Something strang on "left join"

From
陳世泓
Date:

Hi,

 

        My pgsql version is 9.6, and I got something wrong result while using left join.

       

        We used “left join” to join t1 and t2, the result of the first row was wrong ,here is the LEFT JOIN result.  

 

1.     select * from cicifcif

2.     select * from eccifidi

3.     select * from cicifcif t1 left join eccifidi t2 on t1."CI-CUST-NO" = t2."EC-CUST-NO" ORDER BY T1."CI-CUST-NO"

 

  THE LEFT JOIN RESULT OF THE FIRST ROW , COLUMNS OF eccifidi SHOULD BE “222”,”N”,”Y”….

 

Kind regards,

 

Adam.

 



本電子郵件【包括附件】可能載有機密、專有或受法律保護之訊息或資料，並僅供收件人收受。如您並非本郵件之預定收件人，即無權閱讀、列印、使用、保留、複製、散佈或揭露本郵件及/或其任何部分。如您錯誤地收受本郵件，請立即將之從郵件系統中銷毀或刪除，並通知寄件人。金財通商務科技服務(股)公司謹提醒您，任何未經授權即以前述方式利用本郵件之行為將可能侵害本公司權益，為法律所嚴格禁止。郵件中任何與本公司營業無關之內容，不得視為本公司之立場或意見。感謝您的配合。

This e-mail with its any attachment are confidential and may also be legally privileged. If you are not the addressee you shall not read, print, utilize, reserve, copy, forward, or disclose any part of it. If you have received this e-mail in error, please immediately destroy or delete it from your system and promptly notify the sender. Please also be noted that it is prohibited to use or take any action based on the contents of the e-mail without BankPro's prior and explicit permission. BankPro is not liable for any information contained in the e-mail that is irrelevant to its business. Sincerely thank you for your cooperation.

Re: [BUGS] Something strang on "left join"

From
Tomas Vondra
Date:

On 09/28/2017 08:17 AM, 陳世泓 wrote:
> Hi,
> 
>  
> 
>         My pgsql version is 9.6, and I got something wrong result while
> using left join.
> 
>        
> 
>         We used “left join” to join t1 and t2, the result of the first
> row was wrong ,here is the LEFT JOIN result.  
> 
>  
> 
> 1.     select * from cicifcif
> 
> 2.     select * from eccifidi
> 
> 3.     select * from cicifcif t1 left join eccifidi t2 on
> t1."CI-CUST-NO" = t2."EC-CUST-NO" ORDER BY T1."CI-CUST-NO"
> 
>  
> 
>   THE LEFT JOIN RESULT OF THE FIRST ROW , COLUMNS OF eccifidi SHOULD BE
> “222”,”N”,”Y”….
> 

Chances are some of the ID columns contain spaces or some other
whitespace characters. Try length() on them:
   SELECT length("CI-CUST-NO") FROM ...   SELECT length("EC-CUST-NO") FROM ...

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Something strang on "left join"

From
jeanpierre.carayol@free.fr
Date:
I also suspect the ID columns values for lines identified by CUST-NO.

Sorry to ask an additionnal question here. There is a useful Oracle function dump() to check the internal field value. Is there anything similar with PostgreSQL ?

Many thanks for your help.

Best Regards,
Jean-Pierre

De: "Tomas Vondra" <tomas.vondra@2ndquadrant.com>
À: "陳世泓" <adam_chen@bankpro.com.tw>, pgsql-bugs@postgresql.org
Envoyé: Jeudi 28 Septembre 2017 11:14:11
Objet: Re: [BUGS] Something strang on "left join"



On 09/28/2017 08:17 AM, 陳世泓 wrote:
> Hi,
>
>  
>
>         My pgsql version is 9.6, and I got something wrong result while
> using left join.
>
>        
>
>         We used “left join” to join t1 and t2, the result of the first
> row was wrong ,here is the LEFT JOIN result.  
>
>  
>
> 1.     select * from cicifcif
>
> 2.     select * from eccifidi
>
> 3.     select * from cicifcif t1 left join eccifidi t2 on
> t1."CI-CUST-NO" = t2."EC-CUST-NO" ORDER BY T1."CI-CUST-NO"
>
>  
>
>   THE LEFT JOIN RESULT OF THE FIRST ROW , COLUMNS OF eccifidi SHOULD BE
> “222”,”N”,”Y”….
>

Chances are some of the ID columns contain spaces or some other
whitespace characters. Try length() on them:

    SELECT length("CI-CUST-NO") FROM ...
    SELECT length("EC-CUST-NO") FROM ...

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Something strang on "left join"

From
陳世泓
Date:
Hi,

I found that the length of the two columns are different, this should be the cause of the problem.
Thanks for your helping.

Kind regards,

Adam.



-----Original Message-----
From: Tomas Vondra [mailto:tomas.vondra@2ndquadrant.com]
Sent: Thursday, September 28, 2017 5:14 PM
To: 陳世泓 <adam_chen@bankpro.com.tw>; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Something strang on "left join"



On 09/28/2017 08:17 AM, 陳世泓 wrote:
> Hi,
>
>
>
>         My pgsql version is 9.6, and I got something wrong result
> while using left join.
>
>
>
>         We used “left join” to join t1 and t2, the result of the first
> row was wrong ,here is the LEFT JOIN result.
>
>
>
> 1.     select * from cicifcif
>
> 2.     select * from eccifidi
>
> 3.     select * from cicifcif t1 left join eccifidi t2 on
> t1."CI-CUST-NO" = t2."EC-CUST-NO" ORDER BY T1."CI-CUST-NO"
>
>
>
>   THE LEFT JOIN RESULT OF THE FIRST ROW , COLUMNS OF eccifidi SHOULD
> BE “222”,”N”,”Y”….
>

Chances are some of the ID columns contain spaces or some other whitespace characters. Try length() on them:
   SELECT length("CI-CUST-NO") FROM ...   SELECT length("EC-CUST-NO") FROM ...

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

________________________________


本電子郵件【包括附件】可能載有機密、專有或受法律保護之訊息或資料，並僅供收件人收受。如您並非本郵件之預定收件人，即無權閱讀、列印、使用、保留、複製、散佈或揭露本郵件及/或其任何部分。如您錯誤地收受本郵件，請立即將之從郵件系統中銷毀或刪除，並通知寄件人。金財通商務科技服務(股)公司謹提醒您，任何未經授權即以前述方式利用本郵件之行為將可能侵害本公司權益，為法律所嚴格禁止。郵件中任何與本公司營業無關之內容，不得視為本公司之立場或意見。感謝您的配合。

This e-mail with its any attachment are confidential and may also be legally privileged. If you are not the addressee
youshall not read, print, utilize, reserve, copy, forward, or disclose any part of it. If you have received this e-mail
inerror, please immediately destroy or delete it from your system and promptly notify the sender. Please also be noted
thatit is prohibited to use or take any action based on the contents of the e-mail without BankPro's prior and explicit
permission.BankPro is not liable for any information contained in the e-mail that is irrelevant to its business.
Sincerelythank you for your cooperation.
 

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Something strang on "left join"

From
Tomas Vondra
Date:
On 09/28/2017 12:01 PM, jeanpierre.carayol@free.fr wrote:
> I also suspect the ID columns values for lines identified by CUST-NO.
> 
> Sorry to ask an additionnal question here. There is a useful Oracle
> function dump()
> <https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm#SQLRF00635>
> to check the internal field value. Is there anything similar with
> PostgreSQL ?
> 

I don't think there's anything like that in PostgreSQL directly, but you
could use "orafce" extension [1] which provides various functions
available on Oracle, including dump().

[1] https://github.com/orafce/orafce

But you could also use 'quote_ident()' which simply quotes the value, so
any additional spaces are quite obvious.

Or even better - fix the schema and make the fields INT or BIGINT, which
will make the fields both more efficient and resilient to such issues.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs