Problem with nested left-joins and coalesce - Mailing list pgsql-sql
| From | ai |
|---|---|
| Subject | Problem with nested left-joins and coalesce |
| Date | |
| Msg-id | 01a301cc5597$e1b5ea40$a521bec0$@mail.ru Whole thread Raw |
| Responses |
Re: Problem with nested left-joins and coalesce
|
| List | pgsql-sql |
<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi!</span><p class="MsoNormal"><span lang="EN-US">I havestrange issue with nested left-joins in postgresql...</span><p class="MsoNormal"><span lang="EN-US">It's hard to explain,but easy to show =)</span><p class="MsoNormal"><span lang="EN-US">here we are:</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">SELECT * FROM</span><p class="MsoNormal"><span lang="EN-US">(</span><pclass="MsoNormal"><span lang="EN-US"> SELECT 1 as key1</span><p class="MsoNormal"><spanlang="EN-US">) sub1</span><p class="MsoNormal"><span lang="EN-US">LEFT JOIN </span><p class="MsoNormal"><spanlang="EN-US">(</span><p class="MsoNormal"><span lang="EN-US"> SELECT sub3.key3, value2FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key3</span><p class="MsoNormal"><span lang="EN-US"> ) sub3</span><p class="MsoNormal"><span lang="EN-US"> LEFT JOIN </span><p class="MsoNormal"><spanlang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2</span><p class="MsoNormal"><spanlang="EN-US"> FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key5</span><p class="MsoNormal"><span lang="EN-US"> ) sub5</span><p class="MsoNormal"><span lang="EN-US"> LEFT JOIN</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT 1 as key6, value1</span><p class="MsoNormal"><span lang="EN-US"> FROM</span><p class="MsoNormal"><span lang="EN-US"> (</span><p class="MsoNormal"><span lang="EN-US"> SELECT NULL::integer as value1</span><p class="MsoNormal"><spanlang="EN-US"> ) sub7</span><p class="MsoNormal"><spanlang="EN-US"> WHERE false</span><p class="MsoNormal"><spanlang="EN-US"> ) sub6 ON false</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> )</span><p class="MsoNormal"><span lang="EN-US"> sub4 ON sub4.key5=sub3.key3</span><p class="MsoNormal"><span lang="EN-US">)</span><p class="MsoNormal"><spanlang="EN-US">sub2 ON sub1.key1 = sub2.key3</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">The result of this query:</span><p class="MsoNormal"><span lang="EN-US">key1;key3;value2</span><pclass="MsoNormal"><span lang="EN-US">1;1;NULL</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">And this is the problem - value2 can't be NULL because of COALESCEin sub4 (at least I think that it can't be =))</span><p class="MsoNormal"><span lang="EN-US">Anyway if we'll change</span><p class="MsoNormal"><span lang="EN-US">SELECT sub3.key3, sub4.value2 FROM</span><p class="MsoNormal"><spanlang="EN-US">with</span><p class="MsoNormal"><span lang="EN-US">SELECT sub3.key3, value2 FROM</span><pclass="MsoNormal"><span lang="EN-US">we will got correct result:</span><p class="MsoNormal"><span lang="EN-US">key1;key3;value2</span><pclass="MsoNormal"><span lang="EN-US">1;1;1</span><p class="MsoNormal"><span lang="EN-US">Isthere something wrong with my mind&hands? or is it a bug?</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Thanks in advance!</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:10.0pt;color:#1F497D;mso-fareast-language:RU">Kindregards</span><p class="MsoNormal"><span lang="EN-US"style="font-size:10.0pt;color:#1F497D;mso-fareast-language:RU">Alex</span><p class="MsoNormal"> </div>