Help with subquery

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Help with subquery

Ahmed El-Daly
Hello all,

I think this should explain my problem:

SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" AND u."status" = 'activated')
GROUP BY icd."click_date", icd."site_id";
Error: At line 2, column 168: Table 'U' not found (state=,code=0)

SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" WHERE u."status" = 'activated')
GROUP BY icd."click_date", icd."site_id";
Error: At line 2, column 170: Table 'U' not found (state=,code=0)

SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id")
GROUP BY icd."click_date", icd."site_id";
Error: java.lang.AssertionError: Internal error: Not a query: SELECT `S`.`uid`
FROM `REMOTES_SERVER`.`sites` AS `S`
INNER JOIN `REMOTES_SERVER `.`users` AS `U` ON `S`.`user_id` = `U`.`id` (state=,code=0)

SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id";
+----------------------------+
|            uid             |
+----------------------------+
| 0000000000000000000000000  |
| 1111111111111111111111111  |
+----------------------------+
3,453 rows selected (0.305 seconds)

SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" AND u."status" = 'activated';
+----------------------------+
|            uid             |
+----------------------------+
| 0000000000000000000000000  |
| 1111111111111111111111111  |
+----------------------------+
212 rows selected (0.112 seconds)

Any help appreciated :)

Thanks,
Ahmed


------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help with subquery

John Sichi
Administrator
Currently, IN subqueries inside of the ON clause of a JOIN don't work:

http://issues.eigenbase.org/browse/FRG-300

This is a limitation due to the approach taken by LucidDB's optimizer
for subquery rewrite, and is going to take a bit of effort to fix.

In your case, since you are using inner joins, it looks like you can
just rewrite them to avoid the nesting; for example, this rewrite of
your first query should be equivalent (I also renamed the inner alias to
avoid confusion):

SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as
"count"
FROM remotes_server."item_clicks_daily" icd
JOIN remotes_server."sites" s
ON s."uid" = icd."site_id"
WHERE s."uid" IN
(SELECT s1."uid" FROM remotes_server."sites" s1 JOIN
remotes_server."users" u ON s1."user_id" = u."id" AND u."status" =
'activated')
GROUP BY icd."click_date", icd."site_id";

If you needed outer joins, the rewrite would take more work.

JVS

Ahmed El-Daly wrote:

> Hello all,
>
> I think this should explain my problem:
>
> SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
> JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" AND u."status" = 'activated')
> GROUP BY icd."click_date", icd."site_id";
> Error: At line 2, column 168: Table 'U' not found (state=,code=0)
>
> SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
> JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" WHERE u."status" = 'activated')
> GROUP BY icd."click_date", icd."site_id";
> Error: At line 2, column 170: Table 'U' not found (state=,code=0)
>
> SELECT icd."click_date", icd."site_id", SUM(number_of_clicks) as "count" FROM remotes_server."item_clicks_daily" icd
> JOIN remotes_server."sites" s ON s."uid" = icd."site_id" AND s."uid" IN (SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id")
> GROUP BY icd."click_date", icd."site_id";
> Error: java.lang.AssertionError: Internal error: Not a query: SELECT `S`.`uid`
> FROM `REMOTES_SERVER`.`sites` AS `S`
> INNER JOIN `REMOTES_SERVER `.`users` AS `U` ON `S`.`user_id` = `U`.`id` (state=,code=0)
>
> SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id";
> +----------------------------+
> |            uid             |
> +----------------------------+
> | 0000000000000000000000000  |
> | 1111111111111111111111111  |
> +----------------------------+
> 3,453 rows selected (0.305 seconds)
>
> SELECT s."uid" FROM remotes_server."sites" s JOIN remotes_server."users" u ON s."user_id" = u."id" AND u."status" = 'activated';
> +----------------------------+
> |            uid             |
> +----------------------------+
> | 0000000000000000000000000  |
> | 1111111111111111111111111  |
> +----------------------------+
> 212 rows selected (0.112 seconds)
>
> Any help appreciated :)
>
> Thanks,
> Ahmed
>
>
> ------------------------------------------------------------------------------
> This SF.Net email is sponsored by the Verizon Developer Community
> Take advantage of Verizon's best-in-class app development support
> A streamlined, 14 day to market process makes app distribution fast and easy
> Join now and get one step closer to millions of Verizon customers
> http://p.sf.net/sfu/verizon-dev2dev 
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users
>


------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev 
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...