View Issue Details

This bug affects 1 person(s).
 10
IDProjectCategoryView StatusLast Update
18615Bug reportsInstallationpublic2023-06-12 10:45
Reporterriqcles Assigned Toc_schmitz  
PrioritynormalSeverityblock 
Status closedResolutionfixed 
Product Version5.5.x 
Summary18615: Data integrity SQL syntax error on Postgres
Description

Hello,
I perform the data integrity test to clean the database.

After a while, I have the "Fix" button that appears.

I use the "fix" button
it runs for a while, then after an error message.

Looking in the logs we find this:

2023-02-02 15:34:11.252 CET [32469] ERROR: syntax error on or near "LEFT" at character 43
2023-02-02 15:34:11.252 CET [32469] INSTRUCTION: DELETE FROM "lime_quota_languagesettings" LEFT JOIN lime_quota q ON lime_quota_languagesettings.quotals_quota_id=q.id WHERE (q.id IS NULL)

I have broken down the query as follows:
SELECT * FROM "lime_quota_languagesettings" LEFT JOIN lime_quota q ON lime_quota_languagesettings.quotals_quota_id=q.id WHERE (q.id IS NULL)

first result: I have rows with NULL and others not

we continue :

select * from lime_quota_languagesettings as lime1 where lime1.quotals_name IS NULL;

I have NULLs for name

but I did not find a NULL id

And the last :
select * from lime_quota as lime1

I put in screenshot the results of the 3 queries,
I don't understand the syntax error.

TagsNo tags attached.
Bug heat10
Complete LimeSurvey version number (& build)Version 5.4.5+221010
I will donate to the project if issue is resolvedNo
Browser
Database type & versionPostgresql 13
Server OS (if known)
Webserver software & version (if known)
PHP Version7.4.32

Users monitoring this issue

User List There are no users monitoring this issue.

Activities

riqcles

riqcles

2023-02-02 18:30

reporter  

1.png (79,960 bytes)   
1.png (79,960 bytes)   
2.png (79,361 bytes)   
2.png (79,361 bytes)   
3.png (54,335 bytes)   
3.png (54,335 bytes)   
4.png (26,825 bytes)   
4.png (26,825 bytes)   
DenisChenu

DenisChenu

2023-02-02 18:31

developer   ~73641

Seems files are not shown ?

riqcles

riqcles

2023-02-02 19:22

reporter   ~73642

After this : http://192.168.56.111/limesurvey/index.php/admin/checkintegrity?sa=fixintegrity
somes captures

Integrity_1.png (58,813 bytes)   
Integrity_1.png (58,813 bytes)   
Integrity_2.png (80,831 bytes)   
Integrity_2.png (80,831 bytes)   
Integrity_3.png (118,212 bytes)
Integrity_4.png (61,968 bytes)   
Integrity_4.png (61,968 bytes)   
Integrity_5.png (73,192 bytes)   
Integrity_5.png (73,192 bytes)   
ollehar

ollehar

2023-05-03 15:03

administrator   ~74787

You're not supposed to LEFT JOIN in delete?

riqcles

riqcles

2023-05-05 12:23

reporter   ~74837

Good morning,
I didn't do anything other than use the cleanup function. It is this function that generated the code.

c_schmitz

c_schmitz

2023-06-08 17:41

administrator   ~75489

Last edited: 2023-06-08 17:41

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-delete-join/
@Postgres Thank you for nothing.

c_schmitz

c_schmitz

2023-06-08 18:19

administrator   ~75490

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=34787

c_schmitz

c_schmitz

2023-06-08 18:21

administrator   ~75493

Fix committed to 5.x branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=34788

LimeBot

LimeBot

2023-06-12 10:45

administrator   ~75543

Fixed in Release 6.1.3+230612

Related Changesets

LimeSurvey: master 20775288

2023-06-08 18:03:26

c_schmitz

Details Diff
Fixed issue 18615: Data integrity SQL syntax error on Postgres when trying to fix quotas Affected Issues
18615
mod - application/controllers/admin/CheckIntegrity.php Diff File

LimeSurvey: 5.x e747eb5c

2023-06-08 18:03:26

c_schmitz

Details Diff
Fixed issue 18615: Data integrity SQL syntax error on Postgres when trying to fix quotas Affected Issues
18615
mod - application/controllers/admin/CheckIntegrity.php Diff File

Issue History

Date Modified Username Field Change
2023-02-02 18:30 riqcles New Issue
2023-02-02 18:30 riqcles File Added: 1.png
2023-02-02 18:30 riqcles File Added: 2.png
2023-02-02 18:30 riqcles File Added: 3.png
2023-02-02 18:30 riqcles File Added: 4.png
2023-02-02 18:31 DenisChenu Note Added: 73641
2023-02-02 18:31 DenisChenu Bug heat 0 => 2
2023-02-02 19:22 riqcles Note Added: 73642
2023-02-02 19:22 riqcles File Added: Integrity_1.png
2023-02-02 19:22 riqcles File Added: Integrity_2.png
2023-02-02 19:22 riqcles File Added: Integrity_3.png
2023-02-02 19:22 riqcles File Added: Integrity_4.png
2023-02-02 19:22 riqcles File Added: Integrity_5.png
2023-02-02 19:22 riqcles Bug heat 2 => 4
2023-05-03 15:03 ollehar Note Added: 74787
2023-05-03 15:03 ollehar Bug heat 4 => 6
2023-05-03 15:03 ollehar Priority none => normal
2023-05-03 15:03 ollehar Status new => acknowledged
2023-05-03 15:03 ollehar Summary data integrity fail => Data integrity SQL syntax error on Postgres
2023-05-05 12:23 riqcles Note Added: 74837
2023-06-08 17:41 c_schmitz Note Added: 75489
2023-06-08 17:41 c_schmitz Bug heat 6 => 8
2023-06-08 17:41 c_schmitz Note Edited: 75489
2023-06-08 18:03 c_schmitz Assigned To => c_schmitz
2023-06-08 18:03 c_schmitz Status acknowledged => resolved
2023-06-08 18:03 c_schmitz Resolution open => fixed
2023-06-08 18:19 c_schmitz Changeset attached => LimeSurvey master 20775288
2023-06-08 18:19 c_schmitz Note Added: 75490
2023-06-08 18:21 c_schmitz Changeset attached => LimeSurvey 5.x e747eb5c
2023-06-08 18:21 c_schmitz Note Added: 75493
2023-06-12 10:45 LimeBot Note Added: 75543
2023-06-12 10:45 LimeBot Status resolved => closed
2023-06-12 10:45 LimeBot Bug heat 8 => 10