WITH SELECT et FireDac

Version imprimable

Bonjour,

J'ouvre un petit topic pour un problème entre Firedac et une requête SQL sur MariaDB :

Mon collègue utilise un TFQuery (Delphi 10.3) et une base MariaDB 10.4
Dans son code il a une requête du style :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
WITH t_result as (
    SELECT
        col3,
        COALESCE((
            SELECT SUM(t1.num1 * t1.num2) 
            FROM t1        
            WHERE t1.idt2 = t2.idx
        ),0) AS col1, 
        COALESCE((
            SELECT SUM(t1.num1 * t1.num2)
            FROM t1
            WHERE t1.id2 = t2.idy
        ),0) AS col2
    FROM t2
        LEFT JOIN t3 
            ON t3.id=t2.idt3
    WHERE t3.idz=1
)
SELECT *,
    (col3 - col2) AS col4,
    (CASE WHEN (col3 - col1 - col2)>=0 THEN (col3 - col1 - col2) ELSE 0 END) AS col5
FROM  t_result

Lorsque l'on passe FDQuery.Active à TRUE pour la première fois (et uniquement la première fois) la requête plante et renvoie l'erreur "t_result does not exists" mais les données sont quand même renvoyées.

Les activations suivantes fonctionnent bien sans qu'une exception soit levée et on récupère les bons résultats...

Il a réussi à contourner le problème en passant par un SELECT ... FROM (SELECT .... FROM.... ) AS t_result

Mais le mystère reste entier...

Voici un bout de SQL qui met ce comportement en évidence :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
 
-- Script a exécuter via HeidiSQL pour créer les tables 
 
CREATE TABLE `tablea` (
  `Aid` int(11) NOT NULL AUTO_INCREMENT,
  `A1` varchar(100) DEFAULT NULL,
  `A2` int(11) DEFAULT NULL,
  PRIMARY KEY (`Aid`)
) ENGINE=InnoDB ;
 
CREATE TABLE `tableb` (
  `Bid` int(11) NOT NULL AUTO_INCREMENT,
  `Aid` int(11) DEFAULT NULL,
  `B1` varchar(100) DEFAULT NULL,
  `B2` int(11) DEFAULT NULL,
  PRIMARY KEY (`Bid`)
) ENGINE=InnoDB ; 
 
INSERT INTO tablea (Aid, A1, A2) VALUES(1, 'RA1', 1);
INSERT INTO tablea (Aid, A1, A2) VALUES(2, 'RA2', 2);
INSERT INTO tablea (Aid, A1, A2) VALUES(3, 'RA3', 3);
INSERT INTO tablea (Aid, A1, A2) VALUES(4, 'RA4', 4);
INSERT INTO tablea (Aid, A1, A2) VALUES(5, 'RA5', 5);
INSERT INTO tablea (Aid, A1, A2) VALUES(6, 'RA6', 6);
INSERT INTO tablea (Aid, A1, A2) VALUES(7, 'RA7', 7);
INSERT INTO tablea (Aid, A1, A2) VALUES(8, 'RA8', 8);
 
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(1, 1, 'RB1', 1);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(2, 1, 'RB1', 2);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(3, 1, 'RB3', 3);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(4, 2, 'RB4', 4);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(5, 4, 'RB5', 5);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(6, 4, 'RB5', 6);
INSERT INTO tableb (Bid, Aid, B1, B2) VALUES(7, 4, 'RB5', 7);
 
-- A placer dans un TFDQuery
 
WITH t_result as (
    SELECT
        B2,
        COALESCE((
            SELECT SUM(tablea.A2 * tablea.A2) 
            FROM tablea        
            WHERE tablea.Aid = tableb.Aid 
        ),0) AS col1
    FROM tableb
)
SELECT *,
    (CASE WHEN (B2 - col1)>=0 THEN (B2 - col1) ELSE 0 END) AS col5
FROM  t_result ;

Si quelqu'un a une idée.... ou une explication

04/02/2021, 11h19
ShaiLeTroll

Peut-être faire un Prepare Explicite
On peut penser que le premier Active fait un Prepare Implicite qui reste preparé côté MySQL tant que l'on lui change pas le SQL côté Delphi qui dé-préparerait le requête jusqu'au prochain Active
04/02/2021, 11h21
sergio_is_back

Citation:

Envoyé par ShaiLeTroll

Peut-être faire un Prepare Explicite
On peut penser que le premier Active fait un Prepare Implicite qui reste preparé côté MySQL tant que l'on lui change pas le SQL côté Delphi qui dé-préparerait le requête jusqu'au prochain Active

On y avait pensé, on l'a fait mais sans plus de résultats...
Ça ne change pas ce comportement bizarre
04/02/2021, 11h34
ShaiLeTroll
Sinon, ce n'est qu'une Notification d'Exception et pas une Exception en final si les données sont disponibles

Est-ce que FireDAC fait une analyse du SQL en amont et cherche les metadata de t_result ?
Est-ce une erreur MySQL ou FireDAC d'ailleurs ?

avec un alias c'est pareil ?
Code:

1 2 3 SELECT r.*, (CASE WHEN (r.B2 - r.col1)>=0 THEN (r.B2 - r.col1) ELSE 0 END) AS col5 FROM t_result as r;

Il n'y aurait pas une table t_result existante dans la base ou une sorte de mot réservé ? Peut-être qu'en changeant simplement le nom de la CTE
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 WITH r as ( SELECT B2, COALESCE(( SELECT SUM(tablea.A2 * tablea.A2) FROM tablea WHERE tablea.Aid = tableb.Aid ),0) AS col1 FROM tableb ) SELECT *, (CASE WHEN (B2 - col1)>=0 THEN (B2 - col1) ELSE 0 END) AS col5 FROM r;
en tout cas, dans la base de données "bidon", grâce au script fourni (merci c'est rare d'avoir ça de fourni ) , au design time c'est nickel.
Cela dit je ne suis pas aller jusqu'à le faire au runtime ;) puis, si et je n'ai pas d'erreurs.

Cependant, après vérification j'ai MariaDB 10.3 mais c'est bien sur Rio que j'ai fait le test

04/02/2021, 12h12
sergio_is_back

1 pièce(s) jointe(s)

Citation:

en tout cas, dans la base de données "bidon", grâce au script fourni (merci c'est rare d'avoir ça de fourni ) , au design time c'est nickel.
Cela dit je ne suis pas aller jusqu'à le faire au runtime ;)

C'est justement au runtime que cela pose problème, lors du design dans le concepteur, en passant Active à True sur la FDQuery, ça fonctionne sans message d'erreur ou quelconque notification

Il n'y aucune table "t_result" dans la base MariaDB et à ma connaissance "t_result" ne semble pas être un mot réservé (mais bon on pourrait essayer de changer ce nom, c'est une idée)

Pour répondre à ShaiLeTroll :

Oui c'est une notification et pas une exception bloquante
Il nous semble que c'est une exception MySQL et pas Firedac mais c'est pas clair car cette requête exécutée sous DBeaver il n'y pas d'erreur à l'exécution... Ni avec HeidiSQL d'ailleurs, même pas un warning

Pièce jointe 590137

Bon après on a une solution de contournement donc, c'est pas bloquant, mais c'était pour notre culture personnelle (pour être moins cons ce soir) si quelqu'un avait une explication où avait déjà utilisé WITH... SELECT avec FireDac
04/02/2021, 12h18
SergioMaster

1 pièce(s) jointe(s)

Citation:

Envoyé par sergio_is_back

C'est justement au runtime que cela pose problème

entre temps j'avais fait le test ;)

Citation:

où avait déjà utilisé WITH... SELECT avec FireDac

Je l'utilise très souvent (SGBD Firebird) et n'ai jamais eu ça.

Mais tout n'a pas été dit, du moins ne l'ai-je pas lu !
J'ai en effet réussi à obtenir le même message avec une application VCL (mon test fut fait en FMX) et uniquement en mode debug (à l'exécution sans debug pas de message)
en ce cas rien n'empêche de confiruger le debugger pour ignorer l'exception
Pièce jointe 590142
sans avoir à modifier le SQL
04/02/2021, 13h42
ShaiLeTroll

Si c'est une notification d'exception niveau Physique du Wrapper FireDAC du Driver MySQL qui ne remonte pas jusqu'au niveau Client de FireDAC, en dehors un débogage éventuellement pénible si l'on ne veut pas masquer l'exception, cela n'est pas génant.

Cependant, je serais aussi curieux, active un Monitor SQL voir si c'est ta requête qui pose problème, ou une requête interne de FireDAC
Voir DirectExecute à True, PreprocessCmdText à False ...
04/02/2021, 13h49
sergio_is_back

Citation:

Envoyé par ShaiLeTroll

Si c'est une notification d'exception niveau Physique du Wrapper FireDAC du Driver MySQL qui ne remonte pas jusqu'au niveau Client de FireDAC, en dehors un débogage éventuellement pénible si l'on ne veut pas masquer l'exception, cela n'est pas génant.

Cependant, je serais aussi curieux, active un Monitor SQL voir si c'est ta requête qui pose problème, ou une requête interne de FireDAC
Voir DirectExecute à True, PreprocessCmdText à False ...

On fait plusieurs tests sans trouver plus d'explications, on verra ça plus tard

On a fait un test hors de l'environnement de développement (avec le SQL d'origine WITH ... SELECT), pas de messages disgracieux, les données remontent correctement

J'arrête de vous prendre la tête avec ça, merci pour les pistes évoquées