djongo icon indicating copy to clipboard operation
djongo copied to clipboard
verified publisher icon doableware

djongo.sql2mongo.SQLDecodeError

Open lifeisawavesorideit opened this issue 6 years ago • 6 comments

Currently I'm using Django and a MongoDB configured with Djongo.

These are my 2 classes:

class Machine (models.Model):
machine_type = models.ForeignKey(MachineType, related_name ='machine_types', on_delete = models.CASCADE)
machine_customer = models.ForeignKey(Customer, related_name='machine_customers', on_delete = 
models.CASCADE)
machine_serial_number = models.CharField(max_length = 255)
#
def __str__(self):
    return self.machine_serial_number

class Part (models.Model):
part_id = models.CharField(max_length = 255)
machine_serial_number = models.ForeignKey(Machine, related_name='parts', on_delete = models.CASCADE)
machining_start = models.DateTimeField()
machining_end = models.DateTimeField()
quality = models.CharField(max_length = 3)
#
def __str__(self):
    return self.part_id

I've been trying to perform the following query to find the number of not OK parts produced on each machine.

results=Machine.objects.values('machine_serial_number')
.annotate(part_nok=Count('parts', filter=Q(parts__quality__icontains='NIO')))

But I keep receiving the following error message:

COUNT(CASE WHEN "eda_main_part"."quality" iLIKE %(0)s THEN "eda_main_part"."id" ELSE NULL END) AS 
"part_nok" FROM "eda_main_machine" LEFT OUTER JOIN "eda_main_part" ON ("eda_main_machine"."id" = 
"eda_main_part"."machine_serial_number_id") GROUP BY "eda_main_machine"."machine_serial_number"  
LIMIT 21
Version: 1.2.31

Ideally I would like to receive something like:

{machine1:#111222, part_nok:121, part_ok:68, ... and so on }

When instead performing the following query by leaving out the filter

results=Machine.objects
.values('machine_serial_number').annotate(part_nok=Count( 'parts'))

everything runs as expected.

I'm usung following tools:

sqlparse==0.2.4
djongo==1.2.31
Django==2.2.6
python 3.7.4
MongoDb 4.2

Naturally I've tried older versions of sqlparse and djongo but this didn't seem to do the trick.

lifeisawavesorideit avatar Dec 13 '19 10:12 lifeisawavesorideit

Hello, we have been experiencing the same error. It is very annoying and also central to the usability of this package. I can add some details:

  1. This error occurs when the number of threads scale up. It doesn't show up on low demand scenarios.
  2. It doesn't depend on the database deployment (Docker or PureLinux) or in its parameters (e.g. swapiness).
  3. In the log of mongoDB it is reflected as: a) The connection is opened b) The connection is closed without doing a query
  4. When we try the query in mongo, it works well.

Here I paste one error as example:

[05/Feb/2020 11:43:41] "POST /orono/tiene_turnos/ HTTP/1.0" 200 63 test-unif-rest | Internal Server Error: /orono/tiene_turnos/ test-unif-rest | Traceback (most recent call last): test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 819, in parse test-unif-rest | return handler(self, statement) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 949, in _select test-unif-rest | self._query = SelectQuery(self.db, self.connection_properties, sm, self._params) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 113, in init test-unif-rest | super().init(*args) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 74, in init test-unif-rest | self.parse() test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 150, in parse test-unif-rest | c = self.where = WhereConverter(self, tok_id) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/converters.py", line 24, in init test-unif-rest | self.parse() test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/converters.py", line 148, in parse test-unif-rest | params=self.query.params test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/operators.py", line 364, in init test-unif-rest | op = ParenthesisOp(0, self.token[2], self.query) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/operators.py", line 446, in init test-unif-rest | op = CmpOp(0, tok, self.query) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/operators.py", line 514, in init test-unif-rest | self._constant = self.params[index] if index is not None else None test-unif-rest | IndexError: tuple index out of range test-unif-rest | test-unif-rest | The above exception was the direct cause of the following exception: test-unif-rest | test-unif-rest | Traceback (most recent call last): test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/core/handlers/exception.py", line 34, in inner test-unif-rest | response = get_response(request) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py", line 115, in _get_response test-unif-rest | response = self.process_exception_by_middleware(e, request) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/core/handlers/base.py", line 113, in _get_response test-unif-rest | response = wrapped_callback(request, *callback_args, **callback_kwargs) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view test-unif-rest | return view_func(*args, **kwargs) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/views/generic/base.py", line 71, in view test-unif-rest | return self.dispatch(request, *args, **kwargs) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 495, in dispatch test-unif-rest | response = self.handle_exception(exc) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 455, in handle_exception test-unif-rest | self.raise_uncaught_exception(exc) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/rest_framework/views.py", line 492, in dispatch test-unif-rest | response = handler(request, *args, **kwargs) test-unif-rest | File "/rest/orono_unificacion/views.py", line 1028, in post test-unif-rest | profs=icrProfs(profesional) test-unif-rest | File "/rest/orono_unificacion/views.py", line 1226, in icrProfs test-unif-rest | for index,prof in enumerate(icr_profesional): test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 274, in iter test-unif-rest | self._fetch_all() test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 1242, in _fetch_all test-unif-rest | self._result_cache = list(self._iterable_class(self)) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 55, in iter test-unif-rest | results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1097, in execute_sql test-unif-rest | cursor.execute(sql, params) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 99, in execute test-unif-rest | return super().execute(sql, params) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute test-unif-rest | return self._execute_with_wrappers(sql, params, many=False, executor=self._execute) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers test-unif-rest | return executor(sql, params, many, context) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute test-unif-rest | return self.cursor.execute(sql, params) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/cursor.py", line 53, in execute test-unif-rest | params) test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 754, in init test-unif-rest | self.parse() test-unif-rest | File "/usr/local/lib/python3.7/site-packages/djongo/sql2mongo/query.py", line 841, in parse test-unif-rest | raise exe from e test-unif-rest | djongo.sql2mongo.SQLDecodeError: FAILED SQL: SELECT "orono_unificacion_profesionales"."id", "orono_unificacion_profesionales"."idOrigen", "orono_unificacion_profesionales"."masterInst", "orono_unificacion_profesionales"."nombre", "orono_unificacion_profesionales"."slug_nombre", "orono_unificacion_profesionales"."origen", "orono_unificacion_profesionales"."unificado_prof", "orono_unificacion_profesionales"."email", "orono_unificacion_profesionales"."apellido", "orono_unificacion_profesionales"."slug_apellido", "orono_unificacion_profesionales"."telefono", "orono_unificacion_profesionales"."cuit", "orono_unificacion_profesionales"."titulo", "orono_unificacion_profesionales"."seo", "orono_unificacion_profesionales"."genero", "orono_unificacion_profesionales"."eliminado" FROM "orono_unificacion_profesionales" WHERE ("orono_unificacion_profesionales"."masterInst" = %(0)s AND "orono_unificacion_profesionales"."origen" = %(1)s) test-unif-rest | Params: (1132, 2) test-unif-rest | Version: 1.2.33

Versions: djongo==1.2.33
Django==2.2 pymongo==3.7.2 sqlparse==0.2.4 Mongo == 4.2.3

I remark that it is critical to solve this issue. Thanks in advance and we are available to collaborate in solving this.

PDK2020 avatar Feb 05 '20 15:02 PDK2020

Hi guys, We have been debugin the version 1.2.33 and we have developed a fix for this error. We have tested this fix in two ways: a) Streessing the application in testing with some scripts that simulate a very high demand scenario b) In Production environment.

The fix works perfectly. Can you use it in the new versions so it can be used widely in the community?

We have edited two files query.py and operators.py. Here are the two files: https://drive.google.com/drive/folders/1VORvPO9GKtR2K8BkeAlsMoVQNHuBnWiN?usp=sharing

Thanks and hope it will be useful!

Bioengineer Patricio Donnelly Kehoe, MSc. PhD. CTO at Kozaca SA, Argentina

PDK2020 avatar Feb 10 '20 15:02 PDK2020

@PDK2020 Would you open a PR with those?

jourdanrodrigues avatar May 04 '20 21:05 jourdanrodrigues

@PDK2020 such a relief. I’ll have a look at it ASAP.

lifeisawavesorideit avatar May 18 '20 18:05 lifeisawavesorideit

I can't because I can't make a checkout with the version I have worked on. What I would recommend you to do is this:

  1. Install the library in your python environment.
  2. Copy the library folder to your project's folder.
  3. Paste the files I have uploaded replacing the old ones.

It is very important to use the same version.

Best, Patricio

El lun., 4 may. 2020 a las 18:53, Jourdan Rodrigues (< [email protected]>) escribió:

@PDK2020 https://github.com/PDK2020 Would you open a PR with those?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nesdis/djongo/issues/348#issuecomment-623727866, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOPEERZ7N74YINLZIIQI6ALRP42NDANCNFSM4J2KVSUQ .

--

Bioing. Patricio Donnelly Kehoe, MSc. PhD. Doctor en Informática

Coordinador de Innovación Tecnológica Unidad de Innovación y Creatividad (UIC) Grupo Oroño

Director de Tecnología (CTO) Kozaca SA Bv. Oroño 790 (2000) Rosario, Santa Fe, Argentina

*Director * LANEN (Laboratorio de Neuroimágenes y Neurociencias) | Fundación Ineco Rosario. Bv. Oroño 1508 (2000) Rosario, Santa Fe, Argentina TE: +54 (0341) 425-8695 / 424-7278

PDK2020 avatar May 18 '20 18:05 PDK2020

Hi guys, We have been debugin the version 1.2.33 and we have developed a fix for this error. We have tested this fix in two ways: a) Streessing the application in testing with some scripts that simulate a very high demand scenario b) In Production environment.

The fix works perfectly. Can you use it in the new versions so it can be used widely in the community?

We have edited two files query.py and operators.py. Here are the two files: https://drive.google.com/drive/folders/1VORvPO9GKtR2K8BkeAlsMoVQNHuBnWiN?usp=sharing

Thanks and hope it will be useful!

Bioengineer Patricio Donnelly Kehoe, MSc. PhD. CTO at Kozaca SA, Argentina

I have replaced the query.py with your query.py but its showing below warnings line:58 connection_properties: 'djongo.base.DjongoClient', djongo is not defined line:738 connection_properties: 'base.DjongoClient', error: base is not defined

developervick avatar Feb 21 '23 08:02 developervick