CODEWITHSUNDEEP
mysqlsql
abc_killer
abc_killer

Reputation: 11

Mysql: same query, different results

EDIT:

Sorry about unreadable query, I was under deadline. I managed to solve problem by breaking this query into two smaller ones, and doing some business logic in Java. Still want to know why this query can random times return two different results.

So, it randomly returns once all expected results, other time just half. I noticed that when I write it join per join, and execute after each join, in the end it returns all expected results. So am wandering if there's some kind of MySql memory or other limitation that it doesn't take whole tables in joins. Also read on undeterministic queries but not sure what to tell.

Please help, ask if needs clarification, and thank you in advance.

RESET QUERY CACHE;

SET SQL_BIG_SELECTS=1;

set @displayvideoaction_id = 2302;
set @ticSessionId = 3851;


select richtext.id,richtextcross.name,richtextcross.updates_demo_field,richtext.content from
(
select listitemcross.id,name,updates_demo_field,listitem.text_id from
(
select id,name, updates_demo_field, items_id from
(
SELECT id, name, answertype_id, updates_demo_field, 
@student:=CASE WHEN @class <> updates_demo_field THEN 0 ELSE @student+1 END AS rn,
@class:=updates_demo_field AS clset FROM
(SELECT @student:= -1) s,
(SELECT @class:= '-1') c,
(
select id, name, answertype_id, updates_demo_field from
(
select manytomany.questions_id from
(
select questiongroup_id from
(
select questiongroup_id from `ticnotes`.`scriptaction` where ticsession_id=@ticSessionId and questiongroup_id is not null
) scriptaction
inner join 
(
select * from `ticnotes`.`questiongroup` 
) questiongroup on scriptaction.questiongroup_id=questiongroup.id
) scriptgroup
inner join 
(
select * from `ticnotes`.`questiongroup_question` 
) manytomany on scriptgroup.questiongroup_id=manytomany.questiongroup_id
) questionrelation
inner join 
(
select * from `ticnotes`.`question`
) questiontable on questionrelation.questions_id=questiontable.id
where updates_demo_field = 'DEMO1' or updates_demo_field = 'DEMO2'
order by updates_demo_field, id desc
) t
having rn=0
) firstrowofgroup
inner join 
(
select * from `ticnotes`.`multipleoptionstype_listitem`
) selectlistanswers on firstrowofgroup.answertype_id=selectlistanswers.multipleoptionstype_id
) listitemcross
inner join 
(
select * from `ticnotes`.`listitem` 
) listitem on listitemcross.items_id=listitem.id
) richtextcross
inner join 
(
select * from `ticnotes`.`richtext` 
) richtext on richtextcross.text_id=richtext.id;

Upvotes: 0

Views: 439

Answers (1)

RandomWhiteTrash
RandomWhiteTrash

Reputation: 4014

My first impression is - don't use short cuts to describe your tables. I am lost at which td3 is where ,then td6, tdx3... I guess you might be lost as well. If you name your aliases more sensibly there will be less chance to get something wrong and mix 6 with 8 or whatever.

Just a sugestion :)

There is no limitation on mySQL so my bet would be on human error - somewhere there join logic fails.

Upvotes: 4

Related Questions