| Bug #74054 | Limit 1 returns second row not first one! | ||
|---|---|---|---|
| Submitted: | 24 Sep 2014 14:46 | Modified: | 26 Sep 2014 16:13 |
| Reporter: | Pete French | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.6.20 | OS: | FreeBSD (9.3-STABLE) |
| Assigned to: | CPU Architecture: | Any | |
[25 Sep 2014 10:29]
Hartmut Holzgraefe
I can't reproduce this with 5.6.20, result set without LIMIT:
*************************** 1. row ***************************
promo_code:
backend_system: nimax
area_code: NMX
venue_code: PLO/T
event_code: 2649
perf_weekday:
perf_yyyymmdd:
perf_hhmm:
perf_type_code:
mod_group: standard_affiliate
discounts_perm_list: +TSW/IL2/1
pkg_discounts_perm_list:
close_in_day_limit: 0
close_in_discounts_perm_list:
close_in_pkg_discounts_perm_list:
far_out_day_limit: 0
far_out_discounts_perm_list:
far_out_pkg_discounts_perm_list:
comment:
*************************** 2. row ***************************
promo_code:
backend_system: nimax
area_code:
venue_code:
event_code:
perf_weekday:
perf_yyyymmdd:
perf_hhmm:
perf_type_code:
mod_group: standard_affiliate
discounts_perm_list: +TSW/IAR*,+TSW/ING*,+TSW/INR*,+TSW/PRX/*,+TSW/IA3/*,+TSW/IA4/*,+TSW/IA2/*,+TSW/A11/*,+TSW/A12/*,+TSW/FUL/*, +TSW/OPG/*,+TSW/SPN/*,+TSW/TAW/*,+TSW/TS1*,-TSW/TS2*,+TSW/TS5/1,+TSW/TS6/1,+TSW/TSP/1,+TSW/BEE/*,+TSW/TSW*,+TSW/AFF*,+TSW/AF1*,+TSW/IN1/*,+TSW/IN2/*,+TSW/IN3/*,+TSW/IN4/*,+TSW/IN5/*,+TSW/IN6/*,+TSW/SIF/*,+TSW/PRV/*,+TSW/TS3/*,+TSW/TAF/1
pkg_discounts_perm_list:
close_in_day_limit: 0
close_in_discounts_perm_list:
close_in_pkg_discounts_perm_list:
far_out_day_limit: 0
far_out_discounts_perm_list:
far_out_pkg_discounts_perm_list:
comment:
2 rows in set (0.01 sec)
and with LIMIT 1:
*************************** 1. row ***************************
promo_code:
backend_system: nimax
area_code: NMX
venue_code: PLO/T
event_code: 2649
perf_weekday:
perf_yyyymmdd:
perf_hhmm:
perf_type_code:
mod_group: standard_affiliate
discounts_perm_list: +TSW/IL2/1
pkg_discounts_perm_list:
close_in_day_limit: 0
close_in_discounts_perm_list:
close_in_pkg_discounts_perm_list:
far_out_day_limit: 0
far_out_discounts_perm_list:
far_out_pkg_discounts_perm_list:
comment:
1 row in set (0.00 sec)
LIMIT 2 -> same result as without LIMIT
[25 Sep 2014 10:31]
Hartmut Holzgraefe
Explain plans are the same in both cases here, only differ in "rows" estimate (256 vs 287):
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: preferred_discounts_perm_lists
type: index
possible_keys: PRIMARY,backend_system
key: PRIMARY
key_len: 1399
ref: NULL
rows: 287
Extra: Using where
1 row in set (0.00 sec)
[25 Sep 2014 10:33]
Hartmut Holzgraefe
even when adding FORCE INDEX (backend_system) I'm still getting the same results with and without LIMIT ...
[25 Sep 2014 15:08]
Pete French
Which version of FreeBSD are you using, and are you using the compiled version from the pkg repository ? This is perfectly repeatable for me on our systems. I haven't tried it on other versions of FreeBSD though. I will try and setup a separate test system elsewhere and see what results I get from that...
[26 Sep 2014 15:13]
Pete French
Ok, tried again this morning - I still have the bug on my table, but droping the table and reloading the data makes it behave correctly. Sorry! I assume that when I did the test of loading the data I forgot to switch to the fresh database before running the query. Still odd, but looks like a corrupt innodb table I guess. You can close this...
[26 Sep 2014 16:13]
MySQL Verification Team
Thank you for confirming, I couldn't repeat with 5.6.20/21 too. Per [26 Sep 15:13] Pete French, marking this as not a bug, please feel free to open if you are hitting this frequently. Thanks, Umesh
Description: I have a query which produces two rows when run, ordered by several columns in descending order. When a 'limit 1' is applied to this query it does not return the first row, it retruns the second. Note that any 'limit' clause always returns one row - the second - so if I do 'limot 2' then I do not see both rows, only the second. Query is below: select * from preferred_discounts_perm_lists where (area_code in ('NMX','')) and (venue_code in ('PLO/T','')) and (area_code <> '' or venue_code = '') and (event_code in ('2649','')) and (venue_code <> '' or event_code = '') and (perf_weekday in ('fri','')) and (event_code <> '' or perf_weekday = '') and (perf_yyyymmdd in ('20141003','')) and (perf_weekday <> '' or perf_yyyymmdd = '') and (perf_hhmm in ('1945','')) and (perf_yyyymmdd <> '' or perf_hhmm = '') and (perf_type_code in ('202655','')) and (perf_hhmm <> '' or perf_type_code = '') and (mod_group in ('standard_affiliate','')) and (backend_system = 'nimax') and (promo_code = '') order by area_code desc, venue_code desc, event_code desc, perf_weekday desc, perf_yyyymmdd desc, perf_hhmm desc, perf_type_code desc, mod_group desc limit 1; How to repeat: Attached is a uuencoded dump of the table to which the above query applies. You can load this into an empty database and run the above query with and without the limit clause to see the result. begin 644 pdl.sql.gz M'XL("-[-(E0"`W!D;"YS<6P`[5U=EYI,$K[>^16]5Y/,ZT1`_,J>G!-&T6&# M8`3'-W-#&&%F/%&9!9SL[*_?YAL56M#"Y"(YB2D1ZJFN>KJ[&IKNZVLT>E.^ MBLC8K%X037V@&PCU%XYK+QY0\T/K`T/5T*-EHX%MFC=*O_N!1N_TE=%BWU]< M7^._Z-9RW(_(,>T7<^TNUB;"?_JZJS_HCOD1K7YJR\6KZ9UX?=0?[TK%M%]- M&^%_SL):_\,SBVY?+ZVGBXOZU3]9BJ9HI/`J^BR+?:UWRTVXGLI/-'Q(ZXD" M+ZF?/G_..HRNZO\ZJ&'"*U-15?94A,?S=,BBR*F"+&%)DOB>)WHJ,@[O:Y"X M$:^@C?O82?_62+2KPHC7[F6)QRIC>?_<Y+3+ORCJ(T5=)N=0-)OHFTK"URF/ M"\[WOG@EW?I>0]L_4SE*!O*$%X:2]H7_EFC:/UA#&2=2.6[$S-1&<M\K9R36 M4'SP4I(U;JK*VATG8ONP5^_YB9PJ)$WOZ))DE5="9;X<:`L.!T:$M%;UAZ6) M<#W8S-V-;?IUP/6/?7^QS4?3MDU#,Q;.W-JL74=[,>T59KKC.M\]!1?]B3Q& M*G<C\D@8(/YO0<%4.73EK@<<_16?.G>T^7*!*Y=7L]`G]/GS_%FW];EKVIIC MNM&/>P[,/.M30JO>A.=4/C3RD&GHW07R3K)6EC:W#/,[>M5M#^$=;@E07#-\ M8!\A8#KO?]$>%FN$?8RDJ2BB/C_@<,U!EY<U3^>#/O]AK@W->7-<<P6G5[=- M'=C45W.],8%UFEBI"ZP3Q^U1^VF:/PS][3O"1J_>X5\NG<T:?ZXL[]/=F/CS MIVEX\O,&?S[:"^\<W;U\3U;\AO^L5H:1V-L!,/?Y>94*/H0#W+<7Z&"M+$-[ MLJW-"YS*C-KV';GF?]WXBJ!$/YZT@J?.EY9C:HNUAJ./3UHM\%DKTUAL5HNU M^ZZ;%5WJ<N?*DD@EK'O4;<W:N.6-BR\LAU/&<=9JA2MCQD_CB3#B)M\0[K'0 MNW0K6-MMOFJI=J>6;C!JZ9I>VZZBM9V*54M5B=HNF6LI%K[WC/.,VFM%W_UB MPR[>(UX:"CCU$-9KJW\3A].K*[B6?/)KR42>>6G#B%,_];_AK$?H%>O#]GK& M5,?=QXGL8OV$#)R#ENVU1;GW)>@0#W?6:#815#Y*A"@*<:+7`A3L3ON"XI^( M@Q>DD(*D\!,5"9(J'[[:SW<4Y+?JA_Y2._]?7KZO^1?JAKE\>5[D7:FO]/]9 MN&K;EM='7,^$85V0A/I5[:^K`WK=)_PYX_$'-Q[75?P_TVGO:G]\7"P7NFMJ MSXNG9^W1-!U\]"]5F=4%KENG(2"V"N"KYF9]$-7FQMZRMH9"^19$O6TZWHC' M6%IKP^^M*T-R7'UMZ+:AQ?&H$FWC&.6"[-%_;;Y%FL5O/5\SW6Z6H)/$0B*E M0P^K.8JZMA-V6!1"Q&&!TL$^K!FW18_6VLUMC++C*TBX41K27J/DBU)*O$E$ MKGYU$GJVS^#!^R//E3UY%KB4;=+D)DU0)W6!HG$]#<5F(C+%/7X0EE!^7-)C M<9I,JTCQ2A=D*/!A0=K=@OXK!R#)=SY`>[\9`BG`F%>#`C0ZNQZRF!=[8=D+ M]RV)P+BT_B@"5*=9B8-FWZ0P`EV8"#SK;RO=_F$>USYP5Z=JWS$::^5&]V$- MY^Z9^JD`8:<"9.YR\8H38,>:+_2E]O-YX9I+_<%<1@BB<'>RP?L\E!D_70B+ MD!?0W@<%Z89A&@B?A'0'Q9EN8>C]Y`C(;:16_F3E86]87ILH2_A3G=SZ-:K# MEB)^F7IU`&B;HH"*<^,)B$$,[0DXK<(XROA@1XGK+/:%8VG6VBS9SGE)EC*6 M<8L42/05`-1VHS?A^[C1`]";2J"A3<X>-T&CY&?0T$BI%/H$U3UNXB4M:IBH M=]AFD4C[664@,HG8*$GBH<CY]6488'?I;GGL4H!BWZN@G-@/`?=S3&(-@D;; M)CNT]ER^0P,1*7\<F*I$.6[[#!$BH%5#P'Q`"$KD:]].S#P0F:&A08!YEP]4 M(>^Z15.IBM`@:=`MD%15!`0?H9XL1LU0^Z2.*A)9:#/@ZE@"TF&J9R,!#8"- M!.VP;"0`[;,1T&G=]CE#U&U7&:)N^TPAZK;/V&!T.V>-4*?2"'7.%:%.]1&: M",%XF3I'@/+!<KH/4!`($N0J!^9`+@X\!?KR*.I.RW(@'O9"`^^&JC(@0M@J MPSP0PA-QIU\"7(8Z0Z=(0`.H;03ML-6-`%1!?8O`6@VP+#H2FT=9-)`GH45M MJGK.$-``.$/0#LL9`A`\9V['0GB+L'6&6DU``X@003MLA`A`54:HTSIGA#JM MWZ,-(5@$RIE.ZTR<Z51_IU4(LSR<(]#5)<0IE$9U*!(7YCN=QAE&$B2TZAZ9 M',0&8#I!.RS3"4#P3(_!N@Q3/0<[928AGEJD#FD>(KSRG*F(\$#P))`9+GK. MR18A`9/9/;6.PA[S`39#T8WJN4%``R`'03ML$T$`@F?'^%8.INZ=(W\B@)WQ M04:^%1`LR54.3))<G`HX(D8M2.,<),E'@XA/OG;@`.4#51BA+ML\8X3VT2`C MM*^]H@CM`\%'2.'NPGM=YW@F0T([8TM+,`.`*`3ML$0A`,$3Y8Z7"KX-`#0S M)P;L,M19`&="-"JASSCQ3AX,@L^HL&P%=W!2V7(DMA.Q4]+D">_-%>24870_ MK5O%($ZY]1RC]./9'><*BJ9OC`KGQ)+4/SV\5*D>8)JJ-M<K=0Y!/81S".HA MG!-XH#+G$-1#.(>@'LXY=[PWR?F&D:,;J\P^VC$=E1:86)GO">HA?$]0?Z3O MUXN5_M\C7D\0N$F((4C#6(J.C2=_UZ.#7",1V41D(I&CZ42,CPZF(A:#]Y7E M\;`>%TB*1)6;Q:)"7]6N`XF)CS6CQ^*JTDK$<23>\'QR_2RR8#"()3HN5FR@ M(#&)F)1+2LHE-1.Q%5LM#.JQ9^X2U$;]I.ADO!`_2GPY2CP\:B5B.Q9E-K&D MGXA"RBN)R,?1\#*!1.XFLLRDCC=2,IN@-T\J\=9[^G_X!\&_J"R#0ZTH,3!Y M*QS\B='O$R/2VA!_XO3[Q,E=S'^8KL,Z\V?+6L8=KLHJ>6K5Q0HQ%,U>4^UK MJO,![6A`&UPYG8\HUI*\I>LOJ*,OE\BW"#G/UD_G0R$KM];^^$.?\Z4"TNAO M;SY8+WB%@VGMSP(@)6U,46H>ALG./N@D^X!"VNKUX=3F]5EP"*06]VB4,C,E M!!$0)B?>0.K300:T.B?(@`B$()=%&7+!)$^FV2D19-SBY2[/H*#KN&>@Z7"I MAK7Y$]G6S_(V9#$`KH@I!IRM2%FO?HW!2I1-OK,5+I^9%9G0HIJ$I4LB<%%I M'.GA??U9\3N6D:U&%Z32E8<YW)/6XE%U++*)V$S$5B*V$[$#9FNQ2EI:;>F: M4AKAJ,IP"(7=[?&]=:G[@M*3IY*J$>CC);K1:GQ>IIN#/])=%W&I"LDT\!?; M7%G85TA?(VMIH&A)R=.,S?=/8.NO-14RIR7`G))I$M0"99H$!,!,4_P6S/QE M6*I1,@K):"CB=C#R0M&0,'6\!6?5?O.I*C?)**^7B/U$Y%-VL6"V[!(H[9'= MH6C@#W!W$,CV"ZPY0,PS612LA5Z2QV)"8Y%-Q.8V<6(Y?1S4VFQ._2[6':3; M[V)H(2;^`F.;C1)SWDK<6SP,4RKM!`)-YX^`9<D<9]PE.99XQZ1DN.+DY*V` M)2/DK4>CM-A2]Y!H,)BMFSQP:O-N\L`AD&[RE$09<V(T8"^SB'K2&7K+#T8B M-X$#SVH..DD;T(U%F0$#W5I]N^H2YM"D<ES2$MTPV*(<W8)NE:%4([4B?!,. MLK+;&4`&;I&N&A]D]D?"7=('E;T'1L#:OP?FER0N52LEMU-R!\R"O(I5C6]) MU0D$D>U6=XN?@`+Y'(<`<\J#%H):H`<M=]PTBCMS9%-6=CA/@LRZM2`DH](9 M&%)>FP18F`*U%!"M8"T]'I%M0#TBBVY2NL\F&DQ%1#?K5+L\/,#S48+ZPD_' M8$ISS*,K&.1CGUH=AUYJXYGCHTG<=09.;48J()%\%CS<HVGLJ3K-EH;+:U'@ M"D1J18Y&:38J'/T28$X9_1+4`HU^"0@GC7[_LS'-M?.L+Y>YLWH-[<6RO>7X M#VU%YNC&TK2=4A.B1_'<8RF6[I/Y:O7"D,%>`='K[33;+#`_PA_5'0G0;#4K M!FA3AP%&4_I8@&ZC74D!E%FX_`1;8N$Q;C``Q-DOQ\V$AM.?:BE`[<YN*D`A M",^1"\"81;>O"#>KBG?EZL52/Y;X6!K$TC"6;F-)N(HVNI&X8A8J_K9(7/C2 M(=LJ2L0`Y-_^KEZ^^"7$8]JD7X^V(LMG!/W'XJ3WDJE"?]8N617`Y.Q<4P44 M:0>V8^'ZDZ`#9]MTMPP=I_L<O-LE7E]_71AH@*A6G>K4O2D>92W8WGTTWFON MX.ZC!?5O4?`L)<ICRUG`2?R!,T`2@W?KV6Z[S#Y5DE"&MB20+-(,@91O,0;* MXJR6"DIW'N&@]),X50HC>A3"=CN-,K3Y"@2R%5DHI7G>A])/\OYAC&?]L>Q> MBC/^)DC):KXH''Z:3$0)G0ZL==_KP`#9;C\>)%QRY388`;79;JMX-(JT;85A MME,_OT`C$4Q]*MH2/P-3ZV\VC\V>VZ:Q<".$X60,AI!-*,@RY#/J%)1VD?C> MYZL_=E][7MK>UGXJB7+O2W"U$BJEJ092>!6IPHC7[F6)__39FUH<?_4OC$ZE M_5.5KZ(VDOOAF=$W_\3`3IKUSQO($UX82AK&UWJW?.^+$ERQ?WS_VJDD?)WR M6Y=M'4JNB*SJW7(3KH>]H^%O6D\4>$D-+LSZY=#U$UZ9BJJ2I2#\*4.#+(J< M*LB2UI,EB>]Y8J@@XY?4]73B5TE6>25QK/\U",'U->IO5B]H;JU>EJ9K&LA: 9A_.UN]<,BVCV8[/]L4E=_!_JYA^Y1HP````` ` end Suggested fix: If you embed the select as a subselect and apply the limit outside then it works - but this is a workaround and not a rreal fix. It looks as if it is to do with indexes, as the limited query uses an index, but the unlimted one does not.