CODEWITHSUNDEEP
mysqlsql
Raz
Raz

Reputation: 1710

MySql INSERT ignores DISTINCT selection

I have a simple table with 1 column and I try to insert something using a query with INSERT and SELECT.

INSERT INTO workloadtable (test)
  SELECT DISTINCT a.test FROM runtable a 
    WHERE a.test = "awesome_testcase"
    AND NOT EXISTS(SELECT b.test FROM workloadtable b WHERE b.test = a.test)

Now the problem I am having is that this query inserts all the 4000+ testcases present in the 'runtable' and it ignores the DISTINCT statement. I also tried removing DISTINCT and adding GROUP BY a.test after the WHERE statement.

If I run only the SELECT query I get only one result which in this case is "awesome_testcase" and I have no idea why it behaves differently when placed inside the INSERT statement.

I also want to mention a few other things. The query is running like I mentioned above on a linux machine using phpMyAdmin. I tried the exact same query on a windows machine (without phpMyAdmin) with the same database and it worked fine... inserted only one row, not 4000. If I set the column to be a primary key, I obviously get an error when I try the query. This doesn't happen on the Windows machine.

I am sure I'm missing something here, but I can't figure out what.

EDIT:

Tried this in a different manner - same problem

insert into workloadtable (test)
select distinct a.test from runtable a 
    where a.test = "ST.WORKLOAD.REL.THN"
    and 0 = (select count(b.test) from workloadtable b where b.test = a.test)

EDIT2:

MySql versions are not the same on the machines. Linux running the 5.5 and windows 5.6. Can't change the version though because that will complicate other things.

I also tried using the console rather than phpMyAdmin UI and the problem persists.

Upvotes: 1

Views: 126

Answers (2)

memimomu
memimomu

Reputation: 161

There is a bug in MySQL where the table you are inserting into is also in a not exits clause.

You can resolve this by using a left join instead of a not exits. This typically results in faster execution time as well.

INSERT INTO workloadtable (test)
SELECT DISTINCT a.test
FROM runtable a 
  LEFT JOIN workloadtable b on b.test = a.test
WHERE a.test = "awesome_testcase"
AND b.test IS NULL

Upvotes: 0

Benvorth
Benvorth

Reputation: 7722

Build a temporary table to resolve the circle in your Query:

CREATE TEMPORARY TABLE myTemp
  (SELECT DISTINCT a.test as test FROM runtable a 
  WHERE a.test = "awesome_testcase"
  AND NOT EXISTS(SELECT b.test FROM workloadtable b WHERE b.test = a.test));

INSERT INTO workloadtable (test)
SELECT test FROM myTemp

DROP TEMPORARY TABLE myTemp

Upvotes: 1

Related Questions