Databases
StackOverflow2013
Postsdbo
POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
    primarykey
    data
    text
    <p><strong>VERSION</strong> I'm using Server version: 5.1.36-community-log MySQL Community Server (GPL)</p> <p>I've finally contrived a easy example to reproduce it easily!</p> <p>setup:</p> <pre><code>create table t1(id integer unsigned,link integer unsigned); create table t2(id integer unsigned auto_increment,primary key(id)); create table t3(id integer unsigned,content varchar(30)); insert into t1 value(1,null); insert into t2 value(1); insert into t3 value(1,'test'); </code></pre> <p>then run:</p> <pre><code>select t2.*,t3.* from t1 left join t2 on t1.link=t2.id left join t3 on t3.id=t2.id where t1.id=1; </code></pre> <p>will get this wrongly:</p> <pre><code>+------+------+---------+ | id | id | content | +------+------+---------+ | NULL | 1 | test | +------+------+---------+ </code></pre> <p>But if we create t2 this way,it won't happen:</p> <pre><code>create table t2(id integer unsigned); </code></pre> <p>So,it has something to do with primary key!</p> <p><strong>NEW FOUND</strong></p> <p>run this will not trigger the bug:</p> <pre><code>select t2.*,t3.* from t1 left join t2 on t1.link=t2.id left join t3 on t2.id=t3.id where t1.id=1; </code></pre> <p>So it also has something to do with join direction!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMisier
    1. USMisier
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat do the rest of your tables look like?
      singulars
      1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
      1. USrandom
    2. COIs it possible that the join is optimized out? Run an explain on the query to see what happens...
      singulars
      1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
      1. USZed
    3. COplease instead of displaying the results of your queries just show us all your tables with their respective contents!
      singulars
      1. POWhy can I select something out of left join on a NULL column?(with contrived example to reproduce it locally,probably a bug!)
      1. USmarkus
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload