MySQL Select and numbers

Questions
1

I have a MySQL table. Test data consists of an "Amount’ field, and three rows with values, 1000, My JSON input is:

[
  {
    "row_number": 2,
    "Amount": -1000
  },
  {
    "row_number": 3,
    "Amount": -52.23
  },
  {
    "row_number": 4,
    "Amount": -71.09
  }
]

I have a SQL Select using query parameters.

The output from the SELECT is

[
  {
    "reccount": 1,
    "amount": -1000
  },
  {
    "reccount": 1,
    "amount": "-52.23"
  },
  {
    "reccount": 0,
    "amount": "-71.09"
  }
]

Why is the first record in the output a numeric value, while the other records are strings?
This is the result regardless of whether the query parameter for Amount is modified with .toFixed(2), or if the SELECT statement has CONVERT($1,DECIMAL) . I tried a few other permutations. The SELECT does return the proper COUNT() values, but I need consistent output for subsequent steps.

n8n cloud, version 1.51

Mike

2

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
4

Hi @Mike_Z

Thanks for posting here!

Looks like we do not capture the DECIMAL types in MySQL. Let me raise a ticket and get this fixed :muscle:

6

Glad it wasn’t something I was doing wrong! Thanks

7

Hey @Mike_Z

We got the fix in the pipeline and will probably release next week!
You can then use a toggle option for this :slight_smile:

image
image911×818 54.9 KB

8

Hey @ria , I find it odd that we have to be explicit setting decimal as numbers rather than having that option by default. After all decimals are numbers. Is this solution just a workaround and will be (hopefully) “properly” fixed later on?

9

Hey @ihortom,

The change put in is not a workaround and is the way to fix this issue, Javascript has a limit when it comes to numbers and the package used returns a decimal as a string so that you don’t lose precision.

This is also why we return large format numbers as strings instead of numbers because of the 16 digit length.

You can read a bit more about this here: API and Configuration | Quickstart

11

New version n8n@1.55.0 got released which includes the GitHub PR 10313.