Geek asks and answers

Strange number-row behavior affected by SELECT clause with INNER JOIN? (MySQL 5.1.52)

I had a strange problem with one of my test cases, which I cannot explain. It comes down to a query that should return 1 row, returning zero.

This is a request that fails.

SELECT roles.id FROM `roles` 
  INNER JOIN `accounts_roles` ON `roles`.id = `accounts_roles`.role_id 
  WHERE (`roles`.`id` = 9) 
    AND (`accounts_roles`.account_id = 6 ) LIMIT 1;

11:24:07  [SELECT - 0 row(s), 0.001 secs]  Empty result set fetched

And this is the part that I can’t explain.

If I change roles.idto *, I see that there is data.

SELECT * FROM `roles` 
  INNER JOIN `accounts_roles` ON `roles`.id = `accounts_roles`.role_id 
  WHERE (`roles`.`id` = 9) 
    AND (`accounts_roles`.account_id = 6 ) LIMIT 1;


id  name   authorizable_type  authorizable_id  created_at           updated_at  account_id  role_id  created_at           updated_at  
--  -----  -----------------  ---------------  -------------------  ----------  ----------  -------  -------------------  ----------  
9   owner  Couple             1                2010-11-30 11:13:31  (null)      6           9        2010-11-30 11:13:31  (null)

Why do the columns that I select have any meaning in the returned totals rows?

These are the two tables in question:

describe roles;

COLUMN_NAME        COLUMN_TYPE  IS_NULLABLE  COLUMN_KEY  COLUMN_DEFAULT  EXTRA           
-----------------  -----------  -----------  ----------  --------------  --------------  
id                 int(11)      NO           PRI         (null)          auto_increment  
name               varchar(40)  YES                      (null)                          
authorizable_type  varchar(30)  YES                      (null)                          
authorizable_id    int(11)      YES          MUL         (null)                          
created_at         datetime     YES                      (null)                          
updated_at         datetime     YES                      (null)    


describe accounts_roles;

COLUMN_NAME  COLUMN_TYPE  IS_NULLABLE  COLUMN_KEY  COLUMN_DEFAULT  EXTRA  
-----------  -----------  -----------  ----------  --------------  -----  
account_id   int(11)      YES          MUL         (null)                 
role_id      int(11)      YES          MUL         (null)                 
created_at   datetime     YES                      (null)                 
updated_at   datetime     YES                      (null)
+3
source share
2 answers

, - . , MySQL, .

$ mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.52 Source distribution


$ mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.55 Source distribution

postgres .

0

... "" - , , 1 . , .

SELECT 
      roles.id 
   FROM 
      roles
         INNER JOIN accounts_roles ar
            ON roles.id = ar.role_id
           AND ar.account_id = 6 ) 
   WHERE 
      roles.id = 9
+1

More articles:


All Articles