getParcelle with empty ccopre might return results from a prefixed section

[landryb]

https://ids.craig.fr/cadastrapp/services/getParcelle?cgocommune=630255&dnupla=101&ccopre=&ccosec=ZB yields

[
  {
    "parcelle": "630255000ZB0101",
    "cgocommune": "630255",
    "dnvoiri": "    ",
    "dindic": " ",
    "cconvo": "",
    "dvoilib": "RECEVEUSE",
    "ccopre": "",
    "ccosec": "ZB",
    "dnupla": "101",
    "dcntpa": 11680
  },
  {
    "parcelle": "630255266ZB0101",
    "cgocommune": "630255",
    "dnvoiri": "    ",
    "dindic": " ",
    "cconvo": "",
    "dvoilib": "LES CHAZOTS",
    "ccopre": "266",
    "ccosec": "ZB",
    "dnupla": "101",
    "dcntpa": 2470
  }
]

the second entry shouldnt be returned as it doesnt match the given ccopre parameter.

@MaelREBOUX do you have cities with prefixed sections having the same name (eg in my case 266ZB and ZB), and having a plot with the same dnupla/id ? If so you should also have this issue.

[landryb]

not specifying ccopre in the query params gives the same result.

[landryb]

looking in the code at https://github.com/georchestra/cadastrapp/blob/master/cadastrapp/src/main/java/org/georchestra/cadastrapp/service/ParcelleController.java#L138, calling the API with getParcelle?cgocommune=630255&dnupla=101&ccopre=&ccosec=ZB' parms, the SQL query generated lacks the provided ccopre param in the WHERE, so that explains why i get two entries.

select distinct p.parcelle, p.cgocommune, p.dnvoiri, p.dindic, p.cconvo, p.dvoilib, p.ccopre, p.ccosec, p.dnupla, p.dcntpa from cad2021.parcelle p where cgocommune = ?  and ccosec = ?  and dnupla = ?

It's not because a parameter is empty that it should be ignored ? There might be other occurences of this bug i think....

@pierrejego ?

[landryb]

i think getDnuplaList API might suffer from the same issue (eg querying with ccopre set to empty).

[db1:5432] cadastrapp@cadastrapp=> select count(distinct(dnupla)) from cad2021.parcelle where cgocommune='630255' and ccopre='266' and ccosec='ZB';
   148
[db1:5432] cadastrapp@cadastrapp=> select count(distinct(dnupla)) from cad2021.parcelle where cgocommune='630255' and ccosec='ZB';
   176
[db1:5432] cadastrapp@cadastrapp=> select count(distinct(dnupla)) from cad2021.parcelle where cgocommune='630255' and ccopre='' and ccosec='ZB';
   159

via the API..

$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getDnuplaList?cgocommune=630255&ccopre=&ccosec=ZB' | jq . | grep -c dnupla
176
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getDnuplaList?cgocommune=630255&ccopre=266&ccosec=ZB' | jq . | grep -c dnupla
148

so ccopre is ignored too.

[landryb]

i have a potential fix that works for those two cases, but that need careful examination to check for side-effects:

$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getDnuplaList?cgocommune=630255&ccosec=ZB' | jq . | grep -c dnupla         
176
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getDnuplaList?cgocommune=630255&ccosec=ZB&ccopre=' | jq . | grep -c dnupla
159
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getDnuplaList?cgocommune=630255&ccosec=ZB&ccopre=266' | jq . | grep -c dnupla
148
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getParcelle?cgocommune=630255&dnupla=101&ccosec=ZB' | jq .|grep parcelle
    "parcelle": "630255000ZB0101",
    "parcelle": "630255266ZB0101",
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getParcelle?cgocommune=630255&dnupla=101&ccopre=&ccosec=ZB' | jq .|grep parcelle
    "parcelle": "630255000ZB0101",
$curl -s 'https://ids.dev.craig.fr/cadastrapp/services/getParcelle?cgocommune=630255&dnupla=101&ccopre=266&ccosec=ZB' | jq .|grep parcelle
    "parcelle": "630255266ZB0101",

[MaelREBOUX]

wow wow ! I confirm the bug with the 1.9 version. Seems present since a long time :(

The correct SQL should be, looking for a AD 18 plot :

select distinct p.parcelle, p.cgocommune, p.dnvoiri, p.dindic, p.cconvo, p.dvoilib, p.ccopre, p.ccosec, p.dnupla, p.dcntpa 
from app_cadastrapp.parcelle p 
where cgocommune = '350206'  
and ccosec = 'AO'  
AND ccopre = ''
and dnupla = '18'
ORDER BY parcelle

Note the sort by the parcelle attribute.