BUG with characters in extended character set

Andrew McP

Hello

I believe I found a bug i notepad++. I have a file with degree symbol ℃ and metres squared W/m² and when I load this file into Notepad++ these characters display as garbage. Worse, if I then save this file in notepad++, the “garbage” characters are maintained and I can’t then use it (it’s a sql file). Notepad++ detects the character set utf8 and so this shouldn’t happen. Notepad displays and saves the characters fine.

I show the file below. save in notepad then load notepad++ to see the issue.

USE weather segur de calafell;
charset utf8;
show warnings;

– select * from wlogday where daydate between ‘20221118’ and ‘20221118’

– DELETE FROM weather segur de calafell.wlog
– WHERE cast(mtime as date)=‘2022-11-15’;

/****************************************************************************************************
USE this ONLY - it checks year!
select * from wlogday order by dayDate

select * from wlogDay where dayDate BETWEEN ‘20221115’ and ‘20221116’
and year(dayDate) = 2022 order by daydate; – !!! keep constant all year!!! (only change it after Dec 31)

********************************************************************************************************/

insert
into
wlogDay (dayDate,
maxTemp,
maxTempTime,
maxIndoorTemp,
maxHumid,
maxIndoorHumid,
maxRainRate,
maxRainRateTime,
maxRainHour,
maxRainHourTime,
rainHoursTotal,
maxWindSpeed,
maxWindSpeedTime,
maxWindGust,
maxWindGustTime,
maxSolar,
maxSolarTime,
sunHoursTotal,
minTemp,
minTempTime,
minIndoorTemp,
minHumid,
minIndoorHumid,
minRainRate,
minRainHour,
minWindSpeed,
minWindGust,
avgTemp,
avgIndoorTemp,
avgHumid,
avgIndoorHumid,
avgRainRate,
avgWindSpeed,
avgWindGust,
rainDaily,
droughtDays,
rainDaysStreak,
hotDaysStreak,
coldDaysStreak)
select
cast(w.mtime as date) as dayDate,
max(case ISNULL(w.Temperature High(℃)) when 1 then w.Temperature(℃) else w.Temperature High(℃) end) as maxTemp ,
min(case when ranking.maxtemp_rank = 1 then w.mtime else null end) maxtemp_time,
max(case ISNULL(w.In Temperature High(℃)) when 1 then w.In Temperature(℃) else w.In Temperature High(℃) end) as maxIndoorTemp,
max(case isnull(w.Humidity High(%)) when 1 then w.Humidity(%) else w.Humidity High(%) end) as maxHumidity,
max(case isnull(w.In Humidity High(%)) when 1 then w.In Humidity(%) else w.In Humidity High(%) end) as maxIndoorHumid,
max(w.Rain Rate(mm/hr)) as maxRainRate,
case
when max(Rain Rate(mm/hr)) > 0 then min(case when ranking.maxrainrate_rank = 1 then w.mtime else null end)
end maxrainrate_time,
max(w.Hourly(mm)) as maxRainHour,
case
when max(Hourly(mm)) > 0 then min(case when ranking.maxrainhour_rank = 1 then w.mtime else null end)
end maxrainhour_time,
cast(count(distinct case when Rain Rate(mm/hr) > 0 then w.mtime end) as float) * 1 / 12 as rainHoursTotal,
max(w.Wind Speed(km/h)) as maxWindSpeed,
min(case when ranking.maxwindspeed_rank = 1 then w.mtime else null end) maxwindspeed_time,
max(w.Wind Gust(km/h)) as maxWindGust,
min(case when ranking.maxwindgust_rank = 1 then w.mtime else null end) maxwindgust_time,
max(w.Solar(W/m²)) as maxSolar,
min(case when ranking.maxsolar_rank = 1 then w.mtime else null end) maxsolar_time,
cast(count(distinct case when w.Solar(W/m²) >= 250 then w.mtime end) as float) * 1 / 12 as sunHoursTotal,
min(case ISNULL(Temperature Low(℃)) when 1 then w.Temperature(℃) else Temperature Low(℃) end ) as minTemp ,
min(case when ranking.mintemp_rank = 1 then w.mtime else null end) mintemp_time,
min(case ISNULL(w.In Temperature Low(℃)) when 1 then w.In Temperature(℃) else w.In Temperature Low(℃) end) minIndoorTemp,
min(case ISNULL(w.Humidity Low(%)) when 1 then w.Humidity(%) else w.Humidity Low(%) end) minHumid,
min(case ISNULL(w.IN Humidity Low(%)) when 1 then w.IN Humidity(%) else w.IN Humidity Low(%) end) minIndoorHumid,
min(Rain Rate(mm/hr)) as minRainRate,
min(Hourly(mm)) as minRainHour,
min(Wind Speed(km/h)) as minWindSpeed,
min(w.Wind Gust(km/h)) as minWindGust,
avg(Temperature(℃)) as avgTemp ,
avg(In Temperature(℃)) as avgIndoorTemp,
avg(Humidity(%)) as avgHumid,
avg(In Humidity(%)) as avgIndoorHumid,
avg(Rain Rate(mm/hr)) as avgRainRate,
avg(Wind Speed(km/h)) as avgWindSpeed,
avg(Wind Gust(km/h)) as avgWindGust,
max(Daily(mm)) as rainDaily,
0,
0,
0,
0
from
wlog w
join ( select w2.mtime,
rank() over ( partition by date(mtime) order by case ifnull(w2.Temperature High(℃),0) when 0 then w2.Temperature(℃) else w2.Temperature High(℃) end desc) maxtemp_rank,
rank() over ( partition by date(mtime) order by w2.Rain Rate(mm/hr) desc) maxrainrate_rank,
rank() over ( partition by date(mtime) order by w2.Hourly(mm) desc) maxrainhour_rank,
rank() over ( partition by date(mtime) order by Wind Speed(km/h) desc) maxwindspeed_rank,
rank() over ( partition by date(mtime) order by Wind Gust(km/h) desc) maxwindgust_rank,
rank() over ( partition by date(mtime) order by w2.Solar(W/m²) desc) maxsolar_rank,
rank() over ( partition by date(mtime) order by case ifnull(w2.Temperature Low(℃),0) when 0 then w2.Temperature(℃) else w2.Temperature Low(℃) end) mintemp_rank
from
wlog w2
where
cast(mtime as date) >= ‘2022-01-01’
) ranking on
ranking.mtime = w.mtime
where
cast(w.mtime as date) between ‘2022-11-18’ and ‘2022-11-18’
group by
cast(w.mtime as date)
;

drop temporary table if exists tempStreak;
create temporary table tempStreak
select
d.dayDate,
datediff(d.dayDate,
( select IFNULL(max(dayDate), ‘2022-01-01’)
from wlogDay
where dayDate <= d.dayDate and rainDaily>0 and dayDate >= ‘2022-01-01’)
) droughtDays /********* for current day, get Most recent day when it rained */
from
wlogDay d
where d.dayDate >= ‘2022-01-01’;

UPDATE wlogDay
INNER JOIN tempStreak ON wlogDay.dayDate = tempStreak.dayDate
SET wlogDay.droughtDays = tempStreak.droughtDays
WHERE wlogDay.dayDate >= ‘2022-01-01’;

drop temporary table if exists tempStreak;
create temporary table tempStreak
select
d.dayDate,
datediff(d.dayDate,
( select IFNULL(max(dayDate), ‘2022-01-01’) from wlogDay where dayDate < d.dayDate – !!!change to ‘2020-09-01’
and rainDaily=0 and dayDate >= ‘2022-01-01’)
) rainDays /********* for current day, get Most recent day when it rained */
from
wlogDay d
where
d.rainDaily > 0
and d.dayDate >= ‘2022-01-01’;
UPDATE wlogDay
INNER JOIN tempStreak ON wlogDay.dayDate = tempStreak.dayDate
SET wlogDay.rainDaysStreak = tempStreak.rainDays
WHERE wlogDay.rainDaily = 0;

drop temporary table if exists tempStreak;
create temporary table tempStreak
select
d.dayDate,
datediff(d.dayDate,
( select IFNULL(max(dayDate), ‘2022-01-01’) from wlogDay where dayDate < d.dayDate – !!!change to ‘2020-09-01’
and maxTemp<25 and dayDate >= ‘2022-01-01’)
) hotDays /********* for current day, get Most recent day when it rained */
from
wlogDay d
where
d.maxTemp >= 25
and d.dayDate >= ‘2022-01-01’;
UPDATE wlogDay
INNER JOIN tempStreak ON wlogDay.dayDate = tempStreak.dayDate
SET wlogDay.hotDaysStreak = tempStreak.hotDays
WHERE wlogDay.maxTemp >= 25;

drop temporary table if exists tempStreak;
create temporary table tempStreak
select
d.dayDate,
datediff(d.dayDate,
( select IFNULL(max(dayDate), ‘2022-01-01’) from wlogDay where dayDate < d.dayDate – !!!change to ‘2020-09-01’
and maxTemp>10 and dayDate >= ‘2022-01-01’)
) coldDays /********* for current day, get Most recent day when it rained */
from
wlogDay d
where
d.maxTemp < 10
and d.dayDate >= ‘2022-01-01’;
UPDATE wlogDay
INNER JOIN tempStreak ON wlogDay.dayDate = tempStreak.dayDate
SET wlogDay.coldDaysStreak = tempStreak.coldDays
WHERE wlogDay.maxTemp < 10;

commit;

Ekopalypse

@Andrew-McP

Seems to work for me.
Would you mind sharing a screenshot?

Andrew McP

@Ekopalypse Hi there! What steps did you take? I copy/pasted the file’s text from what I posted back into Notepad++ then clicked save and got the garbage that I show in the screenshot.

My notepad version below. I don’t like how it says “ANSI codepage : 1252”. Does your Help->debug say similar?

Notepad++ v8.4.6 (32-bit)
Build time : Sep 25 2022 - 19:55:26
Path : C:\Program Files (x86)\Notepad++\notepad++.exe
Command Line :
Admin mode : OFF
Local Conf mode : OFF
Cloud Config : OFF
OS Name : Windows 10 Home (64-bit)
OS Version : 21H1
OS Build : 19043.2006
Current ANSI codepage : 1252
Plugins :
combine (1)
HTMLTag_unicode (1.3.5)
mimeTools (2.8)
NppConverter (4.4)
NppExport (0.4)

Alan Kilborn

@Andrew-McP

If I paste some of your data into a virgin Notepad++ setup, I see the same “box” displayed as you do.

However if I turn DirectWrite on in the Preferences > MISC. area and restart N++, all turns out well:

BTW, I don’t actually think you had an invalid file when the “box” was showing. The character was there, correctly; Notepad++ just couldn’t show it to you.

Andrew McP

@Alan-Kilborn OMG well done!!! Fantastic discovery. All good now vis-a-vis Notepad++. I did indeed understand it was a display issue, because simple MS Notepad always shows it fine even after saving in notepad++.
OFF topic bit:-
But unfortunately my compiler (Beaver) still also shows garbage and therefore it won’t compile, so I guess it has similar issues. Beaver has the cheek to warn me I must save in UTF8 and then when I re-load it’s garbage…
Sadly I have found no fix for Beaver, so am compiling using mysql in 1990’s command-line (Dos) mode haha :) One can always trust command-line. Actually it’s easier in a way, not having to pfaff around with mouse clicks…anyway I digress.

Andrew McP

@Andrew-McP Alan, it didn’t work 100%, as it has converted all single quotes into smart quotes as shown (they look terrific but sql compilers dont appreciate beauty). Anyways, 15s and 2 global replaces later and all is good …thanks again.
Untitled.png

PeterJones

@Andrew-McP said in BUG with characters in extended character set:

as it has converted all single quotes into smart quotes as shown

No, the forum converted all the single quotes into smart quotes, because when you pasted the original data into the forum, you didn’t use the </> button on the toolbar to mark your text as “code” or “literal text”, as described in the Formatting Forum Posts FAQ. And when you copied the text back out of the forum, you copied the displayed smart-quotes instead of the original ASCII quotes.

Alan Kilborn

@PeterJones said in BUG with characters in extended character set:

And when you copied the text back out of the forum

I don’t know if this tracks.
If someone owns the original source, why would they need to copy it back?
Unless…they thought the original source was corrupted because it was displayed improperly…and, well the forum is showing it correctly, so the data there must be “better”… :-)

PeterJones

@Alan-Kilborn said in BUG with characters in extended character set:

If someone owns the original source, why would they need to copy it back?

True. I guess I should have phrased it, “I am assuming you copied the text back out of the forum, because Notepad++ will not natively convert ASCII quotes into smart quotes, and that’s the only path I could think of that would have caused that conversion without intentional action on your part.”

Andrew McP

@Alan-Kilborn and Peter it’s my bad. I didn’t see the “dont format in forum” icon or read the notes :) sorry have been really busy getting my weather project going and didn’t apply due diligence,. I wrongly assumed the data would be unformatted in the forum.

Well I dont feel a complete fool as what Alan picked out as a solution to those darn Celsius symbols was akin to finding a needle in a haystack (so glad you mentioned in was in Misc) :)

Alan Kilborn

@Andrew-McP said in BUG with characters in extended character set:

what Alan picked out as a solution to those darn Celsius symbols was akin to finding a needle in a haystack

Before you think I’m “smart” again, this sort of issue comes up all the time, so I know the right advice to give, at least as a first thing to try.

Andrew McP

@Alan-Kilborn ah!!! Cool, well let me rephrase…your short term memory is great :)