Sybase ASA error in group by / count
Joined: 01-Sep-2008
Joined: 01-Sep-2008
Joined: 01-Sep-2008
Dan wrote:
Otis wrote:
Fixed in next build.
Excellent! When will the next build be available?
By the way, this is the first occasion I've had to ask for help. I am very impressed by the rapidity of your support. Thank you.
the build is done and waiting. We had some last-minute thingy which we're waiting for now. It will be in the next hour or so.
(edit) It's available.
Joined: 01-Sep-2008
Otis wrote:
the build is done and waiting. We had some last-minute thingy which we're waiting for now. It will be in the next hour or so.
(edit) It's available.
Got it, thank you. That worked for that problem.
However, using the same Linq query I now get the following:
'sp1test.vshost.exe' (Managed): Loaded 'E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\bin\Debug\sp1test.exe', Symbols loaded.
'sp1test.vshost.exe' (Managed): Loaded 'E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\bin\Debug\SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll'
'sp1test.vshost.exe' (Managed): Loaded 'E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\bin\Debug\PowershopData.dll'
'sp1test.vshost.exe' (Managed): Loaded 'E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\bin\Debug\SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll'
'sp1test.vshost.exe' (Managed): Loaded 'E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\bin\Debug\SD.LLBLGen.Pro.DQE.SybaseAsa.NET20.dll'
'sp1test.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[PowershopData.EntityClasses.SalesOrderEntity]).GroupBy(so => so.CustomerId).Where(g => (g.Count() = 1)).Select(g => new <>f__AnonymousType0`2(CustomerID = g.Key, Count = g.Count()))
'sp1test.vshost.exe' (Managed): Loaded 'Anonymously Hosted DynamicMethods Assembly'
'sp1test.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\iAnywhere.Data.SQLAnywhere\10.0.1.36802__f222fc4333e0d400\iAnywhere.Data.SQLAnywhere.dll'
'sp1test.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query:
Query: SELECT [LPLA_1].[customer_id], COUNT(*) AS LPAV_, COUNT(*) AS LPAV_1 FROM [DBA].[sales_order] [LPLA_1] GROUP BY [LPLA_1].[customer_id]
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query:
Query: SELECT [LPA_L1].[CustomerId] AS CustomerID, [LPA_L1].[LPAV_1] AS Count FROM (SELECT [LPLA_1].[customer_id], COUNT(*) AS LPAV_, COUNT(*) AS LPAV_1 FROM [DBA].[sales_order] [LPLA_1] GROUP BY [LPLA_1].[customer_id]) [LPA_L1] WHERE ( ( ( ( [LPA_L1].[LPAV_] = ?))))
Parameter: @LPAV_1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
'sp1test.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>sp1test.vshost.exe</AppDomain><Exception><ExceptionType>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27</ExceptionType><Message>An exception was caught during the execution of a retrieval query: Column 'CustomerId' not found. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.</Message><StackTrace> at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()
at System.Windows.Forms.BindingSource.GetListFromEnumerable(IEnumerable enumerable)
at System.Windows.Forms.BindingSource.ResetList()
at System.Windows.Forms.BindingSource.set_DataSource(Object value)
at SP1Test.Form1.GetCustomersWithOnlyOneOrder() in E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\Form1.cs:line 50
at SP1Test.Form1.Form1_Load(Object sender, EventArgs e) in E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\Form1.cs:line 33
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message&amp; m)
at System.Windows.Forms.Control.WndProc(Message&amp; m)
at System.Windows.Forms.ScrollableControl.WndProc(Message&amp; m)
at System.Windows.Forms.ContainerControl.WndProc(Message&amp; m)
at System.Windows.Forms.Form.WmShowWindow(Message&amp; m)
at System.Windows.Forms.Form.WndProc(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at SP1Test.Program.Main() in E:\dknight\Documents\Visual Studio 2008\Projects\sp1test\sp1test\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Column 'CustomerId' not found. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---&gt; iAnywhere.Data.SQLAnywhere.SAException: Column 'CustomerId' not found
at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
--- End of inner exception stack trace ---
Every field and table in our Sybase db uses underscores in the names: cd_location, object_history, customer_id. By default, they were removed when I generated the LLBLGen project. This hasn't caused any issues until now.
I could, of course, regenerate the LLBLGen project setting the property RemoveUnderscoresFromElementName to False and eliminate this particular issue, but I would rather not (I hate underscores! 
)
The problem is caused by the fact that I described above: a field is not aliased due to the fact ASA doesn't allow that (which is IMHO rather silly) in subqueries for fields which aren't in a group by. Though, because it's not aliased, the fields which are targeting these fields are thus not referring to the right field (as they refer to the alias name... )
The code to make sure the fields are referring to the right fields is very complex and I'm not sure how to fix this, I've to think about it. What I won't do is altering the generic routine so aliases are stripped off, as that could have consequences for other databases.
For the time being, avoid these queries in linq and use our native api.
(edit) I see the field IS in a group by. The DQE could try to check for this and avoid this error for this particular case.
It's however not said it won't happen in other queries, as derived tables are sometimes used in linq queries to wrap a query inside the linq query in its own set, which immediately would cause this problem to happen.
About the underscores: the fields also start with a lowercase character. You won't be able to set entity fields to a lowercase character starting name.
(edit) Thinking about this some more, even if a routine is made which digs up the derived table fields and finds the fields which target them (which is already there, for making everything fixed and target the right fields etc.) and strips off the aliases in the derived table fields, this will fail with inheritance scenarios where fields are aliased like F1_A etc. to avoid duplicates when fields are overriden in subtypes.
I'll alter the DQE to allow aliases on fields in the group by, if any, but otherwise it's not doable to fix this. Sybase should fix this and remove this stupid limitation, but we both know that won't happen iAnywhere soon. It's the same as with Firebird 1.x's lack of derived table support... we can't avoid them in linq queries (otherwise it will require rewriting the query which is too complex)..
(edit) hmm... I found this example:
SELECT lname, fname, number_of_orders
FROM customer JOIN
( SELECT cust_id, COUNT(*)
FROM sales_order
GROUP BY cust_id )
AS sales_order_counts( cust_id,
number_of_orders )
ON ( customer.id = sales_order_counts.cust_id )
WHERE number_of_orders > 3
Which uses a wicked construct to produce a projection for a derived table inside the query. I'll do some experiments...
(edit) hmm... that doesn't work. Any subquery which doesn't have a group by fails. I can't find details on this though... The iAnywhere manual isn't very helpful, it doesn't state you have to use a grouped query as a derived table.
So linq on ASA will be a bit limited due to this... So what I'll do is simply always emit the aliases: if the query fails, the limit is forced by ASA: use a group by. If that's out of your hands (due to linq), fall back onto our native api.
Joined: 01-Sep-2008
Otis wrote:
Could you please try the attached DQE ?
I get the same error.
I'll use the native api instead.
Thank you for taking your time to look at my issue. I appreciate it very much.
Joined: 01-Sep-2008