|
|
TomDog
User
Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
|
This query
| Code: |
from r in RightsToken
select new
{
Action = r.Action.PadRight(2, '0'),
} |
generates this SQL
| Code: |
SELECT CASE WHEN CHAR_LENGTH("LPLA_1"."ACTION")>=2 THEN "LPLA_1"."ACTION" ELSE RPAD("LPLA_1"."ACTION",
2,
'0') END AS "Action"
FROM
"AQD"."EN_RIGHTS_TOKEN" "LPLA_1" |
Giving an OracleException ORA-00904: "CHAR_LENGTH": invalid identifier.
Running the SQL directly and changing CHAR_LENGTH to LENGTH it works
| Code: |
SELECT CASE WHEN LENGTH("LPLA_1"."ACTION")>=2 THEN "LPLA_1"."ACTION" ELSE RPAD("LPLA_1"."ACTION",
2,
'0') END AS "Action"
FROM
"AQD"."EN_RIGHTS_TOKEN" "LPLA_1" |
IOW CHAR_LENGTH is getting emitted instead of LENGTH
RuntimeBuild 5.5.1_NetFull
| Code: |
--------------------------------------------------------
-- DDL for Table EN_RIGHTS_TOKEN
--------------------------------------------------------
CREATE TABLE "AQD"."EN_RIGHTS_TOKEN"
( "RIGHTS_TOKEN_ID" NUMBER(10,0),
"ACTION" VARCHAR2(50 CHAR),
"OBJECT_ID" NUMBER(10,0) DEFAULT (0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "AQD_DATA" ;
|
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
Holy crap this is a pretty stupid bug. 
I searched for Oracle CHAR_LENGTH and arrived here: https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/string-functions.html I thought: hmm, why would it crash? It uses the right function!
So did some more digging and saw this: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions076.htm clearly there are apparently 2 functions! But ... I couldn't really find a CHAR_LENGTH usage within oracle.
Then it hit me: The docs I thought were oracle docs, were ... mysql docs (which is owned by oracle) on the oracle server (docs.oracle.com).. Check the url!
So, clearly a bug we need to fix asap (it's been in there since 2008 btw). Yes, I know what you're thinking: why isn't there a test for this. I don't know. At the time they were written (2008 ) the function mappings were tested directly in SQL, they worked, mappings were ported and something went wrong there.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
|
Fixed in next build. We're still waiting for info on an issue that could be a regression issue in the graph traverser so we'd like to fix that too before uploading a hotfix build. We'll report in this thread when the new hotfix build is available.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
Fix is now available as hotfix 5.4.5 and 5.5.2
|
|
|
TomDog
User
Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
|
Ha funny, same thing happened with our DB guy when I asked him about that function, took him awhile to realize it was MYSQL he was reading about.
Thanks for sorting, though one of our tests failed after the update, it seem the save order of a UOW changed and it started giving FK violations. Maybe that graph traverser change?
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
We sort the UoW queues now on type after the topological sort, which is just a group on topologically sorted types in the graph, and it shouldn't give any FK issues as the ordering is still the same: a topological sort. That the order in the queues can differ is a given, it's not defined what the order can be, only that the ordering won't result in FK constraint violation (so saving Order before Customer while Order depends on Customer).
In your case the queue is in the wrong order?
|
|
|
TomDog
User
Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
|
|
I compared the UOW before and after the update and it looks the same: 6 collections to save (_collectionsToSave) in the order they should be inserted, but looking in orm profiler the 4th collection is attempted to save first.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
That doesn't tell me anything, sorry. FK constraints are violated however the relationship isn't present in the entities specified? Otherwise it can't happen.
Sorting the entities is done by looking at the dependencies they have in memory, it can't know the dependencies inside the DB which can always be wrong for some reason (some other thread performs updates etc.)
What it DID was for each entity perform a topological sort of the graph visible through that entity, then re-order the queue at that point with that graph, based on the dependencies of the types in that graph.
This is an very expensive operation as the more entities in the queue the slower it became (O(n*m)).
So to fix that we still collect the dependencies of the types (which don't change) but over the total set of the entities to save. Then sort the queue once, based on the type dependencies. This could lead to a different order, but always based on the dependencies of the types/entities found.
I don't know what you're doing with the uow, you simply have collections which have to be persisted in that order and the entities inside the collection have to be persisted in that order as well? As ordering of that is undefined, always has been.
|
|
|
TomDog
User
Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
|
| Otis wrote: |
| I don't know what you're doing with the uow, you simply have collections which have to be persisted in that order and the entities inside the collection have to be persisted in that order as well? As ordering of that is undefined, always has been. |
There is a 6 level master detail.
The entities at the top is in the first collection and next one is in the second collection so on down.
There is no in-memory connection between any of the entities i.e no graph. Imagine taking a graph of the 6 levels disconnecting them then adding the entities of each type to the UOW as a collection per type
Order inside the collection is not important.
Don't know what else to tell you.
I've got a workaround so I'm not so bothered, just a heads up that something somewhere has changed.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
Ah thanks. Yes the ordering in the final queue is different indeed. As this was previously undefined (and already changed at will with 5.5. due to the type grouping for batching) we don't see it as a bug that things are in a different order, as long as the ordering of the types in the queues is correct based on their dependencies in the graph that's about to be persisted. But it is inconvenient, agreed.
We couldn't find an algorithm to determine the type ordering from the final queue, and the regular algorithm (sort based on the dependencies of the types as found during traversal of the graph) works OK always so we kept that.
Your situation however does give an insight in a potential flaw in the algorithm. We collect the types during the sorting of the entities. But no entity in your entire UoW has a dependency (for the sake of argument). So they'll be ordered as they're added. Their types too: there are no dependent types found. So these are added to a multi-value dictionary for the adjacency lists but these are empty.
This adjacencylist set is simply enumerated and topologically sorted. However as all adjacency sets are empty, the order in which the types are collected is... in the order the dictionary is traversed, which is an undefined order and which isn't necessarily the same as the order in which they're added!
So in the case where there are no adjacency lists, the types should be kept in the order in which they're added. So we have to sort a separate list which is appended on the fly and which is going to have the same order if the adjacency lists are empty. This then should not result in any different order of entities in the final queue.
We'll look into adding this now for the new hotfix build.
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
Fixed in hotfix 5.5.2, now available 
|
|
|
TomDog
User
Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
559 posts
|
That made no difference alas
I tried repro-ing against northwind but I couldn't
|
|
|
Otis
LLBLGen Pro Team
Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37186 posts
|
| TomDog wrote: |
That made no difference alas
I tried repro-ing against northwind but I couldn't |
No difference? That's weird.
It's very odd tho, If you add the collections one at a time, and the entities have no relationships with each other, they should come out as-is, in the same order (as they're all of the same type).
I couldn't reproduce what you initially had tho, no matter what I tried. But in any case, the sorting on the types wasn't correct, so that's at least done properly done (albeit the effect of that wouldn't only be visible if the order of the key/value pairs would differ somehow from the order in which they were added, which in theory could happen).
|
|
|
|