SQLite Forum

UPDATE ... FROM ... is failing with 3.46.0
Login
Hierarchical

UPDATE ... FROM ... is failing with 3.46.0

(1) By Stéphane Aulery (saulery) on 2024-08-05 10:35:47 [link] [source]

Hello,

I recently updated the DLL of my PHP installation on Windows at work from SQLite 3.36.0 to 3.46.0 (64-bit DLL x64 taken from the download section on this website).

PHP 8.1.26 (cli) (built: Nov 22 2023 10:01:32) (ZTS Visual C++ 2019 x64) Copyright (c) The PHP Group Zend Engine v4.1.26, Copyright (c) Zend Technologies

For the moment I only have one problem. The UPDATE ... FROM ... queries fail (code not having changed for 2 years) saying that a column from the updated table fails.

e.g., executing this code raises an error "Error while executing SQL query on database 'catalogue_numerique': no such column: aarb_sku":

UPDATE pim_art_arb_cat SET aarb_arbo = CASE WHEN aarb_arbo <> '' THEN aarb_arbo WHEN parb_arbo <> '' THEN parb_arbo ELSE '' END FROM pim_a_p_dec INNER JOIN pim_pro_arb_cat ON apdec_sku_pere = parb_sku AND apdec_sku_fils = aarb_sku;

I also checked with SQLiteStudio and libsqlite 3.36.0, 3.41.2 and 3.46.0.

I reproduce the problem with 3.41.2 but I don't have an older binary to go back to the first version where the regression appears.

I can provide the database used with this query.

Regards,

(2) By Richard Hipp (drh) on 2024-08-05 10:43:38 in reply to 1 [link] [source]

Please send the database schema only (no actual data required, just the schema) to drh at sqlite dot org.

(3.1) By ddevienne on 2024-08-05 10:54:25 edited from 3.0 in reply to 1 [link] [source]

Hi. I'm sure Richard (DRH) will sort it out, but independently of any possible regressions/changes, two remarks:

  • it may be a lookup issue, so fully-qualifying your column names with their table names, might be a quick work-around.
  • when aarb_arbo <> '', your UPDATE degenerates to aarb_arbo = aarb_arbo, i.e. the identity,
    therefore you could push that test into an outer WHERE clause, to update fewer rows
    (avoiding needless updates, unless you have triggers that depend on identity updates somehow...).

(4) By Stéphane Aulery (saulery) on 2024-08-05 11:04:38 in reply to 3.1 [link] [source]

Hi ddevienne,

I tested the full qualification and it didn't help.

Your suggestion is optimal but I looked for ease of reading over speed. The program it is part of is a batch that runs at night in just 7 minutes.

Regards,

(5) By ddevienne on 2024-08-05 11:17:58 in reply to 4 [link] [source]

I tested the full qualification and it didn't help.

Was worth a try, thanks for trying. In any case, DRH will figure it out.

Your suggestion is optimal but I looked for ease of reading over speed.
The program it is part of is a batch that runs at night in just 7 minutes.

7 minutes is quite long in SQLite's term.

If you have a lot of those identity-updates,
adding the WHERE clause could reduce IO significantly,
and thus provide a nice speed up perhaps. Faster is always better :)

And regarding readability, if you were using NULLs instead of empty strings '',
you could replace your CASE expression with a simple coalesce,
which would be simpler / shorter, and perhaps more SQL idiomatic. FWIW.

(6) By Richard Hipp (drh) on 2024-08-05 11:27:08 in reply to 1 [source]

The problem simplifies to this:

CREATE TABLE t1(aa INT,	bb INT);
CREATE TABLE t2(mm INT,	nn INT);
CREATE TABLE t3(xx INT,	yy INT);
UPDATE t1 SET bb = mm+xx FROM t2 INNER JOIN t3 ON nn=xx AND mm=aa;

The problem originates at check-in 98b3816bbaf539ea which says: "Fix the UPDATE-FROM logic so that it works the same as PostgreSQL..."

Sure enough, when I feed the query above into PG it tells me:

ERROR:  column "aa" does not exist
LINE 1: ...ET  bb = mm+xx FROM t2 INNER JOIN t3 ON nn = xx AND mm = aa;
                                                                    ^
HINT:  There is a column named "aa" in table "t1", but it cannot be referenced from this part of the query.

But if I rewrite the UPDATE thusly:

UPDATE t1 SET  bb = mm+xx FROM t2, t3 WHERE nn=xx AND mm=aa;

Then it appears to work, in both SQLite and in PG.

(7) By Stéphane Aulery (saulery) on 2024-08-05 11:31:08 in reply to 5 [link] [source]

There are only two queries like this.

I avoid NULL like the plague. Anyway the starting data is in CSV, loaded into an ad hoc SQLite database for a transformation and output a CSV file again. So there is no NULL value in my data.

Since you are curious, here is a trace of the program (before the library change).

[06-Jul-2024 04:30:32 Europe/Paris] *************************************************** [06-Jul-2024 04:30:32 Europe/Paris] *** PIM008 - Génération flux catalogue Publitas *** [06-Jul-2024 04:30:33 Europe/Paris] *************************************************** [06-Jul-2024 04:30:33 Europe/Paris] [06-Jul-2024 04:30:33 Europe/Paris] -x- Début de traitement -x- [06-Jul-2024 04:30:34 Europe/Paris] [06-Jul-2024 04:30:34 Europe/Paris] [06-Jul-2024 04:30:34 Europe/Paris] SITE [na] [06-Jul-2024 04:30:35 Europe/Paris] [06-Jul-2024 04:30:35 Europe/Paris] Création de la copie de travail des fichiers et insertion dans la base catalogue_numerique.sqlite [06-Jul-2024 04:30:35 Europe/Paris] [06-Jul-2024 04:30:36 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_a_p_dec.csv [06-Jul-2024 04:30:37 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_a_p_dec.csv [06-Jul-2024 04:30:38 Europe/Paris] - Suppression de la table si elle existe déjà pim_a_p_dec [06-Jul-2024 04:30:38 Europe/Paris] - Création de la table pim_a_p_dec [06-Jul-2024 04:30:38 Europe/Paris] - Création d'un index sur la colonne apdec_sku_fils [06-Jul-2024 04:30:39 Europe/Paris] - Création d'un index sur la colonne apdec_sku_pere [06-Jul-2024 04:30:40 Europe/Paris] - Insertion des données [06-Jul-2024 04:30:42 Europe/Paris] - 18741/18741 ligne(s) insérée(s) [06-Jul-2024 04:30:42 Europe/Paris] [06-Jul-2024 04:30:42 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_arb_cat.csv [06-Jul-2024 04:30:42 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_art_arb_cat.csv [06-Jul-2024 04:30:42 Europe/Paris] - Suppression de la table si elle existe déjà pim_art_arb_cat [06-Jul-2024 04:30:42 Europe/Paris] - Création de la table pim_art_arb_cat [06-Jul-2024 04:30:42 Europe/Paris] - Création d'un index sur la colonne aarb_sku [06-Jul-2024 04:30:43 Europe/Paris] - Insertion des données [06-Jul-2024 04:30:47 Europe/Paris] - 66002/66002 ligne(s) insérée(s) [06-Jul-2024 04:30:47 Europe/Paris] [06-Jul-2024 04:30:48 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_img.csv [06-Jul-2024 04:30:48 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_art_img.csv [06-Jul-2024 04:30:49 Europe/Paris] - Suppression de la table si elle existe déjà pim_art_img [06-Jul-2024 04:30:50 Europe/Paris] - Création de la table pim_art_img [06-Jul-2024 04:30:50 Europe/Paris] - Création d'un index sur la colonne aimg_sku [06-Jul-2024 04:30:51 Europe/Paris] - Insertion des données [06-Jul-2024 04:30:53 Europe/Paris] - 66837/66837 ligne(s) insérée(s) [06-Jul-2024 04:30:53 Europe/Paris] [06-Jul-2024 04:30:54 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_inf.csv [06-Jul-2024 04:30:54 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_art_inf.csv [06-Jul-2024 04:30:55 Europe/Paris] - Suppression de la table si elle existe déjà pim_art_inf [06-Jul-2024 04:30:55 Europe/Paris] - Création de la table pim_art_inf [06-Jul-2024 04:30:55 Europe/Paris] - Création d'un index sur la colonne ainf_sku [06-Jul-2024 04:30:56 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:00 Europe/Paris] - 66002/66002 ligne(s) insérée(s) [06-Jul-2024 04:31:00 Europe/Paris] [06-Jul-2024 04:31:01 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_ref.csv [06-Jul-2024 04:31:02 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_art_ref.csv [06-Jul-2024 04:31:03 Europe/Paris] - Suppression de la table si elle existe déjà pim_art_ref [06-Jul-2024 04:31:03 Europe/Paris] - Création de la table pim_art_ref [06-Jul-2024 04:31:03 Europe/Paris] - Création d'un index sur la colonne aref_sku [06-Jul-2024 04:31:04 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:06 Europe/Paris] - 66002/66002 ligne(s) insérée(s) [06-Jul-2024 04:31:07 Europe/Paris] [06-Jul-2024 04:31:07 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_txt_cat.csv [06-Jul-2024 04:31:07 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_art_txt_cat.csv [06-Jul-2024 04:31:10 Europe/Paris] - Suppression de la table si elle existe déjà pim_art_txt_cat [06-Jul-2024 04:31:11 Europe/Paris] - Création de la table pim_art_txt_cat [06-Jul-2024 04:31:12 Europe/Paris] - Création d'un index sur la colonne atxt_sku [06-Jul-2024 04:31:14 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:17 Europe/Paris] - 66002/66002 ligne(s) insérée(s) [06-Jul-2024 04:31:18 Europe/Paris] [06-Jul-2024 04:31:18 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_arb_cat.csv [06-Jul-2024 04:31:19 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_pro_arb_cat.csv [06-Jul-2024 04:31:20 Europe/Paris] - Suppression de la table si elle existe déjà pim_pro_arb_cat [06-Jul-2024 04:31:21 Europe/Paris] - Création de la table pim_pro_arb_cat [06-Jul-2024 04:31:22 Europe/Paris] - Création d'un index sur la colonne parb_sku [06-Jul-2024 04:31:23 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:24 Europe/Paris] - 2823/2823 ligne(s) insérée(s) [06-Jul-2024 04:31:24 Europe/Paris] [06-Jul-2024 04:31:24 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_img.csv [06-Jul-2024 04:31:26 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_pro_img.csv [06-Jul-2024 04:31:27 Europe/Paris] - Suppression de la table si elle existe déjà pim_pro_img [06-Jul-2024 04:31:27 Europe/Paris] - Création de la table pim_pro_img [06-Jul-2024 04:31:27 Europe/Paris] - Création d'un index sur la colonne pimg_sku [06-Jul-2024 04:31:27 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:29 Europe/Paris] - 7955/7955 ligne(s) insérée(s) [06-Jul-2024 04:31:29 Europe/Paris] [06-Jul-2024 04:31:30 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_inf.csv [06-Jul-2024 04:31:30 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_pro_inf.csv [06-Jul-2024 04:31:30 Europe/Paris] - Suppression de la table si elle existe déjà pim_pro_inf [06-Jul-2024 04:31:30 Europe/Paris] - Création de la table pim_pro_inf [06-Jul-2024 04:31:30 Europe/Paris] - Création d'un index sur la colonne pinf_sku [06-Jul-2024 04:31:31 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:32 Europe/Paris] - 2823/2823 ligne(s) insérée(s) [06-Jul-2024 04:31:32 Europe/Paris] [06-Jul-2024 04:31:33 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_ref.csv [06-Jul-2024 04:31:34 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_pro_ref.csv [06-Jul-2024 04:31:34 Europe/Paris] - Suppression de la table si elle existe déjà pim_pro_ref [06-Jul-2024 04:31:34 Europe/Paris] - Création de la table pim_pro_ref [06-Jul-2024 04:31:34 Europe/Paris] - Création d'un index sur la colonne pref_sku [06-Jul-2024 04:31:35 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:36 Europe/Paris] - 2823/2823 ligne(s) insérée(s) [06-Jul-2024 04:31:37 Europe/Paris] [06-Jul-2024 04:31:37 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_txt_cat.csv [06-Jul-2024 04:31:37 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnapim_pro_txt_cat.csv [06-Jul-2024 04:31:37 Europe/Paris] - Suppression de la table si elle existe déjà pim_pro_txt_cat [06-Jul-2024 04:31:37 Europe/Paris] - Création de la table pim_pro_txt_cat [06-Jul-2024 04:31:39 Europe/Paris] - Création d'un index sur la colonne ptxt_sku [06-Jul-2024 04:31:40 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:41 Europe/Paris] - 2823/2823 ligne(s) insérée(s) [06-Jul-2024 04:31:41 Europe/Paris] [06-Jul-2024 04:31:41 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_eco.csv [06-Jul-2024 04:31:41 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnax3_art_eco.csv [06-Jul-2024 04:31:43 Europe/Paris] - Suppression de la table si elle existe déjà x3_art_eco [06-Jul-2024 04:31:43 Europe/Paris] - Création de la table x3_art_eco [06-Jul-2024 04:31:43 Europe/Paris] - Création d'un index sur la colonne aeco_sku [06-Jul-2024 04:31:46 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:48 Europe/Paris] - 8653/8653 ligne(s) insérée(s) [06-Jul-2024 04:31:48 Europe/Paris] [06-Jul-2024 04:31:48 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_inf.csv [06-Jul-2024 04:31:48 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnax3_art_inf.csv [06-Jul-2024 04:31:48 Europe/Paris] - Suppression de la table si elle existe déjà x3_art_inf [06-Jul-2024 04:31:49 Europe/Paris] - Création de la table x3_art_inf [06-Jul-2024 04:31:49 Europe/Paris] - Création d'un index sur la colonne aix3_sku [06-Jul-2024 04:31:51 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:53 Europe/Paris] - 132279/132279 ligne(s) insérée(s) [06-Jul-2024 04:31:53 Europe/Paris] [06-Jul-2024 04:31:53 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_sto.csv [06-Jul-2024 04:31:53 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnax3_art_sto.csv [06-Jul-2024 04:31:55 Europe/Paris] - Suppression de la table si elle existe déjà x3_art_sto [06-Jul-2024 04:31:55 Europe/Paris] - Création de la table x3_art_sto [06-Jul-2024 04:31:55 Europe/Paris] - Création d'un index sur la colonne asto_sku [06-Jul-2024 04:31:56 Europe/Paris] - Insertion des données [06-Jul-2024 04:31:59 Europe/Paris] - 132275/132275 ligne(s) insérée(s) [06-Jul-2024 04:31:59 Europe/Paris] [06-Jul-2024 04:31:59 Europe/Paris] De //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_tar.csv [06-Jul-2024 04:31:59 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnax3_art_tar.csv [06-Jul-2024 04:31:59 Europe/Paris] - Suppression de la table si elle existe déjà x3_art_tar [06-Jul-2024 04:31:59 Europe/Paris] - Création de la table x3_art_tar [06-Jul-2024 04:32:01 Europe/Paris] - Création d'un index sur la colonne atar_sku [06-Jul-2024 04:32:03 Europe/Paris] - Insertion des données [06-Jul-2024 04:32:24 Europe/Paris] - 372792/372792 ligne(s) insérée(s) [06-Jul-2024 04:32:24 Europe/Paris] [06-Jul-2024 04:32:25 Europe/Paris] De F:/Traitements/PIM/PIM008_catalogue_numerique/cnf_ordre_type_image.csv [06-Jul-2024 04:32:25 Europe/Paris] Vers F:TraitementsPIMPIM008_catalogue_numeriquetmpinnacnf_ordre_type_image.csv [06-Jul-2024 04:32:26 Europe/Paris] - Suppression de la table si elle existe déjà cnf_ordre_type_image [06-Jul-2024 04:32:26 Europe/Paris] - Création de la table cnf_ordre_type_image [06-Jul-2024 04:32:27 Europe/Paris] - Insertion des données [06-Jul-2024 04:32:28 Europe/Paris] - 55/55 ligne(s) insérée(s) [06-Jul-2024 04:32:29 Europe/Paris] [06-Jul-2024 04:32:30 Europe/Paris] Préparation et nettoyage des données [06-Jul-2024 04:32:30 Europe/Paris] - Exécution de la requête .sql000_vacuum.sql [06-Jul-2024 04:33:02 Europe/Paris] - Exécution de la requête .sql001_supprimer_image_substitution_principale.sql [06-Jul-2024 04:33:06 Europe/Paris] - Exécution de la requête .sql002_creation_vue_articles_liste_image_art_ordre_type.sql [06-Jul-2024 04:33:08 Europe/Paris] - Exécution de la requête .sql002_creation_vue_produits_liste_image_art_ordre_type.sql [06-Jul-2024 04:33:11 Europe/Paris] - Exécution de la requête .sql003_creation_vue_articles_liste_image_art_ordre_final.sql [06-Jul-2024 04:33:11 Europe/Paris] - Exécution de la requête .sql003_creation_vue_produits_liste_image_art_ordre_final.sql [06-Jul-2024 04:33:12 Europe/Paris] - Exécution de la requête .sql004_creation_table_pre_art_img.sql [06-Jul-2024 04:33:14 Europe/Paris] - Exécution de la requête .sql004_creation_table_pre_pro_inf.sql [06-Jul-2024 04:33:15 Europe/Paris] - Exécution de la requête .sql005_creation_index_table_pim_art_ref.sql [06-Jul-2024 04:33:19 Europe/Paris] - Exécution de la requête .sql005_creation_index_table_pre_art_img.sql [06-Jul-2024 04:33:21 Europe/Paris] - Exécution de la requête .sql005_creation_index_table_pre_pro_inf.sql [06-Jul-2024 04:33:21 Europe/Paris] - Exécution de la requête .sql005_insertion_table_pre_art_img.sql [06-Jul-2024 04:33:45 Europe/Paris] - Exécution de la requête .sql005_insertion_table_pre_pro_inf.sql [06-Jul-2024 04:33:46 Europe/Paris] - Exécution de la requête .sql006_suppression_donnees_hors_referentiel.sql [06-Jul-2024 04:33:59 Europe/Paris] - Exécution de la requête .sql007_nettoyage_donnees.sql [06-Jul-2024 04:34:22 Europe/Paris] - Exécution de la requête .sql008_corriger_op_libelles.sql [06-Jul-2024 04:34:47 Europe/Paris] - Exécution de la requête .sql009_fusionner_textes.sql [06-Jul-2024 04:35:59 Europe/Paris] - Exécution de la requête .sql010_fusionner_arbo.sql [06-Jul-2024 04:36:02 Europe/Paris] - Exécution de la requête .sql012_ajouter_ecotaxes.sql [06-Jul-2024 04:36:03 Europe/Paris] - Exécution de la requête .sql013_fusionner_marque.sql [06-Jul-2024 04:36:09 Europe/Paris] - Exécution de la requête .sql014_fusionner_code_operation.sql [06-Jul-2024 04:36:12 Europe/Paris] - Exécution de la requête .sql015_creation_table_pre_pro_tar.sql [06-Jul-2024 04:36:13 Europe/Paris] - Exécution de la requête .sql016_creation_index_table_pre_pro_tar.sql [06-Jul-2024 04:36:15 Europe/Paris] - Exécution de la requête .sql016_insertion_table_pre_pro_tar.sql [06-Jul-2024 04:36:17 Europe/Paris] - Exécution de la requête .sql050_creation_vue_articles_catalogue_generique.sql [06-Jul-2024 04:36:19 Europe/Paris] - Exécution de la requête .sql060_creation_vue_produits_catalogue_generique.sql [06-Jul-2024 04:36:21 Europe/Paris] - Exécution de la requête .sql070_creation_vue_catalogue_generique.sql [06-Jul-2024 04:36:21 Europe/Paris] [06-Jul-2024 04:36:22 Europe/Paris] - catalogue_publitas_fr_generique.csv [06-Jul-2024 04:36:24 Europe/Paris] Ouverture en écriture du fichier F:TraitementsPIMPIM008_catalogue_numeriquetmpoutnacatalogue_publitas_fr_generique.csv [06-Jul-2024 04:36:24 Europe/Paris] Ouverture de la connexion à F:TraitementsPIMPIM008_catalogue_numeriquetmpinnacatalogue_numerique.sqlite [06-Jul-2024 04:36:25 Europe/Paris] Exécution de la requête [06-Jul-2024 04:36:49 Europe/Paris] Génération du fichier catalogue_publitas_fr_generique.csv [06-Jul-2024 04:36:50 Europe/Paris] Correction du fichier [06-Jul-2024 04:36:50 Europe/Paris] - Renommage des titre de colonnes des images supplémentaires [06-Jul-2024 04:36:52 Europe/Paris] - Insertion des urls du CDN [06-Jul-2024 04:36:53 Europe/Paris] - Passage systématique en HTTPS [06-Jul-2024 04:37:05 Europe/Paris] Sauvegarde du fichier Google Shopping final dans le dossier de données du site [06-Jul-2024 04:37:08 Europe/Paris] [06-Jul-2024 04:37:09 Europe/Paris] Mémoire consommée au pic : 102.6644 Mo [06-Jul-2024 04:37:10 Europe/Paris] Mémoire consommée à la fermeture : 52.6644 Mo [06-Jul-2024 04:37:11 Europe/Paris] Durée du traitement : 7 m 2 s [06-Jul-2024 04:37:12 Europe/Paris] [06-Jul-2024 04:37:14 Europe/Paris] -x- Fin de traitement -x-

(8) By Stéphane Aulery (saulery) on 2024-08-05 11:37:43 in reply to 6 [link] [source]

I have a hard time understanding why one would want to replicate PostgreSQL's limited behavior when SQLite was better, like SQL Server in this regard.

(9) By ddevienne on 2024-08-05 11:52:31 in reply to 7 [link] [source]

There are only two queries like this.

It's more a matter of how many rows are concerned.

But given your log, with 300K rows max, that optimization
would probably be dwarfed by the CSV in/out you are doing in your ETL.
But I'm of the school of always striving for the better.

I avoid NULL like the plague

:)

Since you are curious

I am indeed ;)

Thanks. As a Frenchman, it does speak to me!

[06-Jul-2024 04:30:38 Europe/Paris]  - Création de la table pim_a_p_dec
[06-Jul-2024 04:30:38 Europe/Paris]  - Création d'un index sur la colonne apdec_sku_fils
[06-Jul-2024 04:30:39 Europe/Paris]  - Création d'un index sur la colonne apdec_sku_pere
[06-Jul-2024 04:30:40 Europe/Paris]  - Insertion des données
[06-Jul-2024 04:30:42 Europe/Paris]  - 18741/18741 ligne(s) insérée(s)

Instead of 1) create-table then 2) create-index then 3) insert,
you may want to 1) create-table then 2) insert then 3) create-index.
It is typically faster, and could provide you a small boost in perf.
(but I get it, 7min is good enough for you already. Just sharing)

Also using a large page-cache usually helps, in case you don't already do. FWIW.

(10) By Stéphane Aulery (saulery) on 2024-08-05 12:27:40 in reply to 8 [link] [source]

Richard, do you consider the situation to be normal and in this case I have queries to rewrite or will you restore the previous behavior?

(11) By Stéphane Aulery (saulery) on 2024-08-05 12:33:51 in reply to 9 [link] [source] 

As much as I won't touch the previous query, delaying the creation of indexes is a good idea that can benefit many of my programs.

After a small adjustment of the CSV loading function the loading time of this database goes from 2 min to 15 s.

Thanks for this great idea.

<code>
[05-Aug-2024 14:20:47 Europe/Paris] ***************************************************
[05-Aug-2024 14:20:47 Europe/Paris] *** PIM008 - Génération flux catalogue Publitas ***
[05-Aug-2024 14:20:47 Europe/Paris] ***************************************************
[05-Aug-2024 14:20:47 Europe/Paris] 
[05-Aug-2024 14:20:47 Europe/Paris] -x- Début de traitement -x-
[05-Aug-2024 14:20:47 Europe/Paris] 
[05-Aug-2024 14:20:47 Europe/Paris] 
[05-Aug-2024 14:20:47 Europe/Paris] SITE [na]
[05-Aug-2024 14:20:47 Europe/Paris] 
[05-Aug-2024 14:20:47 Europe/Paris] Création de la copie de travail des fichiers et insertion dans la base catalogue_numerique.sqlite
[05-Aug-2024 14:20:48 Europe/Paris] 
[05-Aug-2024 14:20:48 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_a_p_dec.csv
[05-Aug-2024 14:20:48 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_a_p_dec.csv
[05-Aug-2024 14:20:48 Europe/Paris]  - Suppression de la table si elle existe déjà pim_a_p_dec
[05-Aug-2024 14:20:48 Europe/Paris]  - Création de la table pim_a_p_dec
[05-Aug-2024 14:20:48 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:48 Europe/Paris]  - 18853/18853 ligne(s) insérée(s)
[05-Aug-2024 14:20:48 Europe/Paris]  - Création d'un index sur la colonne apdec_sku_fils
[05-Aug-2024 14:20:48 Europe/Paris]  - Création d'un index sur la colonne apdec_sku_pere
[05-Aug-2024 14:20:48 Europe/Paris] 
[05-Aug-2024 14:20:48 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_arb_cat.csv
[05-Aug-2024 14:20:48 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_art_arb_cat.csv
[05-Aug-2024 14:20:48 Europe/Paris]  - Suppression de la table si elle existe déjà pim_art_arb_cat
[05-Aug-2024 14:20:48 Europe/Paris]  - Création de la table pim_art_arb_cat
[05-Aug-2024 14:20:48 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:48 Europe/Paris]  - 66172/66172 ligne(s) insérée(s)
[05-Aug-2024 14:20:48 Europe/Paris]  - Création d'un index sur la colonne aarb_sku
[05-Aug-2024 14:20:48 Europe/Paris] 
[05-Aug-2024 14:20:48 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_img.csv
[05-Aug-2024 14:20:48 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_art_img.csv
[05-Aug-2024 14:20:48 Europe/Paris]  - Suppression de la table si elle existe déjà pim_art_img
[05-Aug-2024 14:20:48 Europe/Paris]  - Création de la table pim_art_img
[05-Aug-2024 14:20:49 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:49 Europe/Paris]  - 66896/66896 ligne(s) insérée(s)
[05-Aug-2024 14:20:49 Europe/Paris]  - Création d'un index sur la colonne aimg_sku
[05-Aug-2024 14:20:49 Europe/Paris] 
[05-Aug-2024 14:20:49 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_inf.csv
[05-Aug-2024 14:20:49 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_art_inf.csv
[05-Aug-2024 14:20:49 Europe/Paris]  - Suppression de la table si elle existe déjà pim_art_inf
[05-Aug-2024 14:20:49 Europe/Paris]  - Création de la table pim_art_inf
[05-Aug-2024 14:20:49 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:49 Europe/Paris]  - 66172/66172 ligne(s) insérée(s)
[05-Aug-2024 14:20:49 Europe/Paris]  - Création d'un index sur la colonne ainf_sku
[05-Aug-2024 14:20:50 Europe/Paris] 
[05-Aug-2024 14:20:50 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_ref.csv
[05-Aug-2024 14:20:50 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_art_ref.csv
[05-Aug-2024 14:20:50 Europe/Paris]  - Suppression de la table si elle existe déjà pim_art_ref
[05-Aug-2024 14:20:50 Europe/Paris]  - Création de la table pim_art_ref
[05-Aug-2024 14:20:50 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:50 Europe/Paris]  - 66172/66172 ligne(s) insérée(s)
[05-Aug-2024 14:20:50 Europe/Paris]  - Création d'un index sur la colonne aref_sku
[05-Aug-2024 14:20:50 Europe/Paris] 
[05-Aug-2024 14:20:50 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_art_txt_cat.csv
[05-Aug-2024 14:20:50 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_art_txt_cat.csv
[05-Aug-2024 14:20:50 Europe/Paris]  - Suppression de la table si elle existe déjà pim_art_txt_cat
[05-Aug-2024 14:20:54 Europe/Paris]  - Création de la table pim_art_txt_cat
[05-Aug-2024 14:20:54 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:55 Europe/Paris]  - 66172/66172 ligne(s) insérée(s)
[05-Aug-2024 14:20:55 Europe/Paris]  - Création d'un index sur la colonne atxt_sku
[05-Aug-2024 14:20:55 Europe/Paris] 
[05-Aug-2024 14:20:55 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_arb_cat.csv
[05-Aug-2024 14:20:55 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_pro_arb_cat.csv
[05-Aug-2024 14:20:55 Europe/Paris]  - Suppression de la table si elle existe déjà pim_pro_arb_cat
[05-Aug-2024 14:20:55 Europe/Paris]  - Création de la table pim_pro_arb_cat
[05-Aug-2024 14:20:55 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:55 Europe/Paris]  - 2855/2855 ligne(s) insérée(s)
[05-Aug-2024 14:20:55 Europe/Paris]  - Création d'un index sur la colonne parb_sku
[05-Aug-2024 14:20:55 Europe/Paris] 
[05-Aug-2024 14:20:55 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_img.csv
[05-Aug-2024 14:20:55 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_pro_img.csv
[05-Aug-2024 14:20:55 Europe/Paris]  - Suppression de la table si elle existe déjà pim_pro_img
[05-Aug-2024 14:20:55 Europe/Paris]  - Création de la table pim_pro_img
[05-Aug-2024 14:20:55 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:55 Europe/Paris]  - 8007/8007 ligne(s) insérée(s)
[05-Aug-2024 14:20:55 Europe/Paris]  - Création d'un index sur la colonne pimg_sku
[05-Aug-2024 14:20:55 Europe/Paris] 
[05-Aug-2024 14:20:55 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_inf.csv
[05-Aug-2024 14:20:55 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_pro_inf.csv
[05-Aug-2024 14:20:55 Europe/Paris]  - Suppression de la table si elle existe déjà pim_pro_inf
[05-Aug-2024 14:20:55 Europe/Paris]  - Création de la table pim_pro_inf
[05-Aug-2024 14:20:55 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:55 Europe/Paris]  - 2855/2855 ligne(s) insérée(s)
[05-Aug-2024 14:20:55 Europe/Paris]  - Création d'un index sur la colonne pinf_sku
[05-Aug-2024 14:20:55 Europe/Paris] 
[05-Aug-2024 14:20:55 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_ref.csv
[05-Aug-2024 14:20:55 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_pro_ref.csv
[05-Aug-2024 14:20:55 Europe/Paris]  - Suppression de la table si elle existe déjà pim_pro_ref
[05-Aug-2024 14:20:55 Europe/Paris]  - Création de la table pim_pro_ref
[05-Aug-2024 14:20:55 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:55 Europe/Paris]  - 2855/2855 ligne(s) insérée(s)
[05-Aug-2024 14:20:55 Europe/Paris]  - Création d'un index sur la colonne pref_sku
[05-Aug-2024 14:20:56 Europe/Paris] 
[05-Aug-2024 14:20:56 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/PIM/pim_pro_txt_cat.csv
[05-Aug-2024 14:20:56 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\pim_pro_txt_cat.csv
[05-Aug-2024 14:20:56 Europe/Paris]  - Suppression de la table si elle existe déjà pim_pro_txt_cat
[05-Aug-2024 14:20:56 Europe/Paris]  - Création de la table pim_pro_txt_cat
[05-Aug-2024 14:20:56 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:56 Europe/Paris]  - 2855/2855 ligne(s) insérée(s)
[05-Aug-2024 14:20:56 Europe/Paris]  - Création d'un index sur la colonne ptxt_sku
[05-Aug-2024 14:20:56 Europe/Paris] 
[05-Aug-2024 14:20:56 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_eco.csv
[05-Aug-2024 14:20:56 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\x3_art_eco.csv
[05-Aug-2024 14:20:56 Europe/Paris]  - Suppression de la table si elle existe déjà x3_art_eco
[05-Aug-2024 14:20:56 Europe/Paris]  - Création de la table x3_art_eco
[05-Aug-2024 14:20:56 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:56 Europe/Paris]  - 8770/8770 ligne(s) insérée(s)
[05-Aug-2024 14:20:56 Europe/Paris]  - Création d'un index sur la colonne aeco_sku
[05-Aug-2024 14:20:56 Europe/Paris] 
[05-Aug-2024 14:20:56 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_inf.csv
[05-Aug-2024 14:20:56 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\x3_art_inf.csv
[05-Aug-2024 14:20:56 Europe/Paris]  - Suppression de la table si elle existe déjà x3_art_inf
[05-Aug-2024 14:20:57 Europe/Paris]  - Création de la table x3_art_inf
[05-Aug-2024 14:20:57 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:57 Europe/Paris]  - 132756/132756 ligne(s) insérée(s)
[05-Aug-2024 14:20:57 Europe/Paris]  - Création d'un index sur la colonne aix3_sku
[05-Aug-2024 14:20:57 Europe/Paris] 
[05-Aug-2024 14:20:57 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_sto.csv
[05-Aug-2024 14:20:57 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\x3_art_sto.csv
[05-Aug-2024 14:20:57 Europe/Paris]  - Suppression de la table si elle existe déjà x3_art_sto
[05-Aug-2024 14:20:57 Europe/Paris]  - Création de la table x3_art_sto
[05-Aug-2024 14:20:58 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:20:58 Europe/Paris]  - 132752/132752 ligne(s) insérée(s)
[05-Aug-2024 14:20:58 Europe/Paris]  - Création d'un index sur la colonne asto_sku
[05-Aug-2024 14:20:58 Europe/Paris] 
[05-Aug-2024 14:20:58 Europe/Paris]  De   //SRVXXX/Partage/CATPUBLITAS/X3/x3_art_tar.csv
[05-Aug-2024 14:20:58 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\x3_art_tar.csv
[05-Aug-2024 14:20:58 Europe/Paris]  - Suppression de la table si elle existe déjà x3_art_tar
[05-Aug-2024 14:20:59 Europe/Paris]  - Création de la table x3_art_tar
[05-Aug-2024 14:20:59 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:21:00 Europe/Paris]  - 374500/374500 ligne(s) insérée(s)
[05-Aug-2024 14:21:00 Europe/Paris]  - Création d'un index sur la colonne atar_sku
[05-Aug-2024 14:21:01 Europe/Paris] 
[05-Aug-2024 14:21:01 Europe/Paris]  De   F:/Traitements/PIM/PIM008_catalogue_numerique/cnf_ordre_type_image.csv
[05-Aug-2024 14:21:01 Europe/Paris]  Vers F:\Traitements\PIM\PIM008_catalogue_numerique\tmp\in\na\cnf_ordre_type_image.csv
[05-Aug-2024 14:21:01 Europe/Paris]  - Suppression de la table si elle existe déjà cnf_ordre_type_image
[05-Aug-2024 14:21:01 Europe/Paris]  - Création de la table cnf_ordre_type_image
[05-Aug-2024 14:21:01 Europe/Paris]  - Insertion des données
[05-Aug-2024 14:21:01 Europe/Paris]  - 55/55 ligne(s) insérée(s)
[05-Aug-2024 14:21:01 Europe/Paris] 
</code>

(12) By ddevienne on 2024-08-05 12:42:49 in reply to 10 [link] [source]

FWIW, I've asked on the PostgreSQL ML why the INNER JOIN form is not allowed. No answers so far.

(13) By Bo Lindbergh (_blgl_) on 2024-08-05 15:30:53 in reply to 6 [link] [source]

"so that it works the same as PostgreSQL" is a weak argument when there are so many other places where SQLite is much laxer than PostgreSQL. Consider this schema:

    create table a (
        aid integer primary key,
        aval integer
    );

    create table b (
        bid integer primary key,
        bval integer
    );

    create table c (
        cid integer primary key,
        aid integer,
        bid integer
    );

The right way to do a three-way join looks like this:

    select * from
        a
            join c on c.aid = a.aid
            join b on b.bid = c.bid;

But SQLite will also cheerfully accept the wrong way:

    select * from
        a
            join b on b.bid = c.bid
            join c on c.aid = a.aid;

(14) By Eduardo on 2024-08-05 15:36:08 in reply to 12 [link] [source]

The INNER JOIN is between tables t2 and t3, table t1 is not involved and aa column don't exist in the inner join. Perhaps adding t1.aa or joining with t1 may solve it. The proposed workaround should not work, afaik, because INNER JOIN and using ',' are equivalent.

(15) By ddevienne on 2024-08-05 15:55:48 in reply to 14 [link] [source]

That would do a self-join, as the doc clearly says. So no.

(16) By Stéphane Aulery (saulery) on 2024-08-05 16:07:17 in reply to 15 [link] [source]

UPDATE t1 SET bb = mm+xx FROM t2, t3 WHERE nn=xx AND mm=aa;

is working. aa filed is seen in WHERE clause but not in FROM clause.

E.g. this second query also works:

UPDATE pim_art_arb_cat SET aarb_arbo = CASE WHEN aarb_arbo <> '' THEN aarb_arbo WHEN parb_arbo <> '' THEN parb_arbo ELSE '' END FROM pim_a_p_dec INNER JOIN pim_pro_arb_cat ON apdec_sku_pere = parb_sku WHERE apdec_sku_fils = aarb_sku;

(17) By Richard Hipp (drh) on 2024-08-05 18:44:44 in reply to 8 [link] [source]

I think the problem is that we misunderstood how UPDATE FROM was suppose to work when we first implemented it in SQLite. For an UPDATE FROM that is conceptually like this:

UPDATE FROM t1 SET ... FROM t2 JOIN t3 JOIN t4 ...;

The query that drives the UPDATE FROM (the query that determines which rows are going to get updated) should be like this (form 1):

SELECT * FROM t1, (SELECT * FROM t2 JOIN t3 JOIN t4);

In other words, the entire FROM clause of the UPDATE FROM should become a subquery unto itself. Prior to the change in version 3.39.0 that brought SQLite into alignment with PostgreSQL, SQLite was doing a query like this (form 2):

SELECT * FROM t1, t2 JOIN t3 JOIN t4

Among the differences is that in the first form, an ON clause in the subquery is not allowed to reference the t1 table, but an ON clause on one of the later tables of the second form is allowed to reference the t1 table. To reiterate: The first form is correct. The second form is what SQLite was (incorrectly) doing prior to version 3.39.0.

(18) By Stéphane Aulery (saulery) on 2024-08-05 21:30:03 in reply to 17 [link] [source]

Form 1 is fine if you care about PostgreSQL compliance, but Form 2 is just as fair as it looks like what SQL Server UPDATE FROM(https://learn.microsoft.com/fr-fr/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16) does.

I have read the PostgreSQL UPDATE page(https://www.postgresql.org/docs/current/sql-update.html).

For me, allowing a FROM while castrating it from the possibility of doing joins is an inconsistency. The page also talks about using an alias in the FROM clause for the target table, if we want to do a self-join.

In my opinion the 'Notes' section on PostgreSQL page explains confusedly why FROM is castrated. The designers were struggling and intentionally limited the syntax of FROM to avoid having to implement it correctly.

In fact, it is already not what SQLite does which allows joins in the FROM clause. So SQLite is halfway between PostgreSQL and SQL Server.

I find Form 2 more natural and more powerful considering the syntax of FROM in SELECT. I am very familiar with SQL Server and have never used PostgreSQL so I am not put off and I have my own distortion :-)

I suggest you revert to the previous behavior which is more consistent and powerful, as SQLite seems not bound to PostgreSQL design limits, if Form 2 query is safe in SQLite.