CODEWITHSUNDEEP
mysqlview
Derek Wildstar
Derek Wildstar

Reputation: 533

SQL run fine but if used to create a view I got "Error Code: 1242. Subquery returns more than 1 row"

I'm running into some odd issue with MySQL. I've a rather long query (cutted below) that contains subquery:

select
    SITEID as ID,
    "Predicted" as KIND,
    ATTRIBUTE as ATTRIBUTE_ID,
    "1" as TYPE,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour where siteid = ID and attribute = ATTRIBUTE_ID and TS = date_format((now() - interval 4 hour), '%Y-%m-%d %H:00:00')) as MINUS4,
    if(PREDICTEDRATE is null, 0, PREDICTEDRATE) as CURRENT,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour where siteid = ID and attribute = ATTRIBUTE_ID and TS = date_format((now() + interval 1 hour), '%Y-%m-%d %H:00:00')) as PLUS1,
from
    predictionperhour
where
    TS = date_format(now(), '%Y-%m-%d %H:00:00')
group by
    SITEID, 
    ATTRIBUTE

If I run this query into SQL Workbench (without limit the results) I got back my 2027 rows and everything works fine. Now if I use the very same select to create a view and then try a simple select * from there I got the above error "Error Code: 1242. Subquery returns more than 1 row".

Question how this could happen? I've tried to modify the subquery to do a count but as far as I can see all the results are 1... I have even tried to add a LIMIT 1 but though in this case I do not hit any error I say that instead of getting back the expected result from subquery I got back 0.

Could you help me to find out where the issue is?

Thank you

Upvotes: 0

Views: 62

Answers (1)

user3714582
user3714582

Reputation: 1940

Try it like this using aliases for tables (p0, p1, p2). If we will not alias it, there could be column attribute = ATTRIBUTE_ID from the same table predictionperhour AS p0 from outer query not from inner query.

select
    SITEID as ID,
    "Predicted" as KIND,
    ATTRIBUTE as ATTRIBUTE_ID,
    "1" as TYPE,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour AS p1 where p1.siteid = p0.ID and p1.attribute = p0.ATTRIBUTE_ID and TS = date_format((now() - interval 4 hour), '%Y-%m-%d %H:00:00')) as MINUS4,
    if(PREDICTEDRATE is null, 0, PREDICTEDRATE) as CURRENT,
    (select if(PREDICTEDRATE is null, 0, PREDICTEDRATE) from predictionperhour AS p2 where p2.siteid = p0.ID and p2.attribute = p0.ATTRIBUTE_ID and TS = date_format((now() + interval 1 hour), '%Y-%m-%d %H:00:00')) as PLUS1,
from
    predictionperhour AS p0
where
    TS = date_format(now(), '%Y-%m-%d %H:00:00')
group by
    SITEID, 
    ATTRIBUTE

Upvotes: 1

Related Questions