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.
Attached Files
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)   
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

There are no users monitoring this issue.

Activities

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