Re: Something strang on "left join"

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(dot)vondra(at)2ndquadrant(dot)com]
Sent: Thursday, September 28, 2017 5:14 PM
To: 陳世泓 <adam_chen(at)bankpro(dot)com(dot)tw>; pgsql-bugs(at)postgresql(dot)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 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.