mysql query equivalent to a condition. Any suggestions?

Question

I am not a sql expert.

I want to implement a query equivalent to this condition

If  ( (first-access-of-error1 = first-access-of-error2 and 
       second-access-of-error1 = second-access-of-error2) OR 
      (first-access-of-error1 = second-access-of-error2 and
       second-accessr-of-error1 = first-access-of-error2) )

I attempted it with something like:

select d.id,
       concat(a.variable_name,"|",a.file_url,"|",a.line_number,"|",a.stacktrace)
             as FirstAccess_Params, 
       concat(b.variable_name,"|",b.file_url,"|",b.line_number,"|",b.stacktrace) 
             as SecondA_Params 
from defect d 
right join (accessor a, accessor b) 
   on (d.id=a.defect_id and d.id=b.defect_id and a.id<b.id) 
where d.category_id=0 and d.relationship_id!=-1 
group by FirstAccess_Params,SecondA_Params

With above query I am able to work this condition out:

(first-access-of-error1 = first-access-of-error2 and 
 second-access-of-error1 = second-access-of-error2)

But I am having trouble like how to accomplish this condition:

(first-access-of-error1 = second-access-of-error2 and
 second-accessr-of-error1 = first-access-of-error2)

Any help is appreciated? Any ideas are welcome..

This is schema for Accessor table

<createTable tableName="accessor">
        <column autoIncrement="true" name="id" type="BIGINT UNSIGNED">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="defect_id" type="BIGINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="operation" type="TINYINT UNSIGNED"/>
        <column name="variable_name" type="VARCHAR(128)"/>
        <column name="object_address" type="VARCHAR(64)"/>
        <column name="type" type="TINYINT UNSIGNED"/>
        <column name="module_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="file_url" type="VARCHAR(256)"/>
        <column name="function_name" type="VARCHAR(64)"/>
        <column name="line_number" type="SMALLINT UNSIGNED"/>
        <column name="accessing_order" type="TINYINT UNSIGNED"/>
        <column name="stacktrace_type" type="TINYINT UNSIGNED"/>
        <column name="stacktrace" type="VARCHAR(2048)"/>
        <column name="parameter" type="VARCHAR(5120)"/>
    </createTable>

And this is schema for defect Table

<createTable tableName="defect">
        <column autoIncrement="true" name="id" type="BIGINT UNSIGNED">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column defaultValueNumeric="0" name="rule_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column defaultValueBoolean="false" name="hide" type="BIT">
            <constraints nullable="false"/>
        </column>
        <column defaultValueNumeric="0" name="relationship_id" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="category_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="sub_category_id" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="module1_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="module2_id" type="INT UNSIGNED"/>
        <column name="execution_instance_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="application_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="project_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="target_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="testsuite_id" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="timestamp" type="INT UNSIGNED">
            <constraints nullable="false"/>
        </column>
        <column name="priority" type="BIT"/>
        <column name="status" type="BIT"/>
        <column name="assignee" type="VARCHAR(64)"/>
        <column name="label_ids" type="VARCHAR(1024)"/>
        <column name="remark" type="VARCHAR(512)"/>
        <column name="accessor_ids" type="VARCHAR(1024)"/>
        <column name="parameter" type="VARCHAR(2048)"/>
        <column defaultValueNumeric="0" name="parent_id" type="BIGINT">
            <constraints nullable="false"/>
        </column>
    </createTable>

Better simplify, make it schematic. Better readable already! — Joop Eggen, Jun 25 '13 at 07:47
Thanks @JoopEggen for reply. But i couldn't understand 'make it schematic' ? Can you put in few more lines? And thanks for editing it anyway. — Tyagi Akhilesh, Jun 25 '13 at 07:56
I meant that it is a bit much info: `table1.a = table2.a and table1.b = table2.b` or `table1.a = table2.b and table1.b = table2.a` would be more clear. Sorry, must work today. — Joop Eggen, Jun 25 '13 at 09:16
Here is an SQL Fiddle with your code, as I see it: http://sqlfiddle.com/#!2/1c76c/5 i don't quite understand what values do you want to get from 'table a' and what values from 'table d'!!? Or do you want to make different conditions on query rows? — Gimmy, Jun 25 '13 at 09:30
@Gimmy All I want to do is filter duplicates from table defect on conditions I mentioned above. The data on which these conditions shall work is in accessor table. So ultimately it shall be something like: select group_concat(d.id) [ query for which I posted having count(*)>1] — Tyagi Akhilesh, Jun 25 '13 at 09:56
Where do you take the values for 'first-access-of-error1', 'first-access-of-error2', 'second-access-of-error1', 'second-access-of-error2' — Gimmy, Jun 25 '13 at 09:56
-access-of-error is nothing but a key generated using a.variable_name,a.file_url,a.line_number,a.stacktrace — Tyagi Akhilesh, Jun 25 '13 at 09:59
BTWn.. I was reading and searching online in the meanwhile. My instinct is telling me to approach this problem by create view and then do something with the view. — Tyagi Akhilesh, Jun 25 '13 at 10:01

score 0 · Answer 1 · answered Jun 25 '13 at 10:27

0

Consider this jsFiddle: http://sqlfiddle.com/#!2/c25e1/1 . It doesn't show duplicates. Also think about using DISTINCT sql command.

answered Jun 25 '13 at 10:27

Gimmy

3,781
2
18
27

Thanks @Gimmy for all your effort. But I have solved this issue. – Tyagi Akhilesh Jun 26 '13 at 13:07
You should post your solution as an answer, so others could benefit from this question. – Gimmy Jun 26 '13 at 13:12
Surely. I shall do that tomorrow. :) – Tyagi Akhilesh Jun 27 '13 at 19:01

score 0 · Answer 2 · edited May 23 '17 at 12:05

For the completeness of answer:

To accomplish first condition:

(first-access-of-error1 = first-access-of-error2 and 
   second-access-of-error1 = second-access-of-error2)

My code was sort of correct. Look at similar thing here https://stackoverflow.com/a/347300/1229355

The challenging part was to accomplish second condition:

(first-access-of-error1 = second-access-of-error2 and

second-accessr-of-error1 = first-access-of-error2)

For This: I created a new table. Though I started with the view, then moved to temporary table and then final to table. With view, I was having trouble in performance. As query was spending a lot of time in copying tmp table. With temporary table, I cannot use it in one query more then once, so it was not an option for me. So I had to stick with the table only.

Created table like this:

create table myDraceView (index(id)) as select d.id,concat_ws('|',a.variable_name,a.file_url,a.line_number,a.stacktrace) as FirstAccess_Params,"
                   + "concat_ws('|',b.variable_name,b.file_url,b.line_number,b.stacktrace) as SecondA_Params from defect d right join (accessor a, accessor b) on (d.id=a.defect_id and d.id=b.defect_id and a.id<b.id) where d.category_id=0 and d.relationship_id!=-1 and d.defect_level='"
                   + type
                   + "' and "
                   + mdaCondition;

(Sorry rushing.. have no time to write the query formally... shall do it later)

Then I did:

"select ids from (select concat( if( e1.id <= e2.id, e1.id, e2.id ),"
                   + CONCAT_SEP
                   + ",if( e1.id > e2.id, e1.id, e2.id ) ) as ids,e1.FirstAccess_Params as e1_FA,e1.SecondA_Params as e1_SA from myDraceView e1 join myDraceView e2 where e1.FirstAccess_Params=e2.SecondA_Params and e1.SecondA_Params=e2.FirstAccess_Params and e1.id!=e2.id group by e1.FirstAccess_Params,e1.SecondA_Params ) as l group by ids";

Any suggestions are welcome if anybody see any possible improvements that i can make.

mysql query equivalent to a condition. Any suggestions?

2 Answers2