#1054 -unknown column in order clause after exporting to MariaDB from Mysql

Question

I'm getting this #1054 -unknown column in order clause error, after I export my database from one hosting with MySql 5.7.23-cll-lve to another hosting with MariaDB 10.2.27-MariaDB-cll-lve. I have both hosting working right now so I can see that the queries work in the first one and in the MariaDB throws this error. And I checked that the column exists, the structure is the same. I also try to export using "MYSQL40", but nothing changed.

What can I do?

I will put the query that is giving the problem (is not the only one, but the others are similar):

SELECT * FROM (
    (SELECT result.*, IFNULL(SUM(mv.monto_pago_fv), 0) AS pago  FROM(
        (SELECT DISTINCT f.id_factura_de_venta, f.numero_factura_de_venta, f.fecha_contable_fv, f.fecha_fv, f.anular_fv, 
            f.id_cliente, f.tipo,
            c.rut, c.nombre, SUM(fot.monto_pago_ot) AS monto_facturado_neto, SUM(ROUND(fot.monto_pago_ot*0.19)) AS iva
        FROM facturas_de_ventas f
        JOIN clientes_y_proveedores c ON f.id_cliente=c.id_cliente_o_proveedor
        JOIN facturas_de_ot fot ON f.id_factura_de_venta=fot.id_factura_de_venta
        GROUP BY f.id_factura_de_venta
        ORDER BY f.id_factura_de_venta DESC)
        UNION
        (SELECT id_factura_de_venta, numero_factura_de_venta, fecha_contable_fv, fecha_fv, anular_fv,
                id_cliente, tipo, rut, nombre, SUM(monto_facturado_neto) AS monto_facturado_neto, SUM(iva) as iva  FROM(
            (SELECT DISTINCT f.*, c.rut, c.nombre, SUM(s.valor_sf*s.cantidad_sf) AS monto_facturado_neto, 
            SUM(ROUND(s.valor_sf*s.cantidad_sf*0.19)) as iva
            FROM facturas_de_ventas f
            JOIN clientes_y_proveedores c ON f.id_cliente=c.id_cliente_o_proveedor
            JOIN servicios_factura s ON f.id_factura_de_venta=s.id_factura_de_venta
            GROUP BY f.id_factura_de_venta)
            UNION
            (SELECT DISTINCT f.*, c.rut, c.nombre, SUM(mf.valor_mf*mf.cantidad_mf) AS monto_facturado_neto, SUM(IF(m.iva_material=1, mf.valor_mf*mf.cantidad_mf*0.19, 0)) AS iva
            FROM facturas_de_ventas f
            JOIN clientes_y_proveedores c ON f.id_cliente=c.id_cliente_o_proveedor
            JOIN materiales_factura mf ON f.id_factura_de_venta=mf.id_factura_de_venta
            JOIN materiales m ON mf.id_material=m.id_material
            GROUP BY f.id_factura_de_venta)
            ) AS fms
        GROUP BY id_factura_de_venta)
    ) AS result
    LEFT JOIN montos_pago_v mv ON result.id_factura_de_venta=mv.id_factura_de_venta
    GROUP BY result.id_factura_de_venta)
    UNION ALL
    (SELECT DISTINCT f.id_factura_de_venta, ncv.numero_ncv, ncv.fecha_contable_ncv, ncv.fecha_ncv, 
    f.anular_fv, f.id_cliente, 'Nota crédito' AS tipo, c.rut, c.nombre,
    ncv.valor_descontado_ncv AS total_neto, ROUND(ncv.valor_descontado_ncv*0.19) AS iva, SUM(IFNULL(m.monto_pago, 0)) AS pago
    FROM notas_credito_ventas ncv 
    JOIN facturas_de_ventas f ON ncv.id_factura_de_venta=f.id_factura_de_venta AND f.anular_fv=0
    JOIN clientes_y_proveedores c ON f.id_cliente=c.id_cliente_o_proveedor 
    LEFT JOIN montos_pago_ncv m ON ncv.id_nota_credito_venta=m.id_nota_credito_venta
    WHERE ncv.valor_descontado_ncv != 0
    GROUP BY ncv.id_nota_credito_venta
    ORDER BY ncv.id_nota_credito_venta) 
)AS r
WHERE tipo!='boleta' AND MONTH(fecha_contable_fv) =11 AND YEAR(fecha_contable_fv) =2019;

I checked and the problem is in the final WHERE clause, specifically after "tipo!='boleta'", because I prove all the other queries in parenthesis individually and they work, and I also erase the part "AND MONTH(fecha_contable_fv) =11 AND YEAR(fecha_contable_fv) =2019" and in that case it works in the hosting with MariaDB, I repeat that that same query works perfectly in the other hosting with Mysql. So the problem should be some option in the new hosting, or maybe the way of exporting, or maybe I should correct a lot of queries now that I am using MariaDB. I hope someone can help me. Thanks in advance.

It seems very odd to have an ORDER BY in a single query that is part of a UNION on a column that isn't even present in the UNION's SELECT statements. Why not just remove those ORDER BY clauses and put one at the end of the entire statement like ORDER BY id_factura_de_venta. I'm not sure if it's causing the error, but it may be. — JNevill
– JNevill, Commented Nov 8, 2019 at 17:55
Wow....luckily you use MySQL. This query will not ven run in any other dbms. It has so many flaws. Even MariaDB is probably a bit more strict. That's why it errors out. — Eric
– Eric, Commented Nov 8, 2019 at 18:08
I'm with @Eric on this one. The subqueries ORDER BY's the Not-Full-Group-By's. The redundant group by's. It is a bit of a mess. I also agree that the Error message doesn't make much sense here if what you say is true about it working fine after monkeying with that final WHERE clause. I would consider just rewriting this whole thing as a solution. — JNevill
– JNevill, Commented Nov 8, 2019 at 19:06

Rick James · Accepted Answer · 2019-11-10 19:09:24Z

0

Can't you move the tests on tipo and fecha_contable_fv inside? That is do the filtering before doing all the UNIONing and GROUPing.

Here's a way to do the date check in a single step (no faster, just more concise):

fecha_contable_fv LIKE '2019-11%'

If there were a usable index, I would recommend:

    fecha_contable_fv >= '2019-11-01'
AND fecha_contable_fv  < '2019-11-01' + INTERVAL 1 MONTH

What was the full error message; didn't it say more than just "#1054 -unknown column in order clause error"

answered Nov 10, 2019 at 19:09

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

leonardo_palma Over a year ago

Hi Rick. Thanks for answering. The full error said: "#1054 -unknown column 'fecha_contable_fv' in order clause error". Moving the final WHERE to each individual subquery solved the issue. The other changes that you suggest didn't change the situation. Why is this happening considering that putting the WHERE inside each subquery is more redundant, which the other users said that was the original problem?

Rick James Over a year ago

@leonardo_palma - I tend to look at a query from a performance point of view. Filtering first (that is moving the WHERE inside) means less stuff is shoveled around, hence faster. As for why the problem went away -- I don't know.

Collectives™ on Stack Overflow

#1054 -unknown column in order clause after exporting to MariaDB from Mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related