Merge queries causing duplicate records

[dedelman_clng]

I am merging two queries together and it is creating duplicate records in the output. I have verified that there are no duplicates in either of the base queries as shown below

Table "States"

Valve	timestamp	string_value	name	Index	TS-MS	Dt
XV017045	2025-08-04T00:03:26.0110015-05:00	UNDEFINED	PV_D	19400	8/4/2025 12:03:26.011	8/4/2025
XV017045	2025-08-04T00:03:27.0070037-05:00	CLOSED	PV_D	19401	8/4/2025 12:03:27.007	8/4/2025
XV017045	2025-08-04T08:03:27.0070037-05:00	CLOSED	PV_D	19403	8/4/2025 08:03:27.007	8/4/2025
XV017045	2025-08-04T11:23:27.0070037-05:00	CLOSED	PV_D	19405	8/4/2025 11:23:27.007	8/4/2025
XV017045	2025-08-04T11:33:27.0070037-05:00	CLOSED	PV_D	19406	8/4/2025 11:33:27.007	8/4/2025
XV017045	2025-08-04T12:03:27.0070037-05:00	CLOSED	PV_D	19409	8/4/2025 12:03:27.007	8/4/2025
XV017045	2025-08-04T12:10:03.5070037-05:00	OPEN	PV_D	19411	8/4/2025 12:10:03.507	8/4/2025
XV017045	2025-08-04T12:50:58.5070037-05:00	CLOSED	PV_D	19413	8/4/2025 12:50:58.507	8/4/2025
XV017045	2025-08-04T20:50:58.5070037-05:00	CLOSED	PV_D	19415	8/4/2025 08:50:58.507	8/4/2025

Table "RS Samples"

timestamp	numeric_value	string_value	warning	ValveNm	TS-MS
2025-08-04T00:03:26.0110015-05:00	null	0	null	XV017045	8/4/2025 12:03:26.011
2025-08-04T00:03:27.0070037-05:00	null	0	null	XV017045	8/4/2025 12:03:27.007
2025-08-04T08:03:27.0070037-05:00	null	0	null	XV017045	8/4/2025 08:03:27.007
2025-08-04T11:23:27.0070037-05:00	null	0	null	XV017045	8/4/2025 11:23:27.007
2025-08-04T11:33:27.0070037-05:00	null	0	null	XV017045	8/4/2025 11:33:27.007
2025-08-04T12:03:27.0070037-05:00	null	0	null	XV017045	8/4/2025 12:03:27.007
2025-08-04T12:10:03.5070037-05:00	null	1	null	XV017045	8/4/2025 12:10:03.507
2025-08-04T12:50:58.5070037-05:00	null	0	null	XV017045	8/4/2025 12:50:58.507
2025-08-04T20:50:58.5070037-05:00	null	0	null	XV017045	8/4/2025 08:50:58.507

Join (left outer) from States to RS Samples via Valve -> ValveNm and TS-MS -> TS-MS (TS-MS is a text datatype)

Result

Valve	timestamp	string_value	name	Index	TS-MS	Dt	RS Samples.string_value
XV017045	2025-08-04T00:03:26.0110015-05:00	UNDEFINED	PV_D	19400	8/4/2025 12:03:26.011	8/4/2025	0
XV017045	2025-08-04T00:03:27.0070037-05:00	CLOSED	PV_D	19401	8/4/2025 12:03:27.007	8/4/2025	0
XV017045	2025-08-04T00:03:27.0070037-05:00	CLOSED	PV_D	19401	8/4/2025 12:03:27.007	8/4/2025	0
XV017045	2025-08-04T08:03:27.0070037-05:00	CLOSED	PV_D	19403	8/4/2025 08:03:27.007	8/4/2025	0
XV017045	2025-08-04T11:23:27.0070037-05:00	CLOSED	PV_D	19405	8/4/2025 11:23:27.007	8/4/2025	0
XV017045	2025-08-04T11:33:27.0070037-05:00	CLOSED	PV_D	19406	8/4/2025 11:33:27.007	8/4/2025	0
XV017045	2025-08-04T12:03:27.0070037-05:00	CLOSED	PV_D	19409	8/4/2025 12:03:27.007	8/4/2025	0
XV017045	2025-08-04T12:03:27.0070037-05:00	CLOSED	PV_D	19409	8/4/2025 12:03:27.007	8/4/2025	0
XV017045	2025-08-04T12:10:03.5070037-05:00	OPEN	PV_D	19411	8/4/2025 12:10:03.507	8/4/2025	1
XV017045	2025-08-04T12:50:58.5070037-05:00	CLOSED	PV_D	19413	8/4/2025 12:50:58.507	8/4/2025	0
XV017045	2025-08-04T20:50:58.5070037-05:00	CLOSED	PV_D	19415	8/4/2025 08:50:58.507	8/4/2025	0

The ones in red are exact duplicates of each other.

What could be causing this, and is there a way to mitigate without using "Remove Duplicates" (the actual data set is nearly 1,000,000 rows each) ? The two queries come from different base tables, so creating an Index on RS_Samples for matching would not work.

I am on version 2.154.956.0 64-bit (May 2026)

Thanks,

David

[jgeddes · Accepted solution]

Can you verify that there are no duplicates when using the TS-MS column. From the example data you provided it looks like midnight and 12pm are both showing as 12:00:00 in your text column. This could be what is causing the duplicates...

[dedelman_clng]

@jgeddes - thank you so very much. I have been staring at this for days. Fixed the creation of the TS-MS column to use HH instead of hh in the format and the duplicates are gone.