New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Prohibit any improper mixture of explicit and implicit joins [CORE2812] #3199
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovFix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovFix Version: 2.5.1 [ 10333 ] |
Modified by: @dyemanovFix Version: 2.5.1 [ 10333 ] => |
Commented by: @livius2 a think about this but is this really wrong query? look at adapted same query but you explicitly tell optimizer how to build streams by derived table ################### orginal query ###################### select * PLAN MERGE (SORT (TA NATURAL), SORT (JOIN (TB NATURAL, TC NATURAL))) this work wrong - resultset is wrong TA.col1=1 <> TC.col1=4 ################### addapted query ###################### select TX.TA_COL1, TX.TA_COL2, TX.TB_COL1, TX.TB_COL2, TC.COL1 AS TC_COL1, TC.COL2 AS TC_COL2 PLAN JOIN (JOIN (TX TA NATURAL, TX TB NATURAL), TC NATURAL) this query work ok for me - resultset look ok |
Commented by: @livius2 with indexes on every column addapted query work ok with plan |
Commented by: @dyemanov As far as I understand, your "original" query is wrong but the "adapted" one is correct. This is from the SQL spec POV. |
Commented by: @livius2 "orginal" is your query from description and you say that should be prohibited. my addapted query is "the same" only difference that i change TA, TB to derived tabe - may be optimizer should do this? |
Commented by: @dyemanov First of all, SQL is declarative. And if some declaration is defined to be invalid, I don't see any point in "fixing" it at the optimizer level, which is an implementation detail. Second, I don't see these two queries as equivalent, they're TA JOIN (TB LEFT JOIN TC) vs (TA JOIN TB) LEFT JOIN TC. |
Commented by: @livius2 I do not say that this should to be "fixed" select * but i do not see that any other db support this way ingress have very good info about failed execution "Relation 'ta' appearing in join_search condition is not involved in the join" |
Commented by: @dyemanov I've analyzed Oracle/MSSQL/Postgres before changing our behaviour and they all "agree" with the new rules. Your mistake is that your treat A, B join C as (A, B) join C while in fact this is A, (B join C). That said, I completely agree that a better error reporting would be great. I just wish it would be easier to accomplish :-) |
Commented by: @livius2 i say that (A join B) join C becaouse of derived table. as you can saw in plan returned for addapted query. if you go in that path query work ok. but i do not see simple possibility to make this choice in optimizer. |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: Done successfully Test Details: See also (in russian): http://sql.ru/forum/actualutils.aspx?action=gotomsg&tid=804601&msg=9751146 |
Commented by: @pcisar Test created. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @dyemanov
Is related to CORE94
Is related to CORE2378
Is related to QA322
Relate to CORE3693
Consider this query:
You can see that the outer join condition references the table from the implicit join. Currently, such queries either throw a famous "no current record to fetch" error or return incorrect result set, depending on the execution plan. Perhaps one lucky day they could even deliver a correct answer, I'm not sure ;-)
So far it has been considered being an optimizer bug and we have a few related tickets in the tracker. However, I was recently wondering whether this query is correct at all. I've found that both MSSQL and PostgreSQL throw an "invalid column reference" error for this query. The SQL specification mentions that a "joined_table" (i.e. an explicit join) is a table derived from the join expression. But we know that a derived table is prepared in its own (independent) context and cannot reference fields from the outer contexts. For me this clearly means that implicitly joined tables cannot be visible from the explicitly joined ones.
So I'm proposing to prohibit such field references as being invalid. Below are examples that should still work:
I doubt this limitation could badly affect any existing applications, but theoretically this is possible and should be taken into account. I have to admit though that I wouldn't feel comfortable with a yet another legacy compatibility configuration switch :-)
Commits: 401f31a 18a9e37 f9f4f90 90beb3c
====== Test Details ======
See also (in russian): http://sql.ru/forum/actualutils.aspx?action=gotomsg&tid=804601&msg=9751146
Additional explanations about why following WORKS in FB 3.0:
select 'case-4' msg, http://a.id from t1 b, t1 a join t1 c on http://a.id=c.id where http://a.id=b.id;
-- see in the letter by dimitr, 02-apr-2015 19:34
The text was updated successfully, but these errors were encountered: