Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14779

using left join causes incorrect results with materialization and derived tables

    XMLWordPrintable

Details

    • 5.5.59, 10.0.34

    Description

      SELECT t.SID, t.NAME,t.ID_NO,t.MOBILE,r.credit_status,t.amount,r.cust_active_amount,t.END_TIME,t.FILE_NAME
      FROM (select sid,name,id_no,recommend_id_no,mobile,amount,end_time,FILE_NAME from tc_white_list
      WHERE  recommend_id_no='ybr' ) t
        LEFT   JOIN (SELECT credit_status,id_no,cust_active_amount
                   from datacqsn.tc_credit_request WHERE sid in(SELECT max(sid)
                                                                from datacqsn.tc_credit_request where credit_status>'0' GROUP BY id_no)) r
          on t.ID_NO=r.id_no
      

      table t have two rows ,r have 0 rows ,result have no rows

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              altercyx bela sand
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.