MariaDB, duplicaat of niet

[MSteverink]

Software:
MariaDB 10.3.22 op Debian testing
storage engine: InnoDB
Collation: utf8mb4
kolomtype: varchar(512)

Ik heb een programmaatje draaien dat dagelijks de bestandsnamen uit een aantal directories, recursief, inleest en wegschrijft naar een SQL-database. Voor het wegschrijven wordt de tabel leeggegooid.
Je zou verwachten dat bestandsnamen (volledig, met padnaam) uniek zijn, maar ik wilde toch een UNIQUE-constraint of een Primary key op de tabel zetten. En dat lukt niet, omdat bepaalde entries twee keer voorkomen.

code:

SELECT filenames
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';

levert

code:

/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3
/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand By Me.mp3

De entries op zich zjjn correct, ik bedoel, het gaat echt om twee verschillende bestanden. Dat kan, onder Linux/EXT4.

Maar doe ik dit

code:

SELECT DISTINCT filenames/*, COUNT(*)*/
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';

dan krijg ik nog een record terug, namelijk

code:

/mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3

En, als variant op het vorige voorbeeld:

code:

SELECT COUNT(*), filenames
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%'
 GROUP BY filenames;

geeft

code:

2   /mnt/d353bdd4-32af-497a-90de-8971cfc40429/Public/Shared Music/mmedia/F/Flying Pickets/Stand by me.mp3

Dus, twee verschillende records worden in een GROUP BY of DISTINCT toch als identiek gezien.

Wat kan hier aan de hand zijn? En vooral, hoe los ik het zo op dat ik toch een constraint of een primary key kan zetten?

Zoeken op Google (innodb unique constraint) leverde o.a. https://dba.stackexchange...onstraint-on-large-column op, maar daar wordt gesproken over een limiet, op de index, van 767 bytes. Zo lang is mijn kolom niet.

[RobIII]

Je wil een case-sensitive collation op dat veld. Je hebt nu waarschijnlijk een "ci" (Case insensitive) collation en je wil dus een "cs" (Case Sensitive) collation.

[ Voor 75% gewijzigd door RobIII op 31-05-2020 16:40 ]

[0xDEADBEEF]

MSteverink schreef op zondag 31 mei 2020 @ 16:09:

code:

SELECT DISTINCT filenames/*, COUNT(*)*/
 FROM tbl_filenames
 WHERE UPPER(filenames) LIKE '%STAND BY ME%';

Vermoedelijk voert de engine/query optimizer eerst UPPER() uit, en DISTINCT op een upper case filename. Dan klopt het dat er éen regel wordt geretourneerd.

[RobIII]

0xDEADBEEF schreef op zondag 31 mei 2020 @ 23:12:
[...]

Vermoedelijk voert de engine/query optimizer eerst UPPER() uit, en DISTINCT op een upper case filename. Dan klopt het dat er éen regel wordt geretourneerd.

Nee, omdat er een case-insensitive collation wordt gebruikt maakt 't voor een DISTINCT niets uit dat er verschil in hoofdletters zit; "a" is immers gelijk aan "A" (en dus niet verschillend, niet distinct). Sowieso maakt de UPPER() je sargability nul (tenzij de query optimizer slim genoeg is om te zien dat 't totaal niets toevoegt - iets wat bij MySQL/MariaDB altijd maar zeer de vraag is). Heel die "Upper()" heeft in beide queries geen toegevoegde waarde whatsoever.

Wijzig je de collation naar een case-sensitive variant dan zal 't voor DISTINCT (en GROUP BY) etc. wél uitmaken wat de case is en dan is "a" niet meer gelijk aan "A" en dus (ook) UNIQUE.

Kijk hier maar.

MSteverink schreef op zondag 31 mei 2020 @ 16:09:
Collation: utf8mb4

Overigens: de collation utf8mb4 bestaat niet; wel utf8mb4_bin, utf8mb4_unicode_ci of utf8mb4_general_ci bijvoorbeeld.

En als je dan nog niet overtuigd bent dan probeer dit maar eens:

SQL:

CREATE TABLE t(
  f CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  UNIQUE KEY UQ (f)
);

insert into `t` VALUES ('a'), ('A');

Dat geeft een "Duplicate entry 'A' for key 'UQ'". Wijzig je de collation van utf8mb4_unicode_ci naar utf8mb4_bin dan werkt 't precies zoals je wil.

Overigens betekent 't wél weer dat je LIKE of = operator etc. nu ook "opeens" case-sensitive zijn; daar moet je dan wel rekening mee houden uiteraard als je case-insenstive wil zoeken (en dan zul je dus wél met UPPER()/LOWER() en consorten aan de gang moeten).

[ Voor 68% gewijzigd door RobIII op 01-06-2020 01:03 ]

[MueR]

RobIII schreef op maandag 1 juni 2020 @ 00:22:
...

This. Truth.

[MSteverink]

RobIII schreef op maandag 1 juni 2020 @ 00:22:
[...]
Heel die "Upper()" heeft in beide queries geen toegevoegde waarde whatsoever.

Klopt. aanvankelijk stond die er ook niet. Die heb ik toegevoegd, en de like-string in hoofdletters, om duidelijk te maken dat het niet aan de LIKE ligt.

RobIII schreef op maandag 1 juni 2020 @ 00:22:
[...]


(tenzij de query optimizer slim genoeg is om te zien dat 't totaal niets toevoegt - iets wat bij MySQL/MariaDB altijd maar zeer de vraag is).

En dit, ook.

RobIII schreef op maandag 1 juni 2020 @ 00:22:
Overigens: de collation utf8mb4 bestaat niet; wel utf8mb4_bin, utf8mb4_unicode_ci of utf8mb4_general_ci bijvoorbeeld.

Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.

[MueR]

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.

Controleer dan ook even of de betreffende velden ook die collation hebben. MySQL/MariaDB doen daar wel eens wat funky over.

[RobIII]

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.

Je hebt een database collation, een table collation en een column collation. Ik adviseer je ze, voor zover mogelijk, alle 3 gelijk te trekken, maar het gaat hier natuurlijk om de column collation van het "filenames" veld (wat overigens enkelvoud zou moeten zijn).

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Jij zit bij Charset te kijken, niet bij Collation. Zorg dat je 't verschil leert

[ Voor 66% gewijzigd door RobIII op 01-06-2020 14:24 ]

[Kalentum]

MSteverink schreef op maandag 1 juni 2020 @ 11:07:
Dan heb ik nog een extra uitdaging erbij. Mijn tooling (MySQL workbench) kent deze namelijk wel. Maar heeft ook moeite om een gekozen collation vast te houden. Vermoedelijk hierdoor, dus.

Ik heb je eerste antwoord als beste antwoord gemarkeerd. Ik ben er nog niet uit, nog lang niet, maar weet nu in elk geval in welke richting ik moet zoeken.

Er zijn twee verschillende dingen:

- Character sets: die bepalen op welke manier tekst wordt opgeslagen. utf8mb4 is een character set
- Collations: die bepalen hoe het vergelijken van tekens in een characterset gaat

Dus gegeven deze strings: '🎉A' en '🎉a':
- met een collation die case insensitive is zullen deze twee door Maria DB als identiek worden gezien
- met een collation die case sensitive is zullen deze twee als verschillend worden gezien.