1

I have a complicated query which works FINE in phpmyadmin, but whenever I try to use it within php I get an error saying LIMIT and UNION are not allowed together. I have added LIMIT to speed up the query (so a sub-select would only look for 100 rows, and not thousands of possible results) - which did work (it did speed up the query)

Now I switched from mysql to maria_Db and while the query still works on phpmyadmin (112 rows inserted), it simply puts out an error message on php

Any idea?

insert ignore into paulsim2_dokumente_autofill.suche_intern_cache_helper select sysdate(), 'prometheus' as bb,
id, w_tit,w_key, w_uni,w_sim,w_pln,w_len, such_union, 100 from ( select distinct id, w_tit,w_key, w_uni,w_sim,w_pln,w_len, such_union
from ( ( SELECT a.ID as id, 'tit' as such_union, MATCH(a.titel_u8) AGAINST('>prometheus' IN BOOLEAN MODE)* MATCH(a.titel_u8) AGAINST('prometheus')+1 as w_tit, MATCH(a.keywords_titel_u8) AGAINST('prometheus')+1 as w_key, MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus')+1 as w_uni, MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE) *MATCH(a.keywords_simple_u8) AGAINST('prometheu') +1 as w_sim, 1 as w_pln, 10 as w_len from dokument as a where freigegeben in (1,3) and aktiv=1 and a.id <> -1 and MATCH(a.titel_u8) AGAINST('prometheus') > 1
LIMIT 0,100 ) UNION ( SELECT a.ID as id, 'key' as such_union, MATCH(a.titel_u8) AGAINST('>prometheus' IN BOOLEAN MODE)* MATCH(a.titel_u8) AGAINST('prometheus')+1 as w_tit, MATCH(a.keywords_titel_u8) AGAINST('prometheus')+1 as w_key, MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus')+1 as w_uni, MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE) *MATCH(a.keywords_simple_u8) AGAINST('prometheu') +1 as w_sim, 1 as w_pln, 10 as w_len from dokument as a where freigegeben in (1,3) and aktiv=1 and a.id <> -1 and MATCH(a.keywords_titel_u8) AGAINST('prometheus') > 1
 LIMIT 0,100 ) UNION ( SELECT a.ID as id, 'uni' as such_union, MATCH(a.titel_u8) AGAINST('>prometheus' IN BOOLEAN MODE)* MATCH(a.titel_u8) AGAINST('prometheus')+1 as w_tit, MATCH(a.keywords_titel_u8) AGAINST('prometheus')+1 as w_key, MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus')+1 as w_uni, MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE) *MATCH(a.keywords_simple_u8) AGAINST('prometheu') +1 as w_sim, 1 as w_pln, 10 as w_len from dokument as a where freigegeben in (1,3) and aktiv=1 and a.id <> -1 and MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus') > 1
 LIMIT 0,100 ) UNION ( SELECT a.ID as id, 'sim' as such_union, MATCH(a.titel_u8) AGAINST('>prometheus' IN BOOLEAN MODE)* MATCH(a.titel_u8) AGAINST('prometheus')+1 as w_tit, MATCH(a.keywords_titel_u8) AGAINST('prometheus')+1 as w_key, MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus')+1 as w_uni, MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE) *MATCH(a.keywords_simple_u8) AGAINST('prometheu') +1 as w_sim, 1 as w_pln, 10 as w_len from dokument as a where freigegeben in (1,3) and aktiv=1 and a.id <> -1 and MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE)
 LIMIT 0,50 ) UNION ( SELECT a.ID as id, 'pln' as such_union, MATCH(a.titel_u8) AGAINST('>prometheus' IN BOOLEAN MODE)* MATCH(a.titel_u8) AGAINST('prometheus')+1 as w_tit, MATCH(a.keywords_titel_u8) AGAINST('prometheus')+1 as w_key, MATCH(a.keywords_uni_typ_cat_u8) AGAINST('prometheus')+1 as w_uni, MATCH(a.keywords_simple_u8) AGAINST('+prometheu' IN BOOLEAN MODE) *MATCH(a.keywords_simple_u8) AGAINST('prometheu') +1 as w_sim, MATCH(x.plain_u8) AGAINST('"prometheus"' IN BOOLEAN MODE) *MATCH(x.plain_u8) AGAINST('"prometheus"') +1 as w_pln, 10 as w_len from dokument as a, dokumente_text as x where freigegeben in (1,3) and aktiv=1 and a.id <> -1 and x.id = a.id and MATCH(x.plain_u8) AGAINST('"prometheus"')
 LIMIT 0,50 ) UNION ( select 1 as id, 'dum' as such_union,-1 as w_tit,-1 as wkey, -1 as w_uni, -1 as w_sim, -1 as w_pln, -1 as w_len from dokument where 1 Limit 0,1 )
order by (w_tit)*(w_key)*w_uni*w_sim*w_pln desc Limit 0, 500 ) as tneu ) as tnx
EternalHour
  • 8,308
  • 6
  • 38
  • 57
bodomalo
  • 153
  • 1
  • 1
  • 11
  • 1
    I can only suggest that you try to simplify your current query and narrow down the exact problem in the syntax (e.g. just have a single union). – Tim Biegeleisen Dec 20 '19 at 13:33
  • I can, but I know the problem anyway: Within phpmyadmin UNION + LIMIT is allowed - Within php it produces an ERROR. – bodomalo Dec 20 '19 at 16:07
  • That comment doesn't sit right with me, assuming both are ultimately executing against the same database. – Tim Biegeleisen Dec 20 '19 at 16:14

1 Answers1

0

I found my error, which was at another place. I had a pow(-1,1.2) and this was no longer allowed, without setting mysql> SET sql_mode = '';

bodomalo
  • 153
  • 1
  • 1
  • 11