I’m seeing a weird behavior when using the SUM(money) function in a queryExecute() call where the result returns 0, which is incorrect. When I run the exact same query (that pull from the sql statement in a cfdump) in MS SQL Management Studio, I get the correct result. The field being summed is mssql type money.
Does anyone know how I can inspect the sql that is passed through the jdbc driver? I couldn’t figure out how to do this in MS SQL SMS, nor could I find any setting in the jdbc driver settings in Lucee. And I’m not sure how I would put together a reproduceable.
(
SELECT CAST( SUM(oi2.totalPrice) AS VARCHAR(24))
FROM SubscriptionRenewals sr2
LEFT JOIN Orders o2 ON
sr2.orderFK = o2.orderID
LEFT JOIN OrderItems oi2 ON
sr2.orderItemFK = oi2.orderItemID
WHERE sr2.subscriptionFK = sf.subscriptionFK
AND o2.status = 2
) AS totalSpent
I think if there’s a null in the data, zero is expected?
I assume this ... = sf.subscriptionFK
means this is a sub-query / partial snippet.
This doesn’t feel like a Lucee issue to me based on the SQL you posted but you’re saying the exact same SQL works via SSMS? Very weird.
No, for similar needs a quick/ dirty way to see what was passed is to look via activity monitor via SSMS (or write SQL used to a log file). Better is using Fusion Reactor.
The query listener is interesting, but yielded nothing as far as I could tell. The query is exactly the same before and after, so I’m not seeing any manipulation going on, nor would I expect to. I assume my query ends up being executed exactly as I wrote it, but maybe @Zackster would have more info on that.
There are no null values being returned from the subquery, in fact I’ve tested the subquery and can see (as I expect based on my test data) that there’s only 1 record being summed.
I’ve attempted to reproduce on Win / MSSQL Server attempting the most recent 3 jre8 drivers and 5.3.7.x / 5.3.8.x / 5.3.9.x combinations and cannot.
I’m almost certain there’s an issue with the query joins.
I believe if you pass in your subscriptionFK for your use-case, you’ll have 1 record but the count(*) as num will show zero records.
SELECT COUNT(*) as num, SUM(oi2.totalPrice) as totalSpent
FROM SubscriptionRenewals sr2
LEFT JOIN Orders o2 ON
sr2.orderFK = o2.orderID
LEFT JOIN OrderItems oi2 ON
sr2.orderItemFK = oi2.orderItemID
WHERE sr2.subscriptionFK = #your_subscription_id_here#
AND o2.status = 2
Otherwise I would still take a closer look at the full query, not just the sub-query.
Every test I’ve done ends up with consistent results (blank when null for count of zero and expected sum when count is > 0 ):
The metadata of the returned result remains unchanged in either case
If that doesn’t help narrow it down, please post a reproduction case.
Something that includes both the TEST DATA and CFML would be ideal along with the versions you see the different behavior on.
Here’s my “starter” test SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test sum of money]') AND type in (N'U'))
DROP TABLE [dbo].[test sum of money]
GO
CREATE TABLE dbo.[test sum of money]
(
totalPrice money NOT NULL,
orderID int NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[test sum of money]
(
[totalPrice],
[orderID]
)
VALUES
( 1.33,
1
)
GO
I get 1 record with the total price of 10 when I run the subquery directly in management studio, and I get the same result in Lucee. It only calculates incorrectly when run in the full query. I guess I’ll have to build some test data for this.
Here’s the subquery with a little more context (still just a partial query):
SELECT
sf.*,
... more fields from other tables...
(
SELECT SUM(oi2.totalPrice)
FROM SubscriptionRenewals sr2
LEFT JOIN Orders o2 ON
sr2.orderFK = o2.orderID
LEFT JOIN OrderItems oi2 ON
sr2.orderItemFK = oi2.orderItemID
WHERE sr2.subscriptionFK = sf.subscriptionFK
AND o2.status = 2
) AS totalSpent
FROM Students sf
WHERE sf.subscriptionFK IS NOT NULL
The subquery is matching sr2.subscriptionFK with sf.subscriptionFK, which cannot be null according to the WHERE clause, and oi2.totalPrice cannot be null in any case because that field is not nullable. I’ve also checked to make sure that there are no OrderItems.totalPrice values that are null.
After spending a few hours putting together some test data and being unable to replicate the issue, I finally realized that the calculated sum that I named totalSpent is also a field in the sf table, which is no longer being used.
Many thanks for helping me focus on this and I really appreciated your time! I wonder if I can delete this thread.
What’s interesting to me, and what I learned from this is that mssql didn’t complain about a query with identical column names (I didn’t know you could do this), nor did CFML. I didn’t visually see it in mssql management studio bc the columns were far enough apart, and CFML just overwrote one of them.
Yeah, that’s how it works even though it doesn’t make a lot of sense. A CFML query object can have more than one column with the same name, but if you run a QoQ against it, I think the column it selects from will just be the first one it finds with that name, and if you select out the same column twice, it will ignore subsequent definitions of the column.
What’s ironic about this is I can’t run any quick tests about this on tryCF, because queryNew() won’t allow me to create a query object with the same name twice, even though CFQuery will if I’m selecting via JDBC
