This is an automated email from the ASF dual-hosted git repository.
mattcasters pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 6d1eea8c17 sqlite type hardening ,fixes #3633 (#8548)
6d1eea8c17 is described below
commit 6d1eea8c172e95b36ed8061c829aabd3b0a3f2fa
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Thu Sep 24 12:54:54 2026 +0200
sqlite type hardening ,fixes #3633 (#8548)
* sqlite type hardening ,fixes #3633
* revert changes and switch to rules
---
.../sqlite/0008-database-join-numeric-ids.hpl | 427 +++++++++++++++++++++
integration-tests/sqlite/README.md | 9 +
.../sqlite/main-0006-database-join-numeric-ids.hwf | 115 ++++++
.../sqlite/scripts/create-product-types.sql | 45 +++
.../hop/databases/sqlite/SqliteDatabaseMeta.java | 12 +
.../hop/databases/sqlite/SqliteNumericValues.java | 118 ++++++
.../databases/sqlite/SqliteNumericValuesTest.java | 257 +++++++++++++
7 files changed, 983 insertions(+)
diff --git a/integration-tests/sqlite/0008-database-join-numeric-ids.hpl
b/integration-tests/sqlite/0008-database-join-numeric-ids.hpl
new file mode 100644
index 0000000000..41798d6547
--- /dev/null
+++ b/integration-tests/sqlite/0008-database-join-numeric-ids.hpl
@@ -0,0 +1,427 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0008-database-join-numeric-ids</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Walks a hierarchy of ids held in NUMERIC columns with a
Database Join and a recursive query, and compares every id it read with the
text it should render as. Issue #3633: the join's fields said Number while its
rows carried the Long the driver typed from the data, and rendering one
failed.</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/09/23 10:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/23 10:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>lookup keys</from>
+ <to>join descendants</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>join descendants</from>
+ <to>ids to text</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>ids to text</from>
+ <to>null marker</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>null marker</from>
+ <to>ids match?</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>ids match?</from>
+ <to>count rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>ids match?</from>
+ <to>Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count rows</from>
+ <to>all rows joined?</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>all rows joined?</from>
+ <to>success</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>all rows joined?</from>
+ <to>Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>lookup keys</name>
+ <type>DataGrid</type>
+ <description>Two lookups. The first starts at the root, whose parent is
NULL, so the driver types parentId as NUMERIC and id as INTEGER. The second
starts one level down, where it types both as INTEGER.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <data>
+ <line>
+ <item>ABCD</item>
+ <item>100002348</item>
+ </line>
+ <line>
+ <item>ABCD</item>
+ <item>100002344</item>
+ </line>
+ </data>
+ <fields>
+ <field>
+ <name>source</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ </field>
+ <field>
+ <name>start_id</name>
+ <type>Integer</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>96</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>join descendants</name>
+ <type>DBJoin</type>
+ <description>The query from the issue: the start node and everything below
it.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>sqlite</connection>
+ <rowlimit>0</rowlimit>
+ <sql>WITH RECURSIVE descendants AS (
+ SELECT id, parentId, x_id, x_parent_id
+ FROM producttype
+ WHERE export_source = ?
+ AND id = ?
+ UNION ALL
+ SELECT t.id, t.parentId, t.x_id, t.x_parent_id
+ FROM producttype t
+ JOIN descendants d ON t.parentId = d.id
+)
+SELECT *
+FROM descendants</sql>
+ <outer_join>N</outer_join>
+ <replace_vars>N</replace_vars>
+ <parameter>
+ <field>
+ <name>source</name>
+ <type>String</type>
+ </field>
+ <field>
+ <name>start_id</name>
+ <type>Integer</type>
+ </field>
+ </parameter>
+ <attributes/>
+ <GUI>
+ <xloc>272</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>ids to text</name>
+ <type>SelectValues</type>
+ <description>Renders the ids the join read to text, which is where a value
that does not match its field's type fails. The conversions name their decimal
symbol, so the comparison does not depend on the platform locale.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <select_unspecified>N</select_unspecified>
+ <meta>
+ <name>id</name>
+ <rename>id</rename>
+ <type>String</type>
+ <length>-2</length>
+ <precision>-2</precision>
+ <conversion_mask>0</conversion_mask>
+ <date_format_lenient>false</date_format_lenient>
+ <date_format_locale/>
+ <date_format_timezone/>
+ <lenient_string_to_number>false</lenient_string_to_number>
+ <encoding/>
+ <decimal_symbol>.</decimal_symbol>
+ <grouping_symbol/>
+ <currency_symbol/>
+ <storage_type/>
+ </meta>
+ <meta>
+ <name>parentId</name>
+ <rename>parentId</rename>
+ <type>String</type>
+ <length>-2</length>
+ <precision>-2</precision>
+ <conversion_mask>0</conversion_mask>
+ <date_format_lenient>false</date_format_lenient>
+ <date_format_locale/>
+ <date_format_timezone/>
+ <lenient_string_to_number>false</lenient_string_to_number>
+ <encoding/>
+ <decimal_symbol>.</decimal_symbol>
+ <grouping_symbol/>
+ <currency_symbol/>
+ <storage_type/>
+ </meta>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>448</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>null marker</name>
+ <type>IfNull</type>
+ <description>The expected columns spell a NULL out as <null>, so the
root's parent takes part in the comparison.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <replaceAllByValue/>
+ <replaceAllMask/>
+ <selectFields>Y</selectFields>
+ <selectValuesType>N</selectValuesType>
+ <setEmptyStringAll>N</setEmptyStringAll>
+ <valuetypes>
+ </valuetypes>
+ <fields>
+ <field>
+ <name>id</name>
+ <value><null></value>
+ <mask/>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <name>parentId</name>
+ <value><null></value>
+ <mask/>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>624</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>ids match?</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>count rows</send_true_to>
+ <send_false_to>Abort</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <conditions>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>id</leftvalue>
+ <function>=</function>
+ <rightvalue>x_id</rightvalue>
+ </condition>
+ <condition>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <leftvalue>parentId</leftvalue>
+ <function>=</function>
+ <rightvalue>x_parent_id</rightvalue>
+ </condition>
+ </conditions>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>800</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <add_linenr>N</add_linenr>
+ <all_rows>N</all_rows>
+ <directory>${java.io.tmpdir}</directory>
+ <fields>
+ <field>
+ <aggregate>nr_rows</aggregate>
+ <subject>id</subject>
+ <type>COUNT_ANY</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <give_back_row>N</give_back_row>
+ <group>
+</group>
+ <ignore_aggregate>N</ignore_aggregate>
+ <linenr_fieldname/>
+ <prefix>grp</prefix>
+ <attributes/>
+ <GUI>
+ <xloc>976</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>all rows joined?</name>
+ <type>FilterRows</type>
+ <description>Three rows below and including the root, two below and
including its child.</description>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>success</send_true_to>
+ <send_false_to>Abort</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>nr_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>5</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>1152</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>success</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>1328</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <message>A Database Join did not read back the ids SQLite holds.</message>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>976</xloc>
+ <yloc>192</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/sqlite/README.md
b/integration-tests/sqlite/README.md
index bb0c70bbd9..f3b164f8e1 100644
--- a/integration-tests/sqlite/README.md
+++ b/integration-tests/sqlite/README.md
@@ -46,6 +46,7 @@ are listed in <https://www.sqlite.org/lang_datefunc.html> and
include
| `main-0003-write-read-dates.hwf` | Dates Hop writes into SQLite, read back
both through Hop and through SQLite's own `DATE()`/`DATETIME()` |
| `main-0004-read-data-types.hwf` | One column of every declared type a SQLite
table is likely to carry, plus two expression columns |
| `main-0005-write-read-data-types.hwf` | One value of every Hop type, written
into SQLite and read back |
+| `main-0006-database-join-numeric-ids.hwf` | Ids in `NUMERIC` columns walked
with a Database Join and a recursive query ([issue
#3633](https://github.com/apache/hop/issues/3633)) |
Every fixture row carries the expected rendering of its own columns as plain
text (`x_date`, `x_integer`, …), so the pipelines compare what Hop read against
@@ -59,6 +60,14 @@ that row comes **first**, deliberately — the SQLite JDBC
driver types an
expression column from the first row it sees, and a leading `NULL` is what
makes
`STRFTIME()` and friends report `NUMERIC` instead of `TEXT`.
+The same driver types a *table* column from its data too, once a query has
+run: a column declared `NUMERIC` reads as `INTEGER` on a row holding an
+integer and as `NUMERIC` on a row holding `NULL`, where the prepared statement
+said `NUMERIC` for both. `main-0006` joins a hierarchy whose root has a `NULL`
+parent, which is how a Database Join ended up with a `Long` in a `Number`
field.
+The dialect now reads a table column declared `NUMERIC`, `DECIMAL` or `NUMBER`
+as an exact BigNumber on both paths, whatever the row holds.
+
The two expression columns in `main-0004` are the other side of that: the
driver
did see a value in their first row, so they have to keep the type it gave them.
Reading every expression as a string would be as wrong as reading a date as a
diff --git a/integration-tests/sqlite/main-0006-database-join-numeric-ids.hwf
b/integration-tests/sqlite/main-0006-database-join-numeric-ids.hwf
new file mode 100644
index 0000000000..a999253694
--- /dev/null
+++ b/integration-tests/sqlite/main-0006-database-join-numeric-ids.hwf
@@ -0,0 +1,115 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<workflow>
+ <name>main-0006-database-join-numeric-ids</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Walks a hierarchy of ids held in NUMERIC columns with a
Database Join, issue #3633.</description>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/09/23 10:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/09/23 10:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>50</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>create producttype</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql/>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>T</sqlfromfile>
+
<sqlfilename>${PROJECT_HOME}/scripts/create-product-types.sql</sqlfilename>
+ <sendOneStatement>F</sendOneStatement>
+ <connection>sqlite</connection>
+ <parallel>N</parallel>
+ <xloc>208</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0008-database-join-numeric-ids.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <filename>${PROJECT_HOME}/0008-database-join-numeric-ids.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>400</xloc>
+ <yloc>50</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>create producttype</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>create producttype</from>
+ <to>0008-database-join-numeric-ids.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git a/integration-tests/sqlite/scripts/create-product-types.sql
b/integration-tests/sqlite/scripts/create-product-types.sql
new file mode 100644
index 0000000000..bfd4327375
--- /dev/null
+++ b/integration-tests/sqlite/scripts/create-product-types.sql
@@ -0,0 +1,45 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+ http://www.apache.org/licenses/LICENSE-2.0
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+*/
+
+/*
+The hierarchy from issue #3633: ids in columns declared NUMERIC, holding
integers, with the root's
+parent NULL. The SQLite JDBC driver types such a column from the row it is on
once a query runs:
+INTEGER for an integer, NUMERIC for a NULL. Before the query runs it says
NUMERIC for both. A
+Database Join lookup starting at the root therefore sees id as INTEGER and
parentId as NUMERIC,
+and one starting further down sees both as INTEGER.
+
+Each row carries the text its ids should render as once Hop has read them,
which is what
+0008-database-join-numeric-ids.hpl compares against.
+*/
+
+DROP TABLE IF EXISTS producttype;
+
+CREATE TABLE producttype
+(
+ export_source TEXT
+, id NUMERIC
+, parentId NUMERIC
+, x_id TEXT
+, x_parent_id TEXT
+);
+
+INSERT INTO producttype VALUES
+ ('ABCD', 100002348, NULL, '100002348', '<null>')
+, ('ABCD', 100002344, 100002348, '100002344', '100002348')
+, ('ABCD', 100002340, 100002344, '100002340', '100002344')
+, ('WXYZ', 100002348, NULL, '100002348', '<null>')
+;
diff --git
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
index d2ece935a0..bd6b31c7cb 100644
---
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
+++
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteDatabaseMeta.java
@@ -99,6 +99,18 @@ public class SqliteDatabaseMeta extends BaseDatabaseMeta
implements IDatabase {
private static final List<IDatabaseTypeRule> TYPE_RULES =
DatabaseTypes.rules()
+ // A table column declared NUMERIC, DECIMAL or NUMBER is an exact
number, whatever the
+ // driver makes of the row it is on: INTEGER for a whole number,
NUMERIC for a null,
+ // VARCHAR for text. The prepared statement says NUMERIC for all of
them, and a Database
+ // Join takes its fields from one and its values from the other.
Matched on the declared
+ // name, which is the same on both, rather than on the JDBC type.
See issue #3633.
+ .readNativeMatching(SqliteNumericValues.DECLARED_TYPE)
+ .where(column -> !Utils.isEmpty(column.getTableName()))
+ .bind(SqliteNumericValues.BINDING)
+ .as(
+ IValueMeta.TYPE_BIGNUMBER,
+ column -> column.getPrecision() > 0 ? column.getPrecision() : -1,
+ column -> column.getPrecision() > 0 ? column.getScale() : -1)
// Dynamic typing means a binary column is as likely to hold text.
.read(Types.BINARY, Types.BLOB, Types.VARBINARY, Types.LONGVARBINARY)
.where(
diff --git
a/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteNumericValues.java
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteNumericValues.java
new file mode 100644
index 0000000000..e764b12ebf
--- /dev/null
+++
b/plugins/databases/sqlite/src/main/java/org/apache/hop/databases/sqlite/SqliteNumericValues.java
@@ -0,0 +1,118 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.sqlite;
+
+import java.math.BigDecimal;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import org.apache.hop.core.database.IDatabase;
+import org.apache.hop.core.database.types.IValueBinding;
+import org.apache.hop.core.row.IValueMeta;
+
+/**
+ * Reading a column declared NUMERIC, DECIMAL or NUMBER on SQLite, exactly.
+ *
+ * <p>Such a column has numeric affinity: SQLite stores each value as an
integer when it is a whole
+ * number, as a real otherwise, and keeps text it cannot convert as text (<a
+ * href="https://www.sqlite.org/datatype3.html";>datatype3</a>). The JDBC
driver types the column
+ * from the value on the current row once a query runs, so the same column is
INTEGER on one row and
+ * NUMERIC on the next, while the prepared statement said NUMERIC for all of
them. Hop describes a
+ * query both ways and needs the two to agree (issue #3633).
+ *
+ * <p>So the column is a BigNumber whatever the row holds, read through {@link
ResultSet#getObject}:
+ * an integer stays exact to the last digit, where a double loses anything
past 2^53, and a real
+ * keeps the digits it prints with. Text that is not a number cannot be a
BigNumber; rather than
+ * turn it into zero, the way getDouble does, the read fails and says which
value it was.
+ */
+final class SqliteNumericValues {
+
+ /** Declared type names, with or without a size, read as exact numbers. */
+ static final String DECLARED_TYPE =
"\\s*(NUMERIC|DECIMAL|NUMBER)\\s*(\\(.*\\))?\\s*";
+
+ /** Reads exactly; writing a BigNumber is the default handling. */
+ static final IValueBinding BINDING =
+ new IValueBinding() {
+ @Override
+ public Object read(IDatabase database, IValueMeta valueMeta, ResultSet
resultSet, int index)
+ throws SQLException {
+ return toBigDecimal(resultSet.getObject(index), valueMeta.getName());
+ }
+
+ @Override
+ public void write(
+ IDatabase database,
+ IValueMeta valueMeta,
+ PreparedStatement preparedStatement,
+ int index,
+ Object value) {
+ throw new UnsupportedOperationException("This binding only reads
values");
+ }
+ };
+
+ private SqliteNumericValues() {
+ // Utility class.
+ }
+
+ static BigDecimal toBigDecimal(Object value, String columnName) throws
SQLException {
+ if (value == null) {
+ return null;
+ }
+ if (value instanceof BigDecimal bigDecimal) {
+ return bigDecimal;
+ }
+ if (value instanceof Long || value instanceof Integer || value instanceof
Short) {
+ return BigDecimal.valueOf(((Number) value).longValue());
+ }
+ try {
+ if (value instanceof Double || value instanceof Float) {
+ // valueOf, not new BigDecimal(double): 2.5 stays 2.5 and 0.1 stays
0.1.
+ return withoutNegativeScale(BigDecimal.valueOf(((Number)
value).doubleValue()));
+ }
+ if (value instanceof String text) {
+ return withoutNegativeScale(new BigDecimal(text.trim()));
+ }
+ } catch (NumberFormatException e) {
+ // Not a number: reported below.
+ }
+ throw new SQLException(
+ "Column '"
+ + columnName
+ + "' is declared as a number but holds "
+ + describe(value)
+ + ". Read it as text with CAST("
+ + columnName
+ + " AS TEXT) to keep such values.");
+ }
+
+ /**
+ * 55487400.0 comes out of valueOf as 5.54874E+7, a negative scale that
toString() and every
+ * driver inlining the value then print in scientific notation. Rescaling to
zero is exact. The
+ * same as ValueMetaBase.convertDoubleToBigNumber.
+ */
+ private static BigDecimal withoutNegativeScale(BigDecimal number) {
+ return number.scale() < 0 ? number.setScale(0) : number;
+ }
+
+ private static String describe(Object value) {
+ if (value instanceof byte[] bytes) {
+ return "a blob of " + bytes.length + " bytes";
+ }
+ return "the value '" + value + "'";
+ }
+}
diff --git
a/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteNumericValuesTest.java
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteNumericValuesTest.java
new file mode 100644
index 0000000000..ecbf14d94b
--- /dev/null
+++
b/plugins/databases/sqlite/src/test/java/org/apache/hop/databases/sqlite/SqliteNumericValuesTest.java
@@ -0,0 +1,257 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.databases.sqlite;
+
+import static org.apache.hop.junit.database.TypeRuleFixture.meta;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.types.DatabaseColumn;
+import org.apache.hop.core.database.types.DatabaseTypeMapper;
+import org.apache.hop.core.exception.HopDatabaseException;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.variables.Variables;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * A table column declared NUMERIC, DECIMAL or NUMBER reads as the same exact
number before and
+ * after its query runs, whatever the rows hold. Against the real driver,
because the driver is what
+ * types a column from its data. Issue #3633.
+ */
+class SqliteNumericValuesTest {
+
+ private static final String[] DECLARED_TYPES = {
+ "NUMERIC", "NUMERIC(20)", "NUMERIC (20)", "DECIMAL(10,2)", "decimal(10,
2)", "NUMBER"
+ };
+
+ /** One of each storage class, as the first row the driver sees. */
+ private static final String[] FIRST_VALUES = {
+ "NULL", "42", "4200000000", "2.5", "'abc'", "x'00'"
+ };
+
+ private Connection connection;
+ private DatabaseMeta databaseMeta;
+
+ @BeforeAll
+ static void setUpClass() throws Exception {
+ HopClientEnvironment.init();
+ Class.forName("org.sqlite.JDBC");
+ }
+
+ @BeforeEach
+ void setUp() throws Exception {
+ connection = DriverManager.getConnection("jdbc:sqlite::memory:");
+ databaseMeta = meta(new SqliteDatabaseMeta());
+ }
+
+ @AfterEach
+ void tearDown() throws Exception {
+ connection.close();
+ }
+
+ @Test
+ void aNumericColumnIsTheSameBigNumberBeforeAndAfterItsQueryRuns() throws
Exception {
+ List<String> wrong = new ArrayList<>();
+ for (String declared : DECLARED_TYPES) {
+ createTable(declared);
+ IValueMeta prepared;
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t")) {
+ prepared = valueMeta(ps.getMetaData(), 1);
+ }
+ if (prepared.getType() != IValueMeta.TYPE_BIGNUMBER) {
+ wrong.add(declared + " prepared: " + prepared.getTypeDesc());
+ }
+ for (String value : FIRST_VALUES) {
+ insertOnly(value);
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t");
+ ResultSet rs = ps.executeQuery()) {
+ IValueMeta executed = valueMeta(rs.getMetaData(), 1);
+ if (executed.getType() != IValueMeta.TYPE_BIGNUMBER) {
+ wrong.add(declared + " holding " + value + ": " +
executed.getTypeDesc());
+ }
+ }
+ }
+ }
+ assertTrue(wrong.isEmpty(), String.join("\n", wrong));
+ }
+
+ @Test
+ void aSizedColumnKeepsItsPrecisionAndScale() throws Exception {
+ createTable("DECIMAL(10,2)");
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t")) {
+ IValueMeta valueMeta = valueMeta(ps.getMetaData(), 1);
+ assertEquals(10, valueMeta.getLength());
+ assertEquals(2, valueMeta.getPrecision());
+ }
+ }
+
+ @Test
+ void valuesAreReadExactly() throws Exception {
+ createTable("NUMERIC");
+ assertEquals(BigDecimal.valueOf(Long.MAX_VALUE),
readOnly(Long.toString(Long.MAX_VALUE)));
+ // A double rounds this to 2^53 + 4.
+ long pastTheMantissa = (1L << 53) + 3;
+ assertEquals(BigDecimal.valueOf(pastTheMantissa),
readOnly(Long.toString(pastTheMantissa)));
+ assertEquals(BigDecimal.valueOf(-7), readOnly("-7"));
+ assertEquals(new BigDecimal("2.5"), readOnly("2.5"));
+ assertEquals(new BigDecimal("0.1"), readOnly("0.1"));
+ // Too large for an integer, so SQLite keeps it a real. It must not come
back with a negative
+ // scale, which prints as 1E+20.
+ BigDecimal large = (BigDecimal) readOnly("1e20");
+ assertEquals("100000000000000000000", large.toString());
+ assertNull(readOnly("NULL"));
+ }
+
+ @Test
+ void textThatIsNotANumberIsAnErrorRatherThanZero() throws Exception {
+ createTable("NUMERIC");
+ HopDatabaseException error = assertThrows(HopDatabaseException.class, ()
-> readOnly("'abc'"));
+ String message = messages(error);
+ assertTrue(message.contains("'abc'"), message);
+ assertTrue(message.contains("CAST(v AS TEXT)"), message);
+ }
+
+ /** The case from the issue: ids in NUMERIC columns, walked with a recursive
query. */
+ @Test
+ void theIssueQueryReadsExactIdsOnBothPaths() throws Exception {
+ try (Statement st = connection.createStatement()) {
+ st.execute("CREATE TABLE producttype (export_source TEXT, id NUMERIC,
parentId NUMERIC)");
+ st.execute(
+ "INSERT INTO producttype VALUES ('ABCD', 100002348, NULL), ('ABCD',
100002344,"
+ + " 100002348)");
+ }
+ String sql =
+ "WITH RECURSIVE ChildNodes AS ("
+ + " SELECT id, parentId FROM producttype WHERE export_source = ?
AND id = ?"
+ + " UNION ALL"
+ + " SELECT t.id, t.parentId FROM producttype t JOIN ChildNodes c
ON t.parentId = c.id)"
+ + " SELECT * FROM ChildNodes";
+ try (PreparedStatement ps = connection.prepareStatement(sql)) {
+ for (int i = 1; i <= 2; i++) {
+ assertEquals(IValueMeta.TYPE_BIGNUMBER, valueMeta(ps.getMetaData(),
i).getType());
+ }
+ ps.setString(1, "ABCD");
+ ps.setLong(2, 100002348L);
+ try (ResultSet rs = ps.executeQuery()) {
+ ResultSetMetaData rm = rs.getMetaData();
+ IValueMeta id = valueMeta(rm, 1);
+ IValueMeta parentId = valueMeta(rm, 2);
+ assertEquals(IValueMeta.TYPE_BIGNUMBER, id.getType());
+ assertEquals(IValueMeta.TYPE_BIGNUMBER, parentId.getType());
+
+ assertTrue(rs.next());
+ assertEquals(BigDecimal.valueOf(100002348L), read(rs, id, 0));
+ assertNull(read(rs, parentId, 1));
+ assertTrue(rs.next());
+ assertEquals(BigDecimal.valueOf(100002344L), read(rs, id, 0));
+ assertEquals(BigDecimal.valueOf(100002348L), read(rs, parentId, 1));
+ }
+ }
+ }
+
+ /** An expression has no declared type, only its data, so it keeps what the
driver says. */
+ @Test
+ void anExpressionIsLeftAsTheDriverTypedIt() throws Exception {
+ createTable("NUMERIC");
+ insertOnly("42");
+ try (PreparedStatement ps =
+ connection.prepareStatement(
+ "SELECT v + 1 AS plus_one, CAST(v AS NUMERIC) AS c FROM t");
+ ResultSet rs = ps.executeQuery()) {
+ ResultSetMetaData rm = rs.getMetaData();
+ for (int i = 1; i <= rm.getColumnCount(); i++) {
+ assertEquals(IValueMeta.TYPE_INTEGER, valueMeta(rm, i).getType(),
rm.getColumnName(i));
+ }
+ }
+ }
+
+ /** Only the exact numeric names: other declared types map as they always
did. */
+ @Test
+ void otherDeclaredTypesAreUnchanged() throws Exception {
+ createTable("INTEGER");
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t")) {
+ assertEquals(IValueMeta.TYPE_INTEGER, valueMeta(ps.getMetaData(),
1).getType());
+ }
+ createTable("REAL");
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t")) {
+ assertEquals(IValueMeta.TYPE_NUMBER, valueMeta(ps.getMetaData(),
1).getType());
+ }
+ createTable("TEXT");
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM
t")) {
+ assertEquals(IValueMeta.TYPE_STRING, valueMeta(ps.getMetaData(),
1).getType());
+ }
+ }
+
+ private void createTable(String declared) throws Exception {
+ try (Statement st = connection.createStatement()) {
+ st.execute("DROP TABLE IF EXISTS t");
+ st.execute("CREATE TABLE t (v " + declared + ")");
+ }
+ }
+
+ private void insertOnly(String value) throws Exception {
+ try (Statement st = connection.createStatement()) {
+ st.execute("DELETE FROM t");
+ st.execute("INSERT INTO t VALUES (" + value + ")");
+ }
+ }
+
+ /** Stores one value and reads it back the way Hop reads a row. */
+ private Object readOnly(String value) throws Exception {
+ insertOnly(value);
+ try (PreparedStatement ps = connection.prepareStatement("SELECT v FROM t");
+ ResultSet rs = ps.executeQuery()) {
+ IValueMeta valueMeta = valueMeta(rs.getMetaData(), 1);
+ assertTrue(rs.next());
+ return read(rs, valueMeta, 0);
+ }
+ }
+
+ private Object read(ResultSet rs, IValueMeta valueMeta, int index) throws
Exception {
+ return databaseMeta.getIDatabase().getValueFromResultSet(rs, valueMeta,
index);
+ }
+
+ private IValueMeta valueMeta(ResultSetMetaData rm, int index) throws
Exception {
+ return DatabaseTypeMapper.getValueMeta(
+ new Variables(), databaseMeta, DatabaseColumn.of(rm, index), false,
false);
+ }
+
+ private static String messages(Throwable error) {
+ StringBuilder text = new StringBuilder();
+ for (Throwable t = error; t != null; t = t.getCause()) {
+ text.append(t.getMessage()).append('\n');
+ }
+ return text.toString();
+ }
+}
