Data to Fields not working for some calculated MySql query fields

[wbaker]

In Writer/Data Sources window, I run a simple MySQL query with a single computation (sum):
SELECT c.*, (c.client_income1+c.client_income2) AS total_income
FROM client_incomes c

client_incomes table is just:
Cl_ID client client_income1 client_income2

1 Red 1000 1500

2 Brown 2000 3000

Output, in Writer Data Sources window shows, correctly:

1 Red 1000 1500 2500

2 Brown 2000 3000 5000

Create a simple document by inserting all five fields:
<Cl_ID> <client_income1> <client_income2> <Total_income>

Select top record and “Data to Fields” result in Writer document:
1 Red 1000 1500 <Total_income>

Select top record and “Data to Fields” result in Writer document:
2 Brown 2000 3000 <Total_income>

<Total_income> field does not update or change.

I run the query in “Run SQL Command Directly” mode because it is actually part of a far more complex query that must be run directly.

If I define a stored function in MySql, say to add two numbers, and return that result as part of the query, the <Total_income> field updates properly. However, my calculations are numerous and I really don’t want to redefine and store simple math functions that already are available. Besides, some calculations are not simple in the original query.

My question(s): Is there a simple way to correct this behavior without having to store a bunch of math functions? It seems that “Data to Fields” is somehow not using or unable to use the calculated field. Is this a bug?

[EasyTrieve]

1. Are you sure you’re getting the data from the query, and not from the table? 2) Also, out of curiosity, what are the types for fields: Client_income1 and 2? Perhaps the result of the sum is of a type that is not correctly getting put into your table. Try using other numeric field types, like float, integer, decimal, etc. (Not saying what the solution is, but what you might try). (Note: native SQL connector has been removed from newer LO versions. Now MySQL/JDBC is way to go. Not JDBC/mysql.)

[wbaker]

@EasyTrieve Thank you for this suggestion. It gives me a better direction. I will try them and report the results.

[wbaker]

@EasyTrieve Right You Are! Replaced MySQL Native connector with MySQL JDBC (Connector J) and all is as it should be!

[EasyTrieve]

@wbaker,

[Ratslinger]

Using LO v5.3.0.3 currently (and others in the past), I have not been able to duplicate your situation - MySQL or otherwise.

Using some complex queries (including unions, multiple joins, calculations and more) the displayed information in the document always matches the information in the Data Source window.

Verify the <total_income> field in the document is in fact the one in the data source by hovering the mouse over the field. This will display the information source.

[wbaker]

I’m using Ubuntu 16.04LTS, LO 5.1.4.2 with native MySQL connector. If I use MySQL functions that allow “Design View”, the behaviour is correct. However, if I use slightly more sophisticated functions, where “Design View” is prevented because I must use “Run SQL Command Directly”, the field never changes again.
At any rate, I have confirmed that if I create a function to add two numbers and use it instead a simple"+", the “Data to Fields” operates correctly again.

[wbaker]

Also, I’ve “hovered” over the <total_income> field and it is correctly used. I expected this as I inserted the field from the “other fields” dialogue.
Your answer seems to indicate this is some quirk in my system, and maybe it is. I use three different computers (same software states, more or less) and all are behaving this way. Perhaps it is the connector. I will try a different connector next.

[Ratslinger]

@wbaker My calculations also include simple + without problem. Have tried many connectors just for the sake of trying but have stuck with MySQL Connector/J 5.1.36. Did a lot of this testing today on Mint 18 system with & without Run SQL Command Directly.

[wbaker]

@Ratslinger Thank you for checking and trying to help. Your information is, in fact, helpful. I will dig a little deeper and add what I can find.

[wbaker]

Thanks to EasyTrieve’s suggestion (and Ritslinger’s input), the problem is SOLVED. The odd behaviour only occurs when I was using the MySQL Native database connector. Replaced with MySQL JDBC (Connector J) and the “Data to Fields” operates perfectly as expected.