Menu

#2161 (ok 2.11) Wrong primary key used (results with subquery)

2.8.0.3
fixed
1
2015-02-05
2006-05-31
Percy
No

I had a child table that I wanted to add a foreign key
to. It told me there was a missing row in the parent
table so I went about with a query that selected all
child items where the parent row does not exist, and
then I wanted to manually delete each row

CREATE TABLE `parent` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`test` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

CREATE TABLE `child` (
`parent_id` INT UNSIGNED NOT NULL ,
`id` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `parent_id` , `id` )
) ENGINE = MYISAM ;

INSERT INTO `child` ( `parent_id` , `id` )
VALUES (
'1', '1'
);

select * from child where not exists (select id from
parent where child.parent_id = parent.id);

When I run the last query in the SQL tab, I get the one
row, as expected. However, if I try to delete that row
from the results, it gives me this error:

SQL query:

DELETE FROM `parent` WHERE `parent_id` =1 AND `id` =1
LIMIT 1

MySQL said: Documentation
#1054 - Unknown column 'parent_id' in 'where clause'

Another thing I noticed, when I run the select query,
the table at the top (like the current "active" table
in PMA) changes to "parent" instead of whatever other
table might have been active at the time, so obviously
it has something to do with the SQL parser or whatever,
although the symptoms of it are that it assumes the
primary key of the results is a different table than it
really is...

Discussion

  • Marc Delisle

    Marc Delisle - 2007-04-01

    Logged In: YES
    user_id=210714
    Originator: NO

    Indeed the parser does not (yet) support subqueries.

     
  • Marc Delisle

    Marc Delisle - 2007-04-06
    • assigned_to: nobody --> lem9
     
  • Marc Delisle

    Marc Delisle - 2007-04-06
    • priority: 5 --> 1
    • summary: Wrong primary key used for displaying results with subquery --> (ok 2.11) Wrong primary key used (results with subquery)
    • status: open --> open-fixed
     
  • Marc Delisle

    Marc Delisle - 2007-08-21
    • status: open-fixed --> closed-fixed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-fixed --> fixed